Database Design for Mere Mortals: 25th Anniversary Edition

by Michael J Hernandez

Overview

The #1 Easy, Commonsense Guide to Database Design—Now Updated


Foreword by Michelle Poolet, Mount Vernon Data Systems LLC




Database Design for Mere Mortals has earned worldwide respect as the simplest way to learn relational database design. Now, this hands-on, software independent tutorial is even clearer and easier to use.




Step by step, this new 25th Anniversary Edition shows you how to design modern databases that are soundly structured, reliable, and flexible, even in the latest online applications. Michael Hernandez guides you through everything from planning to defining tables, fields, keys, table relationships, business rules, and views. You will learn practical ways to improve data integrity, how to avoid common mistakes, and when to break the rules. Updated review questions and figures help you learn these techniques more easily and effectively.

• Understand database types, models, and design terminology
• Perform interviews to efficiently capture requirements—even if everyone works remotely
• Set clear design objectives and transform them into effective designs
• Analyze a current database so you can identify ways to improve it
• Establish table structures and relationships, assign primary keys, set field specifications, and set up views
• Ensure the correct level of data integrity for each database
• Identify and establish business rules
• Preview and prepare for the future of relational databases

Whatever relational database systems you use, Hernandez will help you design databases that are robust and trustworthy. Never designed a database before? Settling for inadequate generic designs? Running existing databases that need improvement? Start here.

Product Details

ISBN-13:	9780136788041
Publisher:	Pearson Education
Publication date:	12/17/2020
Series:	For Mere Mortals
Edition description:	4th ed.
Pages:	640
Sales rank:	528,141
Product dimensions:	7.40(w) x 9.00(h) x 1.30(d)

About the Author

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.

Table of Contents

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