Database Design for Mere Mortals: 25th Anniversary Edition, 4th edition

  • Michael J. Hernandez

Choose the option that's right for you

$9.99 / mo

4-month term, pay monthly or pay $39.96

Enjoy these features

  • Up to 2 devices
  • Exclusive offers

$14.99 / mo

4-month term, pay monthly or pay $59.96

Enjoy these features

  • Up to 2 devices
  • Exclusive offers

Learn more, spend less

  • Listen on the go

    Learn how you like with full eTextbook audio

  • Learn anytime, anywhere

    Get the app to access your eTextbook whenever you need it

  • Make it your own

    Your notes. Your highlights. Your eTextbook

  • Find it fast

    Quickly navigate your eTextbook with search

  • Stay organized

    Access all your eTextbooks in one place


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.

Published by Addison Wesley (July 14th 2021) - Copyright © 2021

ISBN-13: 9780137459667

Subject: Database

Category: Database Systems

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


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


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


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


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

Your questions answered

Choose from one eTextbook or over 1,500 eTextbooks and study tools, all in one place, for one low monthly subscription. A Single plan includes the use of 1 eTextbook title and study tools. A Multi plan gives you access to more than 1,500 eTextbook titles and study tools. Plus, with the app, put textbooks in your pocket and learn wherever.

Make the most of your study time with offline access, enhanced search, notes and flashcards — to get organized, get the work done quicker and get results.

A Pearson eTextbook is an easy-to-use digital version of the book. You can add notes and highlights, plus learn on the go with the Pearson+ mobile app. Listen on the go with the audiobook feature, available for most titles.

When you choose a plan, you're signing up for a 4-month 'term'. You can opt to make a one-time payment for the initial 4-month term or pay monthly. If you opt for monthly payments, we will charge your payment method each month until your 4-month term has ended. You can turn on auto-renew in My account at any time to continue your subscription before your 4-month term has ended.

When you purchase a Pearson+ subscription, it will last 4 months. Before your initial 4-month term ends, you can extend your subscription by turning auto-renew on in My account.

If you turn auto-renew on, we’ll automatically renew your subscription and charge you every month until you turn off auto-renew. If you made a one-time payment for your initial 4-month term, you’ll now pay monthly.

To avoid the next payment charge, make sure you turn auto renewal off 1 day before the auto renewal date. You can subscribe again after auto-renew has been turned off by purchasing another Pearson+ subscription. We use your credit card to renew your subscription automatically. To make sure your learning is uninterrupted, please check your card details before your first monthly payment.

With a Multi Pearson+ subscription plan, you can download up to 10 titles from your library on each of your authorized smartphone and tablet devices every month.

When you’re using your Multi Pearson+ subscription plan in a browser, you can select and read from as many titles as you like.