Relational Database Design and Implementation (eBook)

Front cover 1
Half title page 2
Title page 4
Copyright page 5
Table of contents 6
Preface to the Third Edition 16
Acknowledgments 20
Part I: Introduction 22
Chapter 1: The Database Environment 24
Defining a Database 25
Data “Ownership” 27
Database Software: DBMSs 29
Database Hardware Architecture 31
Other Factors in the Database Environment 39
For Further Reading 44
Chapter 2: Systems Analysis and Database Requirements 46
Dealing with Resistance to Change 47
The Structured Design Life Cycle 48
Conducting the Needs Assessment 49
Assessing Feasibility 53
Generating Alternatives 55
Evaluating and Choosing an Alternative 56
Creating Design Requirements 57
Alternative Analysis Methods 57
For Further Reading 63
Part II: Database Design Theory 64
Chapter 3: Why Good Design Matters 66
Effects of Poor Database Design 66
Unnecessary Duplicated Data and Data Consistency 68
Data Insertion Problems 69
Data Deletion Problems 70
Meaningful Identifiers 71
Chapter 4: Entities and Relationships 72
Entities and Their Attributes 72
Domains 82
Basic Data Relationships 85
Dealing with Many-to-Many Relationships 93
Relationships and Business Rules 98
Data Modeling versus Data Flow 98
Schemas 101
For Further Reading 104
Chapter 5: The Relational Data Model 106
Understanding Relations 107
Primary Keys 109
Representing Data Relationships 114
Views 117
The Data Dictionary 118
A Bit of History 120
For Further Reading 122
Chapter 6: Normalization 124
Translating an ER Diagram into Relations 124
Normal Forms 126
First Normal Form 127
Second Normal Form 132
Third Normal Form 135
Boyce-Codd Normal Form 137
Fourth Normal Form 138
Fifth Normal Form 140
Sixth Normal Form 146
For Further Reading 147
Chapter 7: Database Structure and Performance Tuning 148
Joins and Database Performance 149
Indexing 153
Clustering 155
Partitioning 156
For Further Reading 158
Chapter 8: Codd’s Rules for Relational Database Design 160
Rule 1: The Information Rule 161
Rule 2: The Guaranteed Access Rule 162
Rule 3: Systematic Treatment of Null Values 163
Rule 4: Dynamic Online Catalog Based on the Relational Model 164
Rule 5: The Comprehensive Data Sublanguage Rule 165
Rule 6: The View Updating Rule 166
Rule 7: High-Level Insert, Update, Delete 166
Rule 8: Physical Data Independence 167
Rule 9: Logical Data Independence 168
Rule 10: Integrity Independence 168
Rule 11: Distribution Independence 169
Rule 12: Nonsubversion Rule 170
Chapter 9: Using SQL to Implement a Relational Design 172
Database Structure Hierarchy 172
Schemas 175
Domains 177
Tables 178
Not Null Constraints 182
Views 191
Temporary Tables 194
Creating Indexes 196
Modifying Database Elements 197
Deleting Database Elements 200
Chapter 10: Using CASE Tools for Database Design 202
CASE Capabilities 203
ER Diagram Reports 204
Data Flow Diagrams 207
The Data Dictionary 209
Code Generation 212
Sample Input and Output Designs 214
The Drawing Environment 216
For Further Reading 217
Chapter 11: Database Design Case Study 1: Mighty-Mite Motors 218
Corporate Overview 218
Designing the Database 236
Chapter 12: Database Design Case Study 2: East Coast Aquarium 252
Organizational Overview 252
The Volunteers Database 259
The Animal Tracking Database 275
Part III: Relational Design Practice 294
Chapter 13: Database Design Case Study 3: SmartMart 296
The Merchandising Environment 296
Putting Together an ERD 298
Creating the Tables 305
Generating the SQL 307
Part IV: Database Implementation Issues 318
Chapter 14: Concurrency Control 320
The Multiuser Environment 320
Problems with Concurrent Use 325
Solution 1: Classic Locking 332
Solution 2: Optimistic Concurrency Control (Optimistic Locking) 339
Solution #3: Multiversion Concurrency Control (Timestamping) 339
Transaction Isolation Levels 340
Web Database Concurrency Control Issues 341
Distributed Database Issues 342
For Further Reading 343
Chapter 15: Database Security 344
Sources of External Security Threats 345
Sources of Internal Threats 348
External Remedies 350
Internal Solutions 359
Backup and Recovery 365
The Bottom Line: How Much Security Do You Need? 369
For Further Reading 370
Chapter 16: Data Warehousing 372
Scope and Purpose of a Data Warehouse 373
Obtaining and Preparing the Data 375
Data Modeling for the Data Warehouse 377
Data Warehouse Appliances 379
For Further Reading 382
Chapter 17: Data Quality 384
Why Data Quality Matters 384
Recognizing and Handling Incomplete Data 385
Recognizing and Handling Incorrect Data 387
Recognizing and Handling Incomprehensible Data 390
Recognizing and Handling Inconsistent Data 392
Employees and Data Quality 395
For Further Reading 396
Chapter 18: XML 398
XML Syntax 398
XML Document Correctness 401
XML Schemas 401
XML Support in Relational DBMSs 403
For Further Reading 406
Appendix: Historical Antecedents 408
File Processing Systems 408
The Hierarchical Data Model 414
The Simple Network Data Model 417
The Complex Network Data Model 423
Glossary 428
Index 434