Database Design for Mere Mortals
A Hands-On Guide to Relational Database Design
3rd Edition
Published on 27. July 2021
672 pages
978-0-13-312227-5 (ISBN)
Description
The #1 Easy, Commonsense Guide to Database Design! Michael J. Hernandez's best-selling Database Design for Mere Mortals (R) has earned worldwide respect as the clearest, simplest way to learn relational database design. Now, he's made this hands-on, software-independent tutorial even easier, while ensuring that his design methodology is still relevant to the latest databases, applications, and best practices. Step by step, Database Design for Mere Mortals (R), Third Edition, shows you how to design databases that are soundly structured, reliable, and flexible, even in modern web applications. Hernandez guides you through everything from database planning to defining tables, fields, keys, table relationships, business rules, and views. You'll learn practical ways to improve data integrity, how to avoid common mistakes, and when to break the rules.
Coverage includes
Understanding database types, models, and design terminology
Discovering what good database design can do for you-and why bad design can make your life miserable
Setting objectives for your database, and transforming those objectives into real designs
Analyzing a current database so you can identify ways to improve it
Establishing table structures and relationships, assigning primary keys, setting field specifications, and setting up views
Ensuring the appropriate level of data integrity for each application
Identifying and establishing business rules
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.
More details
Series
Edition
3rd edition
Language
English
Place of publication
Boston
United States
Publishing group
Pearson Education (US)
Target group
College/higher education
File size
18,12 MB
ISBN-13
978-0-13-312227-5 (9780133122275)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Classification
Other editions
New editions
E-Book
03/2021
4th Edition
Addison Wesley
€51.49
Available for download
Additional editions
Book
02/2013
3rd Edition
Addison Wesley
€50.12
Shipment within 10-20 days
Person
Michael J. Hernandez, a relational database developer with more than twenty years of experience, was a program manager and product manager for Microsoft's Visual Studio group. He has been a premier instructor with organizations such as AppDev Training Co., Focal Point, Inc., and Deep Training, and was a top-rated speaker at technical conferences across the United States, Europe, and South America. With John L. Viescas, he coauthored SQL Queries for Mere Mortals (R), Second Edition (Addison-Wesley, 2008).
Content
Foreword xxi Preface xxv
Acknowledgments xxvii
Introduction xxix
Part I: Relational Database Design 1
Chapter 1: The Relational Database 3
Topics Covered in This Chapter 3
Types of Databases 4
Early Database Models 5
The Hierarchical Database Model 5
The Network Database Model 9
The Relational Database Model 12
Retrieving Data 15
Advantages of a Relational Database 16
Relational Database Management Systems 18
Beyond the Relational Model 19
What the Future Holds 21
A Final Note 22
Summary 22
Review Questions 24
Chapter 2: Design Objectives 25
Topics Covered in This Chapter 25
Why Should You Be Concerned with Database Design? 25
The Importance of Theory 27
The Advantage of Learning a Good Design Methodology 29
Objectives of Good Design 30
Benefits of Good Design 31
Database Design Methods 32
Traditional Design Methods 32
The Design Method Presented in This Book 34
Normalization 35
Summary 38
Review Questions 39
Chapter 3: Terminology 41
Topics Covered in This Chapter 41
Why This Terminology Is Important 41
Value-Related Terms 43
Data 43
Information 43
Null 45
The Value of Nulls 46
The Problem with Nulls 47
Structure-Related Terms 49
Table 49
Field 52
Record 53
View 54
Keys 56
Index 58
Relationship-Related Terms 59
Relationships 59
Types of Relationships 60
Types of Participation 65
Degree of Participation 66
Integrity-Related Terms 67
Field Specification 67
Data Integrity 68
Summary 69
Review Questions 70
Part II: The Design Process 73
Chapter 4: Conceptual Overview 75
Topics Covered in This Chapter 75
The Importance of Completing the Design Process 76
Defining a Mission Statement and Mission Objectives 77
Analyzing the Current Database 78
Creating the Data Structures 80
Determining and Establishing Table Relationships 81
Determining and Defining Business Rules 81
Determining and Defining Views 83
Reviewing Data Integrity 83
Summary 84
Review Questions 86
Chapter 5: Starting the Process 89
Topics Covered in This Chapter 89
Conducting Interviews 89
Participant 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
Acknowledgments xxvii
Introduction xxix
Part I: Relational Database Design 1
Chapter 1: The Relational Database 3
Topics Covered in This Chapter 3
Types of Databases 4
Early Database Models 5
The Hierarchical Database Model 5
The Network Database Model 9
The Relational Database Model 12
Retrieving Data 15
Advantages of a Relational Database 16
Relational Database Management Systems 18
Beyond the Relational Model 19
What the Future Holds 21
A Final Note 22
Summary 22
Review Questions 24
Chapter 2: Design Objectives 25
Topics Covered in This Chapter 25
Why Should You Be Concerned with Database Design? 25
The Importance of Theory 27
The Advantage of Learning a Good Design Methodology 29
Objectives of Good Design 30
Benefits of Good Design 31
Database Design Methods 32
Traditional Design Methods 32
The Design Method Presented in This Book 34
Normalization 35
Summary 38
Review Questions 39
Chapter 3: Terminology 41
Topics Covered in This Chapter 41
Why This Terminology Is Important 41
Value-Related Terms 43
Data 43
Information 43
Null 45
The Value of Nulls 46
The Problem with Nulls 47
Structure-Related Terms 49
Table 49
Field 52
Record 53
View 54
Keys 56
Index 58
Relationship-Related Terms 59
Relationships 59
Types of Relationships 60
Types of Participation 65
Degree of Participation 66
Integrity-Related Terms 67
Field Specification 67
Data Integrity 68
Summary 69
Review Questions 70
Part II: The Design Process 73
Chapter 4: Conceptual Overview 75
Topics Covered in This Chapter 75
The Importance of Completing the Design Process 76
Defining a Mission Statement and Mission Objectives 77
Analyzing the Current Database 78
Creating the Data Structures 80
Determining and Establishing Table Relationships 81
Determining and Defining Business Rules 81
Determining and Defining Views 83
Reviewing Data Integrity 83
Summary 84
Review Questions 86
Chapter 5: Starting the Process 89
Topics Covered in This Chapter 89
Conducting Interviews 89
Participant 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
