Database Design for Mere Mortals

Michael J. Hernandez is a man with many talents and has done quite a bit in his life, and he continues to do many things to keep him busy. You could say he's a modern-day Renaissance Man. For instance, he has been an independent database consultant with more than 25 years of experience in the technology industry, and has been a contributing author to a wide variety of magazine articles, white papers, books, and periodicals. Mike has also been a top-rated and noted technical instructor for two national training organizations, the government, the military, the private sector, and companies throughout the United States, and he has been a top-rated speaker and presenter at numerous national and international conferences. He also worked full-time at Microsoft for seven years as a program manager, product manager, and marketing manager in the Visual Studio Group. Mike is mainly pursuing his artistic side at this point in his life. He is currently a professional actor (with more than seven years of experience as of this writing), performing in commercials for the likes of U.S. Bank, Allstate Insurance, and Subaru; in TV for shows such as Veep, American Crime, and Unbelievable; and feature films such as Ernesto's Manifesto, The Honor List, and Almost Home.

• 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