Hernandez, M: Database Design for Mere Mortals

Guidelines 91 Interviewer Guidelines (These Are for You) 93 The Case Study: Mike's Bikes 98 Defining the Mission Statement 100 The Well-Written Mission Statement 100 Composing a Mission Statement 102 Defining the Mission Objectives 105 Well-Written Mission Objectives 106 Composing Mission Objectives 108 Summary 112 Review Questions 113 Chapter 6: Analyzing the Current Database 115 Topics Covered in This Chapter 115 Getting to Know the Current Database 115 Paper-Based Databases 118 Legacy Databases 119 Conducting the Analysis 121 Looking at How Data Is Collected 121 Looking at How Information Is Presented 125 Conducting Interviews 129 Basic Interview Techniques 130 Before You Begin the Interview Process ... 137 Interviewing Users 137 Reviewing Data Type and Usage 138 Reviewing the Samples 140 Reviewing Information Requirements 144 Interviewing Management 152 Reviewing Current Information Requirements 153 Reviewing Additional Information Requirements 154 Reviewing Future Information Requirements 155 Reviewing Overall Information Requirements 155 Compiling a Complete List of Fields 157 The Preliminary Field List 157 The Calculated Field List 164 Reviewing Both Lists with Users and Management 165 Case Study 166 Summary 171 Review Questions 172 Chapter 7: Establishing Table Structures 175 Topics Covered in This Chapter 175 Defining the Preliminary Table List 176 Identifying Implied Subjects 176 Using the List of Subjects 178 Using the Mission Objectives 182 Defining the Final Table List 184 Refining the Table Names 186 Indicating the Table Types 192 Composing the Table Descriptions 192 Associating Fields with Each Table 199 Refining the Fields 202 Improving the Field Names 202 Using an Ideal Field to Resolve Anomalies 206 Resolving Multipart Fields 210 Resolving Multivalued Fields 212 Refining the Table Structures 219 A Word about Redundant Data and Duplicate Fields 219 Using an Ideal Table to Refine Table Structures 220 Establishing Subset Tables 228 Case Study 233 Summary 240 Review Questions 242 Chapter 8: Keys 243 Topics Covered in This Chapter 243 Why Keys Are Important 244 Establishing Keys for Each Table 244 Candidate Keys 245 Primary Keys 253 Alternate Keys 260 Non-keys 261 Table-Level Integrity 261 Reviewing the Initial Table Structures 261 Case Study 263 Summary 269 Review Questions 270 Chapter 9: Field Specifications 273 Topics Covered in This Chapter 273 Why Field Specifications Are Important 274 Field-Level Integrity 275 Anatomy of a Field Specification 277 General Elements 277 Physical Elements 285 Logical Elements 292 Using Unique, Generic, and Replica Field Specifications 300 Defining Field Specifications for Each Field in the Database 306 Case Study 308 Summary 310 Review Questions 311 Chapter 10: Table Relationships 313 Topics Covered in This Chapter 313 Why Relationships Are Important 314 Types of Relationships 315 One-to-One Relationships 316 One-to-Many Relationships 319 Many-to-Many Relationships 321 Self-Referencing Relationships 329 Identifying Existing Relationships 333 Establishing Each Relationship 344 One-to-One and One-to-Many Relationships 345 The Many-to-Many Relationship 352 Self-Referencing Relationships 358 Reviewing the Structure of Each Table 364 Refining All Foreign Keys 365 Elements of a Foreign Key 365 Establishing Relationship Characteristics 372 Defining a Deletion Rule for Each Relationship 372 Identifying the Type of Participation for Each Table 377 Identifying the Degree of Participation for Each Table 380 Verifying Table Relationships with Users and Management 383 A Final Note 383 Relationship-Level Integrity 384 Case Study 384 Summary 389 Review Questions 391 Chapter 11: Business Rules 393 Topics Covered in This Chapter 393 What Are Business Rules? 393 Types of Business Rules 397 Categories of Business Rules 399 Field-Specific Business Rules 399 Relationship-Specific Business Rules 401 Defining and Establishing Business Rules 402 Working with Users and Management 402 Defining and Establishing Field-Specific Business Rules 403 Defining and Establishing Relationship-Specific Business Rules 412 Validation Tables 417 What Are Validation Tables? 419 Using Validation Tables to Support Business Rules 420 Reviewing the Business Rule Specifications Sheets 425 Case Study 426 Summary 431 Review Questions 434 Chapter 12: Views 435 Topics Covered in This Chapter 435 What Are Views? 435 Anatomy of a View 437 Data View 437 Aggregate View 442 Validation View 446 Determining and Defining Views 448 Working with Users and Management 449 Defining Views 450 Reviewing the Documentation for Each View 458 Case Study 460 Summary 465 Review Questions 466 Chapter 13: Reviewing Data Integrity 469 Topics Covered in This Chapter 469 Why You Should Review Data Integrity 470 Reviewing and Refining Data Integrity 470 Table-Level Integrity 471 Field-Level Integrity 471 Relationship-Level Integrity 472 Business Rules 472 Views 473 Assembling the Database Documentation 473 Done at Last! 475 Case Study-Wrap-Up 475 Summary 476 Part III: Other Database Design Issues 477 Chapter 14: Bad Design-What Not to Do 479 Topics Covered in This Chapter 479 Flat-File Design 480 Spreadsheet Design 481 Dealing with the Spreadsheet View Mind-set 483 Database Design Based on the Database Software 485 A Final Thought 486 Summary 487 Chapter 15: Bending or Breaking the Rules 489 Topics Covered in This Chapter 489 When May You Bend or Break the Rules? 489 Designing an Analytical Database 489 Improving Processing Performance 490 Documenting Your Actions 493 Summary 495 In Closing 497 Part IV: Appendixes 499 Appendix A: Answers to Review Questions 501 Chapter 1 501 Chapter 2 502 Chapter 3 504 Chapter 4 505 Chapter 5 506 Chapter 6 508 Chapter 7 510 Chapter 8 513 Chapter 9 516 Chapter 10 518 Chapter 11 520 Chapter 12 521 Appendix B: Diagram of the Database Design Process 525 Appendix C: Design Guidelines 543 Defining and Establishing Field-Specific Business Rules 543 Defining and Establishing Relationship-Specific Business Rules 543 Elements of a Candidate Key 544 Elements of a Foreign Key 544 Elements of a Primary Key 545 Rules for Establishing a Primary Key 545 Elements of the Ideal Field 545 Elements of the Ideal Table 546 Field-Level Integrity 546 Guidelines for Composing a Field Description 547 Guidelines for Composing a Table Description 547 Guidelines for Creating Field Names 548 Guidelines for Creating Table Names 548 Identifying Relationships 549 Identifying View Requirements 549 Interview Guidelines 550 Participant Guidelines 550 Interviewer Guidelines 550 Mission Statements 551 Mission Objectives 551 Relationship-Level Integrity 551 Resolving a Multivalued Field 552 Table-Level Integrity 552 Appendix D: Documentation Forms 553 Appendix E: Database Design Diagram Symbols 557 Appendix F: Sample Designs 559 Appendix G: On Normalization 567 Please Note ... 568 A Brief Recap 569 How Normalization Is Integrated into My Design Methodology 572 Logical Design versus Physical Design and Implementation 575 Appendix H: Recommended Reading 577 Glossary 579 References 595 Index 597