Database Design for Mere Mortals

Foreword xix
Preface xxi
Introduction xxix
What's New in the Fourth Edition xxxi
Who Should Read This Book xxxii
The Purpose of This Book xxxiii
How to Read This Book xxxvi
How This Book Is Organized xxxvii
A Word about the Examples and Techniques in This Book xl
PART I: RELATIONAL DATA BASE DESIGN 1
Chapter 1: The Relational Database 3
Topics Covered in This Chapter 3
What Is a Database? 3
The Relational Database 5
Retrieving Data 7
Advantages of a Relational Database 9
Relational Database Management Systems 10
What's Next? 11
Summary 13
Review Questions 14
Chapter 2: Design Objectives 17
Topics Covered in This Chapter 17
Why Should You Be Concerned with Database Design? 17
The Importance of Theory 19
The Advantage of Learning a Good Design Methodology 21
Objectives of Good Design 22
Benefits of Good Design 23
Database-Design Methods 24
Traditional Design Methods 24
The Design Method Presented in This Book 26
Normalization 27
Summary 30
Review Questions 31
Chapter 3: Terminology 33
Topics Covered in This Chapter 33
Why This Terminology Is Important 33
Value-Related Terms 35
Data 35
Information 35
Null 37
The Value of Null 38
The Problem with Null 39
Structure-Related Terms 41
Table 41
Field 44
Record 45
View 46
Keys 48
Index 50
Relationship-Related Terms 50
Relationships 50
Types of Relationships 52
Types of Participation 57
Degree of Participation 57
Integrity-Related Terms 59
Field Specification 59
Data Integrity 59
Summary 61
Review Questions 62
PART II: THE DESIGN PROCESS 65
Chapter 4: Conceptual Overview 67
Topics Covered in This Chapter 67
The Importance of Completing the Design Process 68
Defining a Mission Statement and Mission Objectives 69
Analyzing the Current Database 70
Creating the Data Structures 72
Determining and Establishing Table Relationships 73
Determining and Defining Business Rules 74
Determining and Defining Views 75
Reviewing Data Integrity 75
Summary 77
Review Questions 78
Chapter 5: Starting the Process 81
Topics Covered in This Chapter 81
Conducting Interviews 82
Participant Guidelines 84
Interviewer Guidelines (These Are for You) 86
Defining the Mission Statement 91
The Well-Written Mission Statement 91
Composing a Mission Statement 93
Defining the Mission Objectives 96
Well-Written Mission Objectives 97
Composing Mission Objectives 99
Summary 103
Review Questions 104
Chapter 6: Analyzing the Current Database 107
Topics Covered in This Chapter 107
Getting to Know the Current Database 107
Paper-Based Databases 111
Legacy Databases 111
Conducting the Analysis 113
Looking at How Data Is Collected 113
Looking at How Information Is Presented 116
Conducting Interviews 120
Basic Interview Techniques 121
Before You Begin the Interview Process 128
Interviewing Users 128
Reviewing Data Type and Usage 129
Reviewing the Samples 131
Reviewing Information Requirements 135
Interviewing Management 143
Reviewing Current Information Requirements 144
Reviewing Additional Information Requirements 145
Reviewing Future Information Requirements 146
Reviewing Overall Information Requirements 147
Compiling a Complete List of Fields 148
The Preliminary Field List 148
The Calculated Field List 156
Reviewing Both Lists with Users and Management 156
Summary 162
Review Questions 164
Chapter 7: Establishing Table Structures 165
Topics Covered in This Chapter 165
Defining the Preliminary Table List 166
Identifying Implied Subjects 166
Using the List of Subjects 168
Using the Mission Objectives 172
Defining the Final Table List 174
Refining the Table Names 176
Indicating the Table Types 182
Composing the Table Descriptions 182
Associating Fields with Each Table 189
Refining the Fields 191
Improving the Field Names 191
Using an Ideal Field to Resolve Anomalies 196
Resolving Multipart Fields 199
Resolving Multivalued Fields 201
Refining the Table Structures 208
A Word about Redundant Data and Duplicate Fields 208
Using an Ideal Table to Refine Table Structures 209
Establishing Subset Tables 216
Summary 229
Review Questions 231
Chapter 8: Keys 233
Topics Covered in This Chapter 233
Why Keys Are Important 234
Establishing Keys for Each Table 234
Candidate Keys 235
Primary Keys 243
Alternate Keys 249
Non-keys 250
Table-Level Integrity 251
Reviewing the Initial Table Structures 251
Summary 259
Review Questions 260
Chapter 9: Field Specifications 263
Topics Covered in This Chapter 263
Why Field Specifications Are Important 264
Field-Level Integrity 266
Anatomy of a Field Specification 267
General Elements 267
Physical Elements 275
Logical Elements 278
Using Unique, Generic, and Replica Field Specifications 283
Defining Field Specifications for Each Field in the Database 287
Summary 291
Review Questions 292
Chapter 10: Table Relationships 293
Topics Covered in This Chapter 293
Why Relationships Are Important 294
Types of Relationships 295
One-to-One Relationships 296
One-to-Many Relationships 298
Many-to-Many Relationships 301
Self-Referencing Relationships 308
Identifying Existing Relationships 312
Establishing Each Relationship 323
One-to-One and One-to-Many Relationships 323
The Many-to-Many Relationship 331
Self-Referencing Relationships 337
Reviewing the Structure of Each Table 342
Refining All Foreign Keys 343
Elements of a Foreign Key 343
Establishing Relationship Characteristics 349
Defining a Deletion Rule for Each Relationship 349
Identifying the Type of Participation for Each Table 354
Identifying the Degree of Participation for Each Table 357
Verifying Table Relationships with Users and Management 360
A Final Note 360
Relationship-Level Integrity 361
Summary 366
Review Questions 368
Chapter 11: Business Rules 369
Topics Covered in This Chapter 369
What Are Business Rules? 370
Types of Business Rules 373
Categories of Business Rules 375
Field-Specific Business Rules 375
Relationship-Specific Business Rules 376
Defining and Establishing Business Rules 378
Working with Users and Management 378
Defining and Establishing Field-Specific Business Rules 379
Defining and Establishing Relationship-Specific Business Rules 386
Validation Tables 394
What Are Validation Tables? 394
Using Validation Tables to Support Business Rules 395
Reviewing the Business Rule Specifications Sheets 400
Summary 408
Review Questions 409
Chapter 12: Views 411
Topics Covered in This Chapter 411
What Are Views? 411
Anatomy of a View 413
Data View 413
Aggregate View 418
Validation View 422
Determining and Defining Views 424
Working with Users and Management 425
Defining Views 426
Reviewing the Documentation for Each View 434
Summary 441
Review Questions 442
Chapter 13: Reviewing Data Integrity 445
Topics Covered in This Chapter 445
Why You Should Review Data Integrity 446
Reviewing and Refining Data Integrity 446
Table-Level Integrity 447
Field-Level Integrity 447
Relationship-Level Integrity 448
Business Rules 448
Views 448
Assembling the Database Documentation 449
Done at Last! 451
Summary 452
PART III: OTHER DATA BASE DESIGN ISSUES 453
Chapter 14: Bad Design-What Not to Do 455
Topics Covered in This Chapter 455
"Flat-File" Design 456
Spreadsheet Design 457
Dealing with the Spreadsheet View Mindset 459
Database Design Based on the Database Software 461
A Final Thought 463
Summary 463
Chapter 15: Bending or Breaking the Rules 465
Topics Covered in This Chapter 465
When May You Bend or Break the Rules? 465
Designing an Analytical Database 465
Improving Processing Performance 466
Documenting Your Actions 469
Summary 471
Chapter 16: In Closing 473

PART IV: APPENDIXES 475
Appendix A: Answers to Review Questions 477
Appendix B: Diagram of the Database Design Process 501
Appendix C: Design Guidelines 519
Appendix D: Documentation Forms 529
Appendix E: Database-Design Diagram Symbols 533
Appendix F: Sample Designs 535
Appendix G: On Normalization 541
Appendix H: Recommended Reading 551
Glossary 553
References 567
Index 569