Database Design For Mere Mortals®: A Hands On Guide To Relational Michael J. Hernandez Mortals D

User Manual:

Open the PDF directly: View PDF .
Page Count: 660

Download
Open PDF In Browser	View PDF

Database Design
for Mere
Mortals®
Third Edition

This page intentionally left blank

Database Design
for Mere
Mortals®
A Hands-on Guide to Relational
Database Design
Third Edition

Michael J. Hernandez

Upper Saddle River, NJ • Boston • Indianapolis • San Francisco
New York • Toronto • Montreal • London • Munich • Paris • Madrid
Capetown • Sydney • Tokyo • Singapore • Mexico City

Many of the designations used by manufacturers and sellers to distinguish their
products are claimed as trademarks. Where those designations appear in this
book, and the publisher was aware of a trademark claim, the designations have
been printed with initial capital letters or in all capitals.
The author and publisher have taken care in the preparation of this book, but
make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for incidental or consequential
damages in connection with or arising out of the use of the information or programs contained herein.
For information about buying this title in bulk quantities, or for special sales
opportunities (which may include electronic versions; custom cover designs; and
content particular to your business, training goals, marketing focus, or branding
interests), please contact our corporate sales department at corpsales@pearsoned.
com or (800) 382-3419.
For government sales inquiries, please contact governmentsales@pearsoned.com.
For questions about sales outside the U.S., please contact international@
pearsoned.com.
Visit us on the Web: informit.com/aw
Cataloging-in-Publication Data is on file with the Library of Congress.
Copyright © 2013 by Michael J. Hernandez
All rights reserved. Printed in the United States of America. This publication is
protected by copyright, and permission must be obtained from the publisher prior
to any prohibited reproduction, storage in a retrieval system, or transmission in
any form or by any means, electronic, mechanical, photocopying, recording, or
likewise. To obtain permission to use material from this work, please submit a
written request to Pearson Education, Inc., Permissions Department, One Lake
Street, Upper Saddle River, New Jersey 07458, or you may fax your request to
(201) 236-3290.
ISBN-13: 978-0-321-88449-7
ISBN-10: 0-321-88449-3
Text printed in the United States on recycled paper at Edwards Brothers Malloy in
Ann Arbor, Michigan.
Third printing, October 2014

For my wife, who has always believed in me and continues
to do so.
To those who have helped me along my journey—teachers,
mentors, friends, and colleagues.
Dedicated to anyone who has unsuccessfully attempted
to design a relational database.

This page intentionally left blank

About the Author
Michael J. Hernandez has been an independent relational database consultant specializing in relational database design. He has more
than twenty years of experience in the technology industry, developing database applications for a broad range of clients. He’s been a
contributing author to a wide variety of
magazine columns, white papers, books, and
periodicals, and is coauthor of the best-selling
SQL Queries for Mere Mortals® (Addison-Wesley, 2007). Mike has been a
top-rated and noted technical trainer for the government, the military,
the private sector, and companies throughout the United States. He
has spoken at numerous national and international conferences, and
has consistently been a top-rated speaker and presenter.
Aside from his technical background, Mike has a diverse set of skills
and interests that he also pursues, ranging from the artistic to the
metaphysical. His greatest interest is still the guitar, as he’s been a
practicing guitarist for more than forty years and played professionally for fifteen years. He is a great cook, loves to teach (writing, public
speaking, music), has a gift for bad puns, and even reads tarot cards.
He says he’s never going to retire, per se, but rather just change whatever it is he’s doing whenever he finally gets tired of it and move on to
something else that interests him.

vii

This page intentionally left blank

Contents

Foreword
Preface

xxi
xxv

Acknowledgments
Introduction

xxvii

xxix

What’s New in the Third Edition
xxxii
Who Should Read This Book
xxxii
The Purpose of This Book
xxxiv
How to Read This Book
xxxvi
How This Book Is Organized
xxxvii
Part I: Relational Database Design
xxxvii
Part II: The Design Process
xxxvii
Part III: Other Database Design Issues
xxxix
Part IV: Appendixes
xxxix
A Word About the Examples and Techniques in This Book
A New Approach to Learning
xli

PART I: RELATIONAL DATABASE DESIGN
Chapter 1: The Relational Database
Topics Covered in This Chapter
3
Types of Databases
4
Early Database Models
5
The Hierarchical Database Model
The Network Database Model
9

Contents

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

Topics Covered in This Chapter
25
Why Should You Be Concerned with Database Design?
The Importance of Theory
27
The Advantage of Learning a Good Design Methodology
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

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

25
29

Contents

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

Chapter 4: Conceptual Overview

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

xii

Contents

Chapter 5: Starting the Process

Topics Covered in This Chapter
89
Conducting Interviews
89
Participant Guidelines
91
Interviewer Guidelines (These Are for You)
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

Contents

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

xiii

xiv

Contents

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

Contents

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
Defining and Establishing Relationship-Specific Business
Rules
412

403

xvi

Contents

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
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

458

Contents

Case Study—Wrap-Up
Summary
476

475

PART III: OTHER DATABASE DESIGN ISSUES
Chapter 14: Bad Design—What Not to Do

477
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
Chapter
Chapter
Chapter
Chapter
Chapter
Chapter
Chapter

1
2
3
4
5
6
7

501
502
504
505
506
508
510

501

xvii

xviii

Chapter
Chapter
Chapter
Chapter
Chapter

Contents

8
9
10
11
12

513
516
518
520
521

Appendix B: Diagram of the Database Design Process
Appendix C: Design Guidelines

525

543

Defining and Establishing Field-Specific Business Rules
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

543

Contents

Appendix E: Database Design Diagram Symbols
Appendix F: Sample Designs
Appendix G: On Normalization

xix

557

559
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
Glossary

579

References
Index

597

595

577

This page intentionally left blank

Foreword

To the Third Edition
Here it is, ten years later, and Mike and I cross paths even less than
we used to. For those who were unaware, we share the same birthday (although he’s much older than me, at least one full year), and we
meet up at least once each year and congratulate ourselves for making
it another year. It’s also funny how Microsoft “reboots” its technology
every ten years or so, and now, revisiting the foreword I wrote ten years
ago, nothing much has changed—I’m still hip-deep in a new Microsoft
technology, but this time it’s all about WinRT and Windows 8, rather
than .NET. One thing that hasn’t changed, however, is the need for
carefully planned and executed database design. Nothing Mike wrote
in his original volume has changed very much, and although this
new edition modifies some details, the basics of good database design
haven’t changed in the ensuing ten years. I must confess a little jealousy that Mike has written a book with such enduring shelf life, but, if
he’s going to have a book that succeeds for this many years, at least it’s
a good one. Whether this is your first visit to Mike’s detailed explanation of database design, or your second or third, be assured that you’ll
find a carefully considered, helpful path through the vagaries of database design here. But let’s get past the intro, and get to work!
—Ken Getz, November 14, 2012
From the Second Edition . . .
I don’t see Mike Hernandez as much as I used to. Both our professional lives have changed a great deal since I first wrote the foreword
to his original edition. If nothing else, we travel less, and our paths
cross less often than they did. If you’ll indulge me, I might try to add
that the entire world has changed since that first edition. On the most

xxi

xxii

Foreword

mundane level, my whole development life has changed, since I’ve
bought into this Microsoft .NET thing whole-heartedly and full-time.
One thing that hasn’t changed, however, is the constant need for data,
and well-designed data. Slapping together sophisticated applications
with poorly designed data will hurt you just as much now as when
Mike wrote his first edition—perhaps even more. Whether you’re just
getting started developing with data, or are a seasoned pro; whether
you’ve read Mike’s previous book, or this is your first time; whether
you’re happier letting someone else design your data, or you love doing
it yourself—this is the book for you. Mike’s ability to explain these concepts in a way that’s not only clear, but fun, continues to amaze me.
—Ken Getz, October 10, 2002
From the First Edition . . .
Perhaps you’re wondering why the world needs another book on database design. When Mike Hernandez first discussed this book with me,
I wondered. But the fact is—as you may have discovered from leafing
through pages before landing here in the foreword—the world does
need a book like this one. You can certainly find many books detailing
the theories and concepts behind the science of database design, but
you won’t find many (if any) written from Mike’s particular perspective. He has made it his goal to provide a book that is clearly based
on the sturdy principles of mathematical study, but has geared it
toward practical use instead of theoretical possibilities. No matter what
specific database package you’re using, the concepts in this book will
make sense and will apply to your database-design projects.
I knew this was the book for me when I turned to the beginning of
Chapter 6 and saw this suggestion:
Do not adopt the current database structure as the basis for the new
database structure.

Foreword

xxiii

If I’d had someone tell me this when I was starting out on this database developer path years ago I could have saved a ton of time! And
that’s my point here: Mike has spent many years designing databases
for clients; he has spent lots of time thinking, reading, and studying
about the right way to create database applications; and he has put it
all here, on paper, for the rest of us.
This book is full of the right stuff, illustrated with easy-to-understand
examples. That’s not to say that it doesn’t contain the hardcore information you need to do databases right—it does, of course. But it’s
geared toward real developers, not theoreticians.
I’ve spent some time talking with Mike about database design. Over
coffee, in meetings, writing courseware, it’s always the same: Mike is
passionate about this material. Just as the operating system designer
seeks the perfect, elegant algorithm, Mike spends his time looking for
just the right way to solve a design puzzle and—as you will read in
this book—how best to explain it to others. I’ve learned much of what
I know about database design from Mike over the years and feel sure
that I have a lot more to learn from this book. After reading through
this concise, detailed presentation of the information you need to know
in order to create professional databases, I’m sure you’ll feel the same
way.
—Ken Getz, MCW Technologies (KenG@mcwtech.com)

This page intentionally left blank

Preface
Life, as the most ancient
of all metaphors insists, is a journey . . .
—JONATHAN R ABAN,
FOR L OVE AND MONEY

DATABASE

Paths may change and
the course may need adjustment,
but the journey continues . . .
—MICHAEL J. HERNANDEZ
DESIGN FOR MERE MORTALS®, SECOND EDTION

To say that the technology field, and database management in particular, has changed significantly in the nine years since the second
edition of this book was published would be an understatement, to be
sure. Small, handheld devices containing storage capacity and processing power that once would have required several room-sized mainframe computers are now so ubiquitous that many people take them
for granted, especially the more recent generations. (My young nephew
would likely never understand the excitement I experienced when I
purchased my first 40MB storage expansion card for my IBM PC. But
that’s another story.) Database management systems can now handle
terabytes of data, and there’s recently been a considerable amount of
emphasis on storing, managing, and accessing data “in the cloud.”
Is there still a need, then, for a book such as the one you hold in your
hands? Absolutely! Regardless of how complex or complicated database
management becomes, there will always be a need for a book on the
basics of database design. You must learn the fundamentals in order
to know how and why things work the way they do. This is true of
many other areas of expertise, whether they are technical disciplines
such as architectural design and engineering or artistic disciplines
such as music and cooking.

xxv

xxvi

Preface

My journey has taken me along new and different paths in recent
years, and I’m really enjoying what I do. I’ve been doing a lot more
writing lately, which is why I thought it was time to do this new edition. I thought I’d share some new nuggets of information I’ve learned
along the way and perhaps clarify my perspectives on this subject a
little more. Now that I’ve completed this work, I can’t wait to see where
my journey takes me next.

An important note to readers:
Visit Informit.com/titles/0321884493 to access additional
content referenced in the book.

Acknowledgments
Writing is truly a cooperative effort, despite what you may have heard
about it. I’m so grateful for the editors, colleagues, friends, and family
who continue to be ready and willing to lend their help. These are the
people who provided encouragement and kept me focused on the task
at hand, and it is to them that I extend my most heartfelt appreciation.
First and foremost, I want to thank my wonderful editor, Joan Murray, for the opportunity to write yet another edition of my book. We
had been talking about this project for a couple of years, and it was
her perseverance, patience, kindness, and leadership that helped me
decide to take on this work and bring it to successful completion. I also
want to thank production editor Caroline Senay for guiding the author
review process with such a deft hand and copy editor Audrey Doyle for
her precise and detailed review of the content. And a special thanks
to John Fuller and his production staff—they did great work, as they
always do! I’ve always had a wonderful relationship with the AddisonWesley team, and I just can’t imagine why I’d ever want to write technical books for anyone else.
Next, I’d like to acknowledge my distinguished technical review team:
Tracy Thornton, Tony Wiggins, and Theodor Richardson. These folks
graciously and generously gave their time, effort, and expertise to provide me with a wealth of valuable feedback and suggestions. This book
definitely benefitted from their contributions. My thanks once again to
each of you for your time and input and for helping to make this edition even better than I first envisioned.

xxvii

xxviii

Acknowledgments

I want to extend a very special thanks to Ken Getz for once again
providing the foreword for my book. Ken is a well-respected expert, a
colleague, and a good friend. I’m so pleased to have his thoughts and
comments at the beginning of the book.
A special thanks also goes to all of those readers who took the time to
send me their thoughts and comments. I am humbled by their praise
and support and particularly appreciative of the good, constructive
criticism that eventually helped me to improve the material in this
edition. I also wish to thank all the academic institutions, government
agencies, and commercial organizations that have adopted my book
and made it “standard reading” for those just beginning their database
careers. I am honored by their support of my work.
Finally, I want to thank my wife for her unending patience while I was
enmeshed in my writing. Her help and support have been invaluable,
and once again, I owe her a great debt. I would tell you exactly how I
feel about her, but she abhors any sort of PDA (public display of affection). Instead, I’ll just extend her a laurel and hardy handshake.

Introduction
Plain cooking cannot be entrusted to plain cooks.
—COUNTESS MORPHY

In the past, the process of designing a database has been a task
performed by people in information technology (IT) departments and
professional database developers. These people usually had mathematical, computer science, or systems design backgrounds and typically
worked with large mainframe databases. Many of them were experienced programmers and had coded a number of database application
programs consisting of thousands of lines of code. (And these people
were usually very overworked due to the nature and importance of
their work!)
People designing database systems at that time needed to have a solid
educational background because most of the systems they created
were meant to be used companywide. Even when creating databases
for single departments within a company or for small businesses, database designers still required extensive formal training because of the
complexity of the programming languages and database application
programs they were using. As technology advanced, however, those
educational requirements evolved.
Database software programs have evolved quite a bit since the 1980s,
too. Many vendors developed software that ran on desktop computers
and could be more easily programmed to collect, store, and manage
data than their mainframe counterparts. As computing power and
demand for complexity grew, vendors produced software that allowed
groups of people to access and share centralized data within a variety

xxix

xxx

Introduction

of environments, such as client/server architectures on computers
connected within local area networks (LANs) and wide area networks
(WANs). People within a company or organization were no longer
strictly dependent on mainframe databases or on having their information needs met by centralized IT departments.
The emergence and wide use of the laptop computer and the evolution and greater acceptance of the Internet have also played a part in
database software development. Laptops have become quite powerful,
with gigabytes of memory and storage, and extremely fast processing
power. They’ve become so ubiquitous that they’ve all but replaced the
desktop computer in many environments. They’ve also allowed people
to be connected to the Internet even in such mundane places as coffee
shops, restaurants, and airports. (And I won’t even mention the plethora of other devices that now allow the same type of access—that’s for
another book and another discussion.) As such, there’s been a greater
push by both software vendors and businesses to run database software and manage databases from the Internet, thus allowing people
to access their applications and data from anywhere at any time. It
will be interesting to see how this whole idea progresses over the next
several years.
Vendors continue to add new features and enhance the tool sets in
their database software, enabling database developers to create more
powerful and flexible database applications. They’re also constantly
improving the ease with which the software can be used, enabling
many people to create their own database applications. Today’s database software greatly simplifies the process of creating efficient database structures and intuitive user interfaces.
Most programs provide sample database structures that you can copy
and alter to suit your specific needs. Although you might initially
think that it would be quite advantageous for you to use these sample structures as the basis for a new database, you should stop and

Introduction

xxxi

reconsider that move for a moment. Why? Because you could easily
and unwittingly create an improper, inefficient, and incomplete design.
Then you would eventually encounter problems in what you believed to
be a dependable database design. This, of course, raises the question,
“What types of problems would I encounter?”
Most problems that surface in a database fall into two categories:
application problems and data problems. Application problems include
such things as problematic data entry/edit forms, confusing menus
and toolbars, confusing dialog boxes, and tedious task sequences.
These problems typically arise when the database developer is inexperienced, is unfamiliar with a good application design methodology, or
knows too little about the software he’s using to implement the database. Problems of this nature are common and important to address,
but they are beyond the scope of this work.

❖ Note One good way to solve many of your application problems is to purchase and study third-party “developer” books that
cover the software you’re using. Such books discuss application
design issues, advanced programming techniques, and various
tips and tricks that you can use to improve and enhance an
application. Armed with these new skills, you can revamp and
fine-tune the database application so that it works correctly,
smoothly, and efficiently.

Data problems, on the other hand, include such things as missing
data, incorrect data, mismatched data, and inaccurate information.
Poor database design is typically the root cause of these types of problems. A database will not fulfill an organization’s information requirements if it is not structured properly. Although poor design is typically
generated by a database developer who lacks knowledge of good database design principles, it shouldn’t necessarily reflect negatively on

xxxii

Introduction

the developer. Many people, including experienced programmers and
database developers, have had little or no instruction in any form of
database design methodology. Many are unaware that design methodologies even exist. Data problems and poor design are the issues that
this work will address.

What’s New in the Third Edition
I revised this edition to improve readability, update or extend existing
topics, add new content, and enhance its educational value. Here is a
list of the changes you’ll find in this edition.
• Portions of the text have been rewritten to improve clarity and
reader comprehension.
• Figures have been updated for improved relevance as
appropriate.
• The discussion on data types has been updated.
• The Recommended Reading section includes the latest editions of
the books and now includes each book’s ISBN.
• A new appendix on Normalization very briefly explains the concept and then explains in detail how it is incorporated into the
design process presented in this book.
Visit Informit.com/titles/0321884493 to access additional content referenced in the book.

Who Should Read This Book
No previous background in database design is necessary to read this
book. The reason you have this book in your hands is to learn how
to design a database properly. If you’re just getting into database

Who Should Read This Book

xxxiii

management and you’re thinking about developing your own databases, this book will be very valuable to you. It’s better that you learn
how to create a database properly from the beginning than that you
learn by trial and error. Believe me, the latter method takes much
longer.
If you fall into the category of those people who have been working
with database programs for a while and are ready to begin developing
new databases for your company or business, you should read this
book. You probably have a good feel for what a good database structure
should look like, but aren’t quite sure how database developers arrive
at an effective design. Maybe you’re a programmer who has created a
number of databases following a few basic guidelines, but you have
always ended up writing a lot of code to get the database to work properly. If this is the case, this book is also for you.
It would be a good idea for you to read this book even if you already
have some background in database design. Perhaps you learned a
design methodology back in college or attended a database class that
discussed design, but your memory is vague about some details, or
there were parts of the design process that you just did not completely
understand. Those points with which you had difficulty will finally
become clear once you learn and understand the design process presented in this book.
This book is also appropriate for those of you who are experienced
database developers and programmers. Although you may already
know many of the aspects of the design process presented here, you’ll
probably find that there are some elements that you’ve never before
encountered or considered. You may even come up with fresh ideas
about how to design your databases by reviewing the material in this
book because many of the design processes familiar to you are presented here from a different viewpoint. At the very least, this book can
serve as a great refresher course in database design.

xxxiv

Introduction

The Purpose of This Book
In general terms, there are three phases to the overall database development process.
1. Logical design: The first phase involves determining and defining tables and their fields, establishing primary and foreign
keys, establishing table relationships, and determining and
establishing the various levels of data integrity.
2. Physical implementation: The second phase entails creating the
tables, establishing key fields and table relationships, and using
the proper tools to implement the various levels of data integrity.
3. Application development: The third phase involves creating an
application that allows a single user or group of users to interact
with the data stored in the database. The application development phase itself can be divided into separate processes, such
as determining end-user tasks and their appropriate sequences,
determining information requirements for report output, and
creating a menu system for navigating the application.
You should always go through the logical design first and execute it as
completely as possible. After you’ve created a sound structure, you can
then implement it within any database software you choose. As you
begin the implementation phase, you may find that you need to modify
the database structure based on the pros and cons or strengths and
weaknesses of the database software you’ve chosen. You may even
decide to make structural modifications to enhance data processing
performance. Performing the logical design first ensures that you
make conscious, methodical, clear, and informed decisions concerning the structure of your database. As a result, you help minimize the
potential number of further structural modifications you might need to
make during the physical implementation and application development
phases.

The Purpose of This Book

xxxv

This book deals with only the logical design phase of the overall development process, and the book’s main purpose is to explain the process
of relational database design without using the advanced, orthodox
methodologies found in an overwhelming majority of database design
books. I’ve taken care to avoid the complexities of these methodologies
by presenting a relatively straightforward, commonsense approach to
the design process. I also use a simple and straightforward data modeling method as a supplement to this approach, and present the entire
process as clearly as possible and with a minimum of technical jargon.
There are many database design books out on the market that include
chapters on implementing the database within a specific database
product, and some books even seem to meld the design and implementation phases together. (I’ve never particularly agreed with the idea
of combining these phases, and I’ve always maintained that a database developer should perform the logical design and implementation
phases separately to ensure maximum focus, effectiveness, and efficiency.) The main drawback that I’ve encountered with these types of
books is that it can be difficult for a reader to obtain any useful or relevant information from the implementation chapters if he or she doesn’t
work with the particular database software or programming language
that the book incorporates. It is for this reason that I decided to write a
book that focuses strictly on the logical design of the database.
This book should be easier to read than other books you may have
encountered on the subject. Many of the database design books on the
market are highly technical and can be difficult to assimilate. I think
most of these books can be confusing and overwhelming if you are not
a computer science major, database theorist, or experienced database
developer. The design principles you’ll learn within these pages are
easy to understand and remember, and the examples are common and
generic enough to be relevant to a wide variety of situations.
Most people I’ve met in my travels around the country have told me
that they just want to learn how to create a sound database structure

xxxvi

Introduction

without having to learn about normal forms or advanced mathematical
theories. Many people are not as worried about implementing a structure within a specific database software program as they are about
learning how to optimize their data structures and how to impose data
integrity. In this book, you’ll learn how to create efficient database
structures, how to impose several levels of data integrity, as well as
how to relate tables together to obtain information in an almost infinite
number of ways. Don’t worry; this isn’t as difficult a task as you might
think. You’ll be able to accomplish all of this by understanding a few
key terms and by learning and using a specific set of commonsense
techniques and concepts.
You’ll also learn how to analyze and leverage an existing database,
determine information requirements, and determine and implement
business rules. These are important topics because many of you will
probably inherit old databases that you’ll need to revamp using what
you’ll learn by reading this book. They’ll also be just as important
when you create a new database from scratch.
When you finish reading this book, you’ll have the knowledge and tools
necessary to create a good relational database structure. I’m confident
that this entire approach will work for a majority of developers and the
databases they need to create.

How to Read This Book
I strongly recommend that you read this book in sequence from beginning to end, regardless of whether you are a novice or a professional.
You’ll keep everything in context this way and avoid the confusion
that generally comes from being unable to see the “big picture” first.
It’s also a good idea to learn the process as a whole before you begin to
focus on any one part.

How This Book Is Organized

xxxvii

If you are reading this book to refresh your design skills, you could
read just those sections that are of interest to you. As much as possible, I’ve tried to write each chapter so that it can stand on its own;
nonetheless, I still recommend that you glance through each chapter
to make sure you’re not missing any new ideas or points on design that
you may not have considered up to now.

How This Book Is Organized
Here’s a brief overview of what you’ll find in each part and each
chapter.

Part I: Relational Database Design
This section provides an introduction to databases, the idea of database design, and some of the terminology you’ll need to be familiar
with in order to learn and understand the design process presented in
this book.
Chapter 1, “The Relational Database,” provides a brief discussion of
the types of databases you’ll encounter, common database models, and
a brief history of the relational database.
Chapter 2, “Design Objectives,” explores why you should be concerned
with design, points out the objectives and advantages of good design,
and provides a brief introduction to Normalization and normal forms.
Chapter 3, “Terminology,” covers the terms you need to know in order
to learn and understand the design methodology presented in this book.

Part II: The Design Process
Each aspect of the database design process is discussed in detail in
Part II, including establishing table structures, assigning primary

xxxviii

Introduction

keys, setting field specifications, establishing table relationships, setting up views, and establishing various levels of data integrity.
Chapter 4, “Conceptual Overview,” provides an overview of the design
process, showing you how the different components of the process fit
together.
Chapter 5, “Starting the Process,” covers how to define a mission
statement and mission objectives for the database, both of which provide you with an initial focus for creating your database.
Chapter 6, “Analyzing the Current Database,” covers issues concerning the existing database. We look at reasons for analyzing the current
database, how to look at current methods of collecting and presenting
data, why and how to conduct interviews with users and management,
and how to compile initial field lists.
Chapter 7, “Establishing Table Structures,” covers topics such as
determining and defining what subjects the database should track,
associating fields with tables, and refining table structures.
Chapter 8, “Keys,” covers the concept of keys and their importance to
the design process, as well as how to define candidate and primary
keys for each table.
Chapter 9, “Field Specifications,” covers a topic that a number of database developers tend to minimize. Besides indicating how each field
is created, field specifications determine the very nature of the values
a field contains. Topics in this chapter include the importance of field
specifications, types of specification characteristics, and how to define
specifications for each field in the database.
Chapter 10, “Table Relationships,” explains the importance of table
relationships, types of relationships, setting up relationships, and
establishing relationship characteristics.

How This Book Is Organized

xxxix

Chapter 11, “Business Rules,” covers types of business rules, determining and establishing business rules, and using validation tables.
Business rules are very important in any database because they provide a distinct level of data integrity.
Chapter 12, “Views,” looks into the concept of views and why they are
important, types of views, and how to determine and set up views.
Chapter 13, “Reviewing Data Integrity,” reviews each level of integrity
that has been defined and discussed in previous chapters. Here you
learn that it’s a good idea to review the final design of the database
structure to ensure that you’ve imposed data integrity as completely as
you can.

Part III: Other Database Design Issues
This section deals with topics such as avoiding bad design and bending the rules set forth in the design process.
Chapter 14, “Bad Design—What Not to Do,” covers the types of designs
you should avoid, such as a flat-file design and a spreadsheet design.
Chapter 15, “Bending or Breaking the Rules,” discusses those rare
instances in which it may be necessary to stray from the techniques
and concepts of the design process. This chapter tells you when you
should consider bending the rules, as well as how it should be done.

Part IV: Appendixes
These appendices provide information that I thought would be valuable
to you as you’re learning about the database design process and when
you’re working on developing your database.
Appendix A, “Answers to Review Questions,” contains the answers to
all of the review questions in Chapters 1 through 12.

Introduction

Appendix B, “Diagram of the Database Design Process,” provides a
diagram that maps the entire database design process.
Appendix C, “Design Guidelines,” provides an easy reference to the
various sets of design guidelines that appear throughout the book.
Appendix D, “Documentation Forms,” provides blank copies of the
Field Specifications, Business Rule Specifications, and View Specifications sheets, which you can copy and use on your database projects.
Appendix E, “Database Design Diagram Symbols,” contains a quick
and easy reference to the diagram symbols used throughout the book.
Appendix F, “Sample Designs,” contains sample database designs that
can serve as the basis for ideas for databases you may want or need to
create.
Appendix G, “On Normalization,” provides a discussion on how I incorporated Normalization into my design methodology.
Appendix H, “Recommended Reading,” provides a list of books that
you should read if you are interested in pursuing an in-depth study of
database technology.
Glossary contains concise definitions of various words and phrases
used throughout the book.

IMPORTANT: READ THIS SECTION!

A Word About the Examples and
Techniques in This Book
You’ll notice that there are a wide variety of examples in this book.
I’ve made sure that they are as generic and relevant as possible. However, you may notice that several of the examples are rather simplified,

A Word About the Examples and Techniques in This Book

xli

incomplete, or occasionally even incorrect. Believe it or not, I created
them that way on purpose.
I’ve created some examples with errors so that I could illustrate specific concepts and techniques. Without these examples, you wouldn’t
see how the concepts or techniques are put to use, as well as the
results you should expect from using them. Other examples are simple
because, once again, the focus is on the technique or concept and not
on the example itself. For instance, there are many ways that you can
design an order-tracking database. However, the structure of the sample order-tracking database I use in this book is simple because the
focus is specifically on the design process, not on creating an elaborate
order-tracking database system.
So what I’m really trying to emphasize here is this:
Focus on the concept or technique and its intended results, not
on the example used to illustrate it.

A New Approach to Learning
Here’s an approach to learning the design process (or pretty much anything else, for that matter) that I’ve found very useful in my database
design classes.
Think of all the techniques used in the design process as a set of tools;
each tool (or technique) is used for a specific purpose. The idea here
is that once you learn how a tool is used generically, you can then use
that tool in any number of situations. The reason you can do this is
because you use the tool the same way in each situation.
Take a Crescent wrench, for example. Generically speaking, you use
a Crescent wrench to fasten and unfasten a nut to a bolt. You open or
close the jaw of the wrench to fit a given bolt by using the adjusting
screw located on the head of the wrench. Now that you’re clear about its
use, try using it on a few bolts. Try it on the legs of an outdoor chair, or

xlii

Introduction

the fan belt cover on an engine, or the side panel of an outdoor cooling
unit, or the hinge plates of an iron gate. Do you notice that regardless of where you encounter a nut and bolt, you can always fasten and
unfasten the nut by using the Crescent wrench in the same manner?
The tools used to design a database work in exactly the same way.
Once you understand how a tool is used generically, it will work the
same way regardless of the circumstances under which it is used. For
instance, consider the tool (or technique) for decomposing a field value.
Say you have a single A DDRESS field in a CUSTOMERS table that contains the street address, city, state, and zip code for a given customer.
You’ll find it difficult to use this field in your database because it
contains more than one item of data; you’ll certainly have a hard time
retrieving information for a particular city or sorting the information
by a specific zip code.
The solution to this apparent dilemma is to decompose the A DDRESS
field into smaller fields. You do this by identifying the distinct items
that make up the value of the field, and then treating each item as its
own separate field. That’s all there is to it! This process constitutes a
“tool” that you can now use on any field containing a value composed
of two or more distinct data items, such as these sample fields. The
following table shows the results of the decomposition process.

Current Field Name

Sample Value

New Field Names

Address

7402 Kingman Dr., Seattle,
WA 98012

Street Address, City,
State, Zip Code

Phone

(206) 555-5555

Area Code, Phone
Number

Name

Michael J. Hernandez

First Name, Middle
Initial, Last Name

EmployeeCode

ITDEV0516

Department, Category,
ID Number

A Word About the Examples and Techniques in This Book

xliii

❖ Note You’ll learn more about decomposing field values in
Chapter 7, “Establishing Table Structures.”

You can use all of the techniques (“tools”) that are part of the design
process presented in this book in the same manner. You’ll be able to
design a sound database structure using these techniques regardless
of the type of database you need to create. Just be sure to remember
this:
Focus on the concept or technique being presented and its
intended results, not on the example used to illustrate it.

This page intentionally left blank

Part I
Relational
Database
Design

This page intentionally left blank

1
The Relational Database
A fish must swim three times—
in water, in butter, and in wine.
—POLISH PROVERB

Topics Covered in This Chapter
Types of Databases
Early Database Models
The Relational Database Model
Relational Database Management Systems
Beyond the Relational Model
What the Future Holds
Summary
Review Questions
The relational database has been in existence for more than 40 years.
It spawned a multibillion-dollar industry, is the most widely used type
of database in the world today, and is an essential part of our everyday
lives. It is very likely that you are using a relational database every
time you purchase goods online or at a local store, make travel plans
with your travel agent, check out books at the library, or make a purchase on the Internet.
Before we delve into the design process, let’s take a look at a brief history of the relational database—where it came from, where it is now,
and where it’s likely to go in the future.

Chapter 1 The Relational Database

Types of Databases
What is a database? As you probably know, a database is an organized
collection of data used for the purpose of modeling some type of organization or organizational process. It really doesn’t matter whether
you’re using paper or a computer application program to collect and
store the data. As long as you’re gathering data in some organized
manner for a specific purpose, you’ve got a database. Throughout the
remainder of this discussion, we’ll assume that you’re using an application program to collect and maintain your data.
There are two types of databases in database management, operational databases and analytical databases.
Operational databases are the backbone of many companies, organizations, and institutions throughout the world. This type of database is
primarily used in online transaction processing (OLTP) scenarios, that
is, in situations where there is a need to collect, modify, and maintain data on a daily basis. The type of data stored in an operational
database is dynamic, meaning that it changes constantly and always
reflects up-to-the-minute information. Organizations such as retail
stores, manufacturing companies, hospitals and clinics, and publishing houses use operational databases because their data is in a constant state of flux.
In contrast, analytical databases are primarily used in online analytical processing (OLAP) scenarios, where there is a need to store and
track historical and time-dependent data. An analytical database is
a valuable asset when there is a need to track trends, view statistical
data over a long period of time, and make tactical or strategic business
projections. This type of database stores static data, meaning that the
data is never (or very rarely) modified. The information gleaned from
an analytical database reflects a point-in-time snapshot of the data.
Chemical labs, geological companies, and marketing analysis firms are
examples of organizations that use analytical databases.

Early Database Models

Analytical databases often use data from operational databases as
their main data source, so there can be some amount of association
between them; nevertheless, operational and analytical databases
fulfill very specific types of data processing needs and creating their
structures requires radically different design methodologies. This book
focuses on designing an operational database because it is still the
most commonly used type of database in the world today.

Early Database Models
In the days before the relational database model, two data models were
commonly used to maintain and manipulate data—the hierarchical
database model and the network database model.

❖ Note I’ve provided a brief overview of each of these models for
historical purposes only. In an overall sense, I believe it is useful for you to know what preceded the relational model so that
you have a basic understanding of what led to its creation and
evolution.
In the following overview I briefly describe how the data in each
model is structured and accessed, how the relationship between
a pair of tables is represented, and one or two of the advantages
or disadvantages of each model.

Some of the terms you’ll encounter in this section are explained in
more detail in Chapter 3, “Terminology.”

The Hierarchical Database Model
Data in this type of database is structured hierarchically and is
typically diagrammed as an inverted tree. A single table in the database acts as the “root” of the inverted tree and other tables act as the

Chapter 1 The Relational Database

branches flowing from the root. Figure 1.1 shows a diagram of a typical hierarchical database structure.

Agents

Entertainers

Schedule

Clients

Engagements

Payments

Figure 1.1 Diagram of a typical hierarchical database

Agents Database
In the example shown in Figure 1.1, an agent books several
entertainers, and each entertainer has his own schedule. An
agent also maintains a number of clients whose entertainment needs are met by the agent. A client books engagements
through the agent and makes payments to him for his services.
A relationship in a hierarchical database is represented by the term
parent/child. In this type of relationship, a parent table can be associated with one or more child tables, but a single child table can be associated with only one parent table. These tables are explicitly linked
via a pointer or by the physical arrangement of the records within
the tables. A user accesses data within this model by starting at the
root table and working down through the tree to the target data. This
access method requires the user to be very familiar with the structure
of the database.

Early Database Models

One advantage to using a hierarchical database is that a user can
retrieve data very quickly because there are explicit links between the
table structures. Another advantage is that referential integrity is built
in and automatically enforced. This ensures that a record in a child
table must be linked to an existing record in a parent table, and that
a record deleted in the parent table will cause all associated records in
the child table to be deleted as well.

❖ Note In the following examples, table names within the text
appear in all capital letters (such as VENDORS) and field names
within the text appear in small capital letters (such as VENDOR ID
NUMBER ).

A problem occurs in a hierarchical database when a user needs to
store a record in a child table that is currently unrelated to any record
in a parent table. Consider an example using the Agents database
shown in Figure 1.1. A user cannot enter a new entertainer in the
ENTERTAINERS table until the entertainer is assigned to an agent
in the AGENTS table. Recall that a record in a child table (in this
case, ENTERTAINERS) must be related to a record in the parent table
(AGENTS). Yet in real life, entertainers commonly sign up with the
agency well before they are assigned to specific agents. This scenario
is difficult to model in a hierarchical database. The rules can be bent
without breaking them if a dummy agent record is inserted in the
AGENTS table; however, this option is not really optimal.
This type of database cannot support complex relationships, and there
is often a problem with redundant data. For example, there is a manyto-many relationship between clients and entertainers—an entertainer
will perform for many clients, and a client will hire many entertainers. You can’t directly model this type of relationship in a hierarchical
database, so you’ll have to introduce redundant data into both the
SCHEDULE and ENGAGEMENTS tables.

Chapter 1 The Relational Database

• The SCHEDULE table will now have client data (such as client
name, address, and phone number) to show for whom and where
each entertainer is performing. This particular data is redundant
because it is currently stored in the CLIENTS table.
• The ENGAGEMENTS table will now contain data on entertainers
(such as entertainer name, phone number, and type of entertainer) to indicate which entertainers are performing for a given
client. This data is redundant as well because it is currently
stored in the ENTERTAINERS table.
The problem with this redundancy is that it opens up the possibility of
allowing a user to enter a single piece of data inconsistently. This, in
turn, can result in producing inaccurate information.
A user can solve this problem in a roundabout manner by creating
one hierarchical database specifically for entertainers and another
specifically for agents. The new Entertainers database will contain
only the ENTERTAINERS table, and the revised Agents database will
contain the AGENTS, CLIENTS, PAYMENTS, and ENGAGEMENTS
tables. The SCHEDULE table is no longer needed in the Entertainers
database because you can define a logical child relationship between
the ENGAGEMENTS table in the Agents database and the ENTERTAINERS table in the Entertainers database. With this relationship in
place, you can retrieve a variety of information, such as a list of booked
entertainers for a given client or a performance schedule for a given
entertainer. Figure 1.2 shows a diagram of the new model.
As you can see, a person designing a hierarchical database must be
able to recognize the need to use this technique for a many-to-many
relationship. Here the need is relatively obvious, but many relationships are more obscure and may not be discovered until very late in
the design process or, more disturbingly, well after the database has
been put into operation.

Early Database Models

Agents Database

Entertainers Database

Agents

Entertainers

Clients

Log
ic
Rela al Child
tion
ship

Engagements

Payments

Figure 1.2 Using two hierarchical databases to resolve a many-to-many
relationship

The hierarchical database lent itself well to the tape storage systems
used by mainframes in the 1970s and was very popular in companies
that used those systems. But, despite the fact that the hierarchical
database provided fast and direct access to data and was useful in a
number of circumstances, it was clear that a new database model was
needed to address the growing problems of data redundancy and complex relationships among data.

The Network Database Model
The network database was, for the most part, developed as an attempt
to address some of the problems of the hierarchical database. The
structure of a network database is represented in terms of nodes and set
structures. Figure 1.3 shows a diagram of a typical network database.
A node represents a collection of records, and a set structure establishes and represents a relationship in a network database. It is a

Chapter 1 The Relational Database

Agents

Represent

Manage

Clients

Make

Payments

Entertainers

Schedule

Perform

Engagements

Play

Musical Styles

Figure 1.3 Diagram of a typical network database

transparent construction that relates a pair of nodes together by using
one node as an owner and the other node as a member. (This is a valuable improvement on the parent/child relationship.) A set structure
supports a one-to-many relationship, which means that a record in the
owner node can be related to one or more records in the member node,
but a single record in the member node is related to only one record
in the owner node. Additionally, a record in the member node cannot
exist without being related to an existing record in the owner node. For
example, a client must be assigned to an agent, but an agent with no
clients can still be listed in the database. Figure 1.4 shows a diagram
of a basic set structure.
One or more sets (connections) can be defined between a specific
pair of nodes, and a single node can also be involved in other sets
with other nodes in the database. In Figure 1.3, for instance, the
CLIENTS node is related to the PAYMENTS node via the Make set
structure. It is also related to the ENGAGEMENTS node via the Schedule set structure. Along with being related to the CLIENTS node, the

Early Database Models

Agents

Owner Node

Represent

Set Structure

Clients

Member Node

Figure 1.4 A basic set structure

ENGAGEMENTS node is related to the ENTERTAINERS node via the
Perform set structure.
A user can access data within a network database by working through
the appropriate set structures. Unlike the hierarchical database, where
access must begin from a root table, a user can access data from
within the network database, starting from any node and working
backward or forward through related sets. Consider the Agents database in Figure 1.3 once again. Say a user wants to find the agent who
booked a specific engagement. She begins by locating the appropriate
engagement record in the ENGAGEMENTS node, and then determines
which client “owns” that engagement record via the Schedule set
structure. Finally, she identifies the agent that “owns” the client record
via the Represent set structure. The user can answer a wide variety of
questions as long as she navigates properly through the appropriate
set structures.
One advantage the network database provides is fast data access. It
also allows users to create queries that are more complex than those
they created using a hierarchical database. A network database’s main
disadvantage is that a user has to be very familiar with the structure

Chapter 1 The Relational Database

of the database in order to work through the set structures. Consider
the Agents database in Figure 1.3 once again. It is incumbent on the
user to be familiar with the appropriate set structures if she is to
determine whether a particular engagement has been paid. Another
disadvantage is that it is not easy to change the database structure
without affecting the application programs that interact with it. Recall
that a relationship is explicitly defined as a set structure in a network
database. You cannot change a set structure without affecting the
application programs that use this structure to navigate through the
data. If you change a set structure, you must also modify all references
made from within the application program to that structure.
Although the network database was clearly a step up from the hierarchical database, a few people in the database community believed that
there must be a better way to manage and maintain large amounts of
data. As each data model emerged, users found that they could ask
more complex questions, thereby increasing the demands made upon
the database. And so we come to the relational database model.

The Relational Database Model
The relational database was first conceived in 1969 and is still one
of the most widely used database models in database management
today. The father of the relational model, Dr. Edgar F. Codd, was an
IBM research scientist in the late 1960s and was at that time looking
into new ways to handle large amounts of data. His dissatisfaction
with the database models and database products of the time led him
to begin thinking of ways to apply the disciplines and structures of
mathematics to solve the myriad problems he had been encountering.
Being a mathematician by profession, he strongly believed that he
could apply specific branches of mathematics to solve problems such
as data redundancy, weak data integrity, and a database structure’s
overdependence on its physical implementation.

The Relational Database Model

Dr. Codd formally presented his new relational model in a landmark
work entitled “A Relational Model of Data for Large Shared Databanks”1 in June 1970. He based his new model on two branches of
mathematics—set theory and first-order predicate logic. Indeed, the
name of the model itself is derived from the term relation, which is part
of set theory. (A widely held misconception is that the relational model
derives its name from the fact that tables within a relational database
can be related to one another.)
A relational database stores data in relations, which the user perceives
as tables. Each relation is composed of tuples, or records, and attributes, or fields. (I’ll use the terms tables, records, and fields throughout the remainder of the book.) The physical order of the records or
fields in a table is completely immaterial, and each record in the table
is identified by a field that contains a unique value. These are the two
characteristics of a relational database that allow the data to exist
independent of the way it is physically stored in the computer. As such,
a user isn’t required to know the physical location of a record in order
to retrieve its data. This is unlike the hierarchical and network database models in which knowing the layout of the structures is crucial to
retrieving data.
The relational model categorizes relationships as one-to-one, one-tomany, and many-to-many. (These relationships are covered in detail
in Chapter 10, “Table Relationships.”) A relationship between a pair of
tables is established implicitly through matching values of a shared
field. In Figure 1.5, for example, the CLIENTS and AGENTS tables are
related via an Agent ID field; a specific client is associated with an
agent through a matching Agent ID. Likewise, the ENTERTAINERS
and ENGAGEMENTS tables are related via an Entertainer ID; a record
in the ENTERTAINERS table can be associated with a record in the
ENGAGEMENTS table through matching Entertainer IDs.
1. Edgar F. Codd, “A Relational Model of Data for Large Shared Databanks,” Communications of the ACM, June 1970, 377–87.

Chapter 1 The Relational Database

Agents
Agent ID

Agent First Name

Agent Last Name

Date of Hire

Agent Home Phone

100

Mike

Hernandez

05/16/11

553-3992

101

Greg

Johnson

10/15/11

790-3992

102

Katherine

Ehrlich

03/01/12

551-4993

Clients
Client ID

Agent ID

Client First Name

Client Last Name

9001

Client Home Phone

......

100

Stewart

Jameson

553-3992

......

9002

101

Susan

Black

790-3992

......

9003

102

Estela

Rosales

551-4993

......

Entertainers
Entertainer ID

Agent ID

Entertainer First Name

Entertainer Last Name

......

3000

100

John

Slade

......

3001

101

Mark

Jebavy

......

3002

102

Teresa

Weiss

......

Engagements
Client ID

Entertainer ID

Engagement Date

Start Time

Stop Time

9003

3001

04/01/12

1:00 PM

3:30 PM

9009

3000

04/13/12

9:00 PM

1:30 AM

9001

3002

05/02/12

3:00 PM

6:00 PM

Figure 1.5 Examples of related tables in a relational database

As long as a user is familiar with the relationships among the tables
in the database, he can access data in an almost unlimited number
of ways. He can access data from tables that are directly related and
from tables that are indirectly related. Consider the Agents database

The Relational Database Model

in Figure 1.5. Although the CLIENTS table is indirectly related to
the ENGAGEMENTS table, the user can produce a list of clients and
the entertainers who have performed for them. (Of course, it really
depends on how the tables are actually structured, but I digress. This
example serves our purpose for now.) He can do this easily because
CLIENTS is directly related to ENGAGEMENTS and ENGAGEMENTS is
directly related to ENTERTAINERS.

Retrieving Data
You retrieve data in a relational database by using Structured Query
Language (SQL). SQL is the standard language used to create, modify, maintain, and query relational databases. The following shows a
sample SQL query statement you can use to produce a list of all clients
in the city of El Paso:
SELECT ClientLastName, ClientFirstName, ClientPhoneNumber
FROM Clients
WHERE City = "El Paso"
ORDER BY ClientLastName, ClientFirstName
The three components of a basic SQL query are the SELECT…FROM
statement, the WHERE clause, and the ORDER BY clause. You use the
SELECT clause to indicate the fields you want to use in the query and
the FROM clause to indicate the table(s) to which the fields belong. You
can filter the records the query returns by imposing criteria against
one or more fields with the WHERE clause, and then sort the results in
ascending or descending order with the ORDER BY clause.
Most of today’s major relational database software programs incorporate various forms of SQL implementations, ranging from windows
in which users can manually enter “raw” SQL statements to tools
that allow users to build queries using various graphic elements. For
example, a user working with R:BASE Technologies’ R:BASE can opt
to build and execute SQL query statements directly from a command
prompt, while someone using Microsoft SQL Server may find it easier

Chapter 1 The Relational Database

to build queries using SQL Server’s graphical query builder. Regardless
of how the queries are built, the user can save them for future use.
It’s not always necessary for you to know SQL in order to work with a
database. If your database software provides a graphical query builder
or you’re using a custom-built application to work with the data in
your database, you’ll never need to write a single SQL statement. It’s
a good idea, however, for you to gain a basic understanding of SQL. It
will help those of you using query-building tools to understand and
troubleshoot the queries you create with these tools, and it will definitely be to your advantage should you need to work with high-end
database software programs, such as Oracle and Microsoft SQL Server.

❖ Note Although a detailed discussion of SQL is beyond the
scope of this book, you should understand that SQL is a language directly related to the relational database model. If you
have a desire or need to study SQL, you could start by reading
my second book, SQL Queries for Mere Mortals®, Second Edition,
and then move on to any of the other SQL books that I have
listed in Appendix H, “Recommended Reading.”

Advantages of a Relational Database
The relational database provides a number of advantages over previous
models, such as the following.
• Built-in multilevel integrity: Data integrity is built into the model
at the field level to ensure the accuracy of the data; at the table
level to ensure that records are not duplicated and to detect
missing primary key values; at the relationship level to ensure
that the relationship between a pair of tables is valid; and at the
business level to ensure that the data is accurate in terms of

The Relational Database Model

the business itself. (Integrity is discussed in detail as the design
process unfolds.)
• Logical and physical data independence from database applications: Neither changes a user makes to the logical design of the
database, nor changes a database software vendor makes to the
physical implementation of the database, will adversely affect the
applications built upon it.
• Guaranteed data consistency and accuracy: Data is consistent and accurate due to the various levels of integrity you can
impose within the database. (This will become quite clear as you
work through the design process.)
• Easy data retrieval: At the user’s command, data can be retrieved
either from a particular table or from any number of related
tables within the database. This enables a user to view information in an almost unlimited number of ways.
These and other advantages have proved beneficial to the business
community and to all those who need to collect and manage data.
Indeed, the relational database has become the database of choice in
many circumstances.
One commonly perceived disadvantage of the relational database
was that software programs based on it ran very slowly. This was not
a fault of the relational model itself, but of the ancillary technology
available at the time of the model’s introduction. Processing speed,
memory, and storage were simply insufficient to provide database software vendors with a platform on which to build a full implementation
of the relational database, so the initial relational database software
programs fell woefully short of their full potential. Advances in both
hardware technology and software engineering over the past 20 years
have made processing speed an insignificant issue and have allowed
vendors to make significant gains in their efforts to support the model
more fully.

Chapter 1 The Relational Database

You’ll learn more about the relational database model as you work
through the design process presented in this book. Some of the topics
you’ll encounter include creating tables, establishing data integrity,
working with relationships, and establishing business rules.

Relational Database Management
Systems
A relational database management system (RDBMS) is a software application program you use to create, maintain, modify, and manipulate a
relational database. Many RDBMS programs also provide the tools you
need to create end-user applications that interact with the data stored
in the database. Of course, the quality of an RDBMS is a direct function of the extent to which it supports the relational database model.
Even among “true” RDBMSs, support for the relational database varies
among vendors, and there is yet to be a full implementation of the relational model’s potential. Despite this, all RDBMS programs continue to
evolve and become more full-featured and powerful than ever before.
In the earliest days of the relational database, RDBMSs were written for use on mainframe computers. (Didn’t everything start on a
mainframe?) Two RDBMS programs prevalent in the early 1970s were
System R, developed by IBM at its San Jose Research Laboratory in
California, and Interactive Graphics Retrieval System (INGRES), developed at the University of California at Berkeley. These two programs
contributed greatly to the general appreciation of the relational model.
As the benefits of the relational database became more widely known,
many companies decided to make a slow move from hierarchical and
network database models to the relational database model, thus creating a need for more and better mainframe RDBMS programs. The
1980s saw the development of various commercial RDBMSs for mainframe computers by companies such as Oracle and IBM.

Beyond the Relational Model

The early to mid-1980s saw the rise of the personal computer, and
with it the development of PC-based RDBMS programs. Some of the
early entries in this category, from companies such as Ashton-Tate and
Fox Software, were nothing more than elementary file-based database
management systems. True PC-based RDBMS programs began to
emerge with products developed by companies such as Microrim and
Ansa Software. These companies helped to spread the idea and potential of database management from the mainframe-dominated domain
of information systems departments to the desktop of the common end
user.
The need to share data became apparent as more and more users
worked with databases throughout the late 1980s and early 1990s.
The concept of a centrally located database that could be made available to multiple users seemed a very promising idea. This would certainly make data management and database security much easier to
implement. Database vendors such as Microsoft and Oracle responded
to this need by developing client/server RDBMS programs.
In a client/server environment, the data resides on a computer acting
as a database server, and users interact with the data through applications residing on their own computers, or database client. The database developer uses the client/server RDBMS program to create and
maintain the database and attendant end-user application programs.
She implements data integrity and data security on the database
server, giving her the ability to base a variety of user applications on
the same set of data without affecting the data’s integrity or security.

Beyond the Relational Model
Although RDBMSs have been widely accepted for use in typical business applications such as inventory control, patient management,
banking, order processing, and event scheduling, they proved to be

Chapter 1 The Relational Database

somewhat lacking for such applications as computer-aided design
(CAD), geographic information systems (GIS), and multimedia storage
systems. Two new database models eventually emerged in response
to this problem: the object-oriented database and the object-relational
database.
The object-oriented model incorporates all of the characteristics of an
object-oriented programming language and essentially relegates the
relational database to the status of a data store. The fundamental
idea here is that the database developer handles every aspect of the
database, including the sets of operations that manipulate the data in
the database from within the object-oriented database programming
software. No longer is there a clear separation between the database
software and the application programming software. (As with any
other model, there are pros and cons to this approach.) Versant Corporation and IBM are two vendors that produce object-oriented database
software.
Unlike the relational model, which has a solid theoretical basis in two
distinct branches of mathematics, the object-oriented database model
has no specific theoretical foundation. As such, there is no singular,
cohesive consensus as to its definition. There is, however, a version
of the model defined by the Object Management Group (OMG) that is
somewhat of a de facto standard for object-oriented database management systems.

❖ Note The OMG is a nonprofit international group that
addresses the issues of object standards. It was founded in
1989 and comprises more than 800 member organizations. It is
important to note that the OMG is not a standards body, such as
the American National Standards Institute (ANSI), but merely an
advisory and certification group.

What the Future Holds

The object-relational model (formerly known as the extended relational
data model), on the other hand, extended the relational database model
by incorporating various object-oriented elements and characteristics,
such as classes, encapsulation, and inheritance. The idea was that
these extensions would allow a relational database to manage and
manipulate more complex types of data, such as audio streams, video
clips, and architectural drawings. Vendors that have produced application programs based on this model include companies such as IBM,
Oracle, Microsoft, and the PostgreSQL Global Development Group.

What the Future Holds
The manner in which databases are used has evolved immensely in
the past several years. There came a time when many organizations
began to realize that there was a lot of useful information that could
be gathered from data they stored in various relational and nonrelational databases. This prompted them to question whether there was a
way to mine the data for useful analytical information that they could
then use to make critical business decisions. Furthermore, they wondered if they could consolidate and integrate their data into a viable
knowledgebase for their organizations. Indeed, these would be difficult
questions to answer.
IBM proposed the idea of a data warehouse, which, as originally
conceived, would allow organizations to access data stored in any
number of nonrelational databases. They were unsuccessful in their
first attempts at implementing data warehouses, primarily because
of the complexities and performance problems associated with such a
task. It has been only since the 1990s that the implementation of data
warehouses has become more viable and practical. Bill Inmon, widely
regarded as the father of the data warehouse, is a strong and vocal
advocate of the technology and has been instrumental in its evolution.
Data warehouses are now more commonplace as companies move to

Chapter 1 The Relational Database

leverage the vast amounts of data they’ve stored in their databases
over the years.
The Internet has had a significant impact on the way organizations use
databases. Many companies and businesses use the Web to expand
their consumer base, and much of the data they share with and gather
from these consumers is stored in a database. Developers commonly
use eXtensible Markup Language (XML) to assemble and consolidate
data from various relational and nonrelational systems. There has
been a considerable effort by various vendors to get their clients to
create databases and store data in the “cloud,” that is, a location that
is completely apart from the client’s location. The idea is that the client
can access data from the “cloud” database via the Internet from anywhere at any time. Given the broad emergence and use of connected
devices within the past few years (as of this writing), it will be interesting to see how database management systems evolve within this type
of environment.

A Final Note
RDBMSs now have a long history, and they continue to play a huge
role in the way people, businesses, and organizations interact with
their data. Their role is constantly expanding and evolving as data
becomes more accessible via the Internet and businesses move at an
ever-increasing pace to expand their presence on the Web. Numerous
organizations are heavily invested in their relational database systems,
and they are not likely to disappear anytime soon.

Summary
We opened this chapter by defining the two types of databases currently used in database management: operational databases and analytical databases.

Summary

We then briefly discussed the hierarchical database model and the
network database model. Our discussion covered the data structures,
relationships, and data access methods used in both models, as well as
their chief disadvantages. You learned that these models were widely
used in the early days of database management and led to the eventual
development and introduction of the relational database model.
Next, we provided a detailed discussion of the relational database
model, its history, and its features. We noted that it is based on specific
branches of mathematics and that this mathematical foundation is what
makes the model so structurally sound. Then we explored the model’s
data structures and relationships, and the role SQL plays in accessing
data within the model. You’ll remember, no doubt, that SQL is the standard language used to work with relational databases. We ended this
section by reviewing the advantages of the relational database model.
We then took a look at a brief history of relational database management systems, beginning with the mainframe systems of the early
1970s and progressing through the PC-based systems of the 1980s to
the client/server systems of the 1990s. At this point you should have a
sense of the progression of circumstances that have led to the development of the database systems we use today.
The chapter continued with a brief discussion of the object-relational
and object-oriented database models. Here you learned that these
models emerged ostensibly as a means to deal with advanced database
applications, and that they each incorporate various object-oriented
elements and characteristics.
Finally, we closed the chapter with a brief discussion of data warehouses and accessing data via the Internet. You learned that data
warehouses are used to consolidate and integrate data from heterogeneous sources and that the possibility of truly using them has
only recently become more viable and practical. Next, you learned
that XML is a common tool for assembling data across relational and

Chapter 1 The Relational Database

nonrelational data sources and that there is an ever-growing movement to store and manage data “in the cloud.” You should now understand that relational databases are likely to be used for quite some
time, despite the great impact the Internet has had on the way organizations use databases.
In the next chapter, we’ll discuss why you should be concerned with
database design and why theory is important. We’ll also cover the
objectives and advantages of good design.

Review Questions
1. Name the two main types of databases in use today.
2. What type of data does an analytical database store?
3. True or False: An operational database is used primarily in online
transaction processing (OLTP) scenarios.
4. What two data models were commonly used in the days before the
relational database model?
5. Describe a parent/child relationship.
6. What is a set structure?
7. Name one of the branches of mathematics on which the relational
model is based.
8. How does a relational database store data?
9. Name the three types of relationships in a relational database.
10. How do you retrieve data in a relational database?
11. State two advantages of a relational database.
12. What is a relational database management system?
13. What is the premise behind the object-relational model?
14. What is the purpose of a data warehouse?

2
Design Objectives
Everything factual is, in a sense, theory.
The blue of the sky exhibits the basic laws of chromatics.
There is no sense in looking for something behind phenomena;
they are theory.
—GOETHE

Topics Covered in This Chapter
Why Should You Be Concerned with Database Design?
The Importance of Theory
The Advantage of Learning a Good Design Methodology
Objectives of Good Design
Benefits of Good Design
Database Design Methods
Normalization
Summary
Review Questions

Why Should You Be Concerned with
Database Design?
Some of you who work with relational database management system (RDBMS) application programs may wonder why you should be
concerned with database design. After all, most programs come with
sample databases that you can copy and modify to suit your own

Chapter 2 Design Objectives

needs, and you can even borrow tables from the sample databases and
use them in other databases that you’ve created. Some programs also
provide tools that will guide you through the process of defining and
creating tables. However, these tools don’t actually help you design a
database—they merely help you create the physical tables that you will
include in the database.
What you must understand is that it’s better for you to use these tools
after you’ve created the logical database structure. RDBMS programs
provide the design tools and the sample databases to help minimize
the time it takes you to implement the database structure physically.
Theoretically, reducing implementation time gives you more time to
focus on creating and building end-user applications.
Yet the primary reason you should be concerned with database design
is that it is crucial to the consistency, integrity, and accuracy of the
data in a database. If you design a database improperly, it will be difficult for you to retrieve certain types of information, and you’ll run the
risk that your searches will produce inaccurate information. Inaccurate
information is probably the most detrimental result of improper database
design—it can adversely affect your organization’s bottom line. In fact, if
your database affects the manner in which your business performs its
daily operations or if it’s going to influence the future direction of your
business, you must be concerned with database design.
Let’s look at this from a different perspective for a moment: Think
about how you would go about having a custom home built. What’s the
first thing you’re going to do? Certainly you’re not going to hire a contractor immediately and let him build your home however he wishes.
Surely you will first engage an architect to design your new home and
then hire a contractor to build it. The architect will explore your needs
and express them as a set of blueprints, recording decisions about
size and shape and requirements for various systems (structural,
mechanical, electrical). Next, the contractor will procure the labor

The Importance of Theory

and materials, including the listed systems, and then assemble them
according to the drawings and specifications.
Now let’s return to our database perspective and think of the logical database design as the architectural blueprints and the physical
database implementation as the completed home. The logical database design describes the size, shape, and necessary systems for your
database and it addresses the informational and operational needs of
your business. You then build the physical implementation of the logical database design using your RDBMS program. Once you’ve created
your tables, set up table relationships, and established the appropriate
levels of data integrity, your database is complete. Now you’re ready to
design and create applications that allow you and your users to interact easily with the data stored in the database, and you can be confident that these applications will provide you with timely and, above
all, accurate information.
Although you can implement a poor design in an RDBMS, implementing a good design is far more to your advantage because it will yield
accurate information, store data more efficiently and effectively, and be
easier for you to manage and maintain.

The Importance of Theory
❖ Note In this chapter, I use the term theory to represent “general propositions used as principles” and not “conjectures or
proposals.”

A number of major disciplines (and their associated design methodologies) have some type of theoretical basis. Structural engineers design
an unlimited variety of structures using the theories of physics. Composers create beautiful symphonies and orchestral pieces using the

Chapter 2 Design Objectives

concepts found in music theory. The automobile industry uses aerodynamics theories to design more fuel-efficient vehicles. The aerospace
industry uses the same theories to design airplane wings that reduce
wind drag.
These examples demonstrate that theory is relevant and very important. The chief advantage of theory is that it helps you predict outcomes; it allows you to predict what will happen if you perform a
certain action or series of actions. You know if you drop a stone, it will
fall to the ground. If you are agile, you can get your toes out of the way
of Newton’s theory of gravity. The point is that it works every time. If
you chisel a stone flat and place it on another flat stone, you can predict that it will stay where you put it. This theory allows you to design
pyramids and cathedrals and brick outhouses. Now consider a database example. Let’s assume you have a pair of tables that are related to
each other. You know that you can draw data from both tables simultaneously simply because of the way relational database theory works.
The data you draw from both tables is based on matching values of a
shared field between the tables themselves. Again, your actions have a
predictable result.
The relational database is based on two branches of mathematics
known as set theory and first-order predicate logic. This very fact is
what allows the relational database to guarantee accurate information.
These branches of mathematics also provide the basis for formulating
good design methodologies and the building blocks necessary to create
good relational database structures.
You might harbor an understandable reluctance to study complicated
mathematical concepts simply to carry out what seems to be a rather
limited task. You’re still sure to hear claims that the mathematical
theories on which the relational database and its associated design
methodologies are based don’t have any relevance to the real world, or
that they are somehow impractical. This is not true: Math is central

The Advantage of Learning a Good Design Methodology

to the relational model and is what guarantees the model’s viability.
But cheer up—it isn’t really necessary for you to know anything about
set theory or first-order predicate logic in order to use a relational
database! You certainly don’t have to know all the details of aerodynamics just to drive an automobile. Aerodynamics theories may help
you understand and appreciate how an automobile can get better gas
mileage, but they won’t help you learn how to parallel park.
Mathematical theory provides the foundation for the relational database model, and thus makes the model predictable, reliable, and
sound. Theory describes the basic building blocks used to create
a relational database and provides guidelines for how it should be
arranged. Arranging building blocks to achieve a desired result is
defined as “design.”

The Advantage of Learning a Good
Design Methodology
You could learn how to design a database properly by trial and error,
but it would take you a very long time and you would probably have to
repair many mistakes along the way. The best approach is to learn a
good database design methodology, such as the one in this book, and
then embark on designing your database.
You’ll gain several advantages from learning and using a good design
methodology.
• It gives you the skills you need to design a sound database
structure. A large number of data processing problems can be
attributed to the presence of redundant data, duplicate data, and
invalid data, or the absence of required data. All of these problems produce erroneous information and make certain queries
and reports difficult to run. You can avoid almost all of these
problems by employing a good design methodology.

Chapter 2 Design Objectives

• It provides you with an organized set of techniques that will guide
you step-by-step through the design process. The organization of
the techniques enables you to make informed decisions on every
aspect of your design.
• It helps you keep your missteps and design reiterations to a minimum. Of course, you will naturally make some mistakes when
you’re designing a database, but a good methodology helps you
recognize errors in your design and gives you the tools to correct
them. Additionally, the organization of the techniques within the
methodology keeps you from unnecessarily repeating a given
design process.
• It makes the design process easier and reduces the amount of time
you spend designing the database. You will inevitably waste valuable time taking an arbitrary trial-and-error approach to design
because it lacks the logic and organization that a good methodology provides.
• It will help you understand and use your RDBMS application
program more fully and effectively. As your knowledge of proper
design expands and grows, you’ll actually begin to understand
why a given RDBMS provides certain tools and how you can use
them to implement the structure within the RDBMS program.
Regardless of whether you use the design methodology presented in
this book or some other established methodology, you should choose a
design methodology, learn it as well as you can, and use it faithfully to
design your databases.

Objectives of Good Design
There are distinct objectives you must achieve in order to design a
good, sound database structure. You can avoid many of the problems

Benefits of Good Design

mentioned in the previous section if you keep these objectives in mind
and constantly focus on them while you’re designing your database.
• The database supports both required and ad hoc information
retrieval. The database must store the data necessary to support
information requirements defined during the design process and
any possible ad hoc queries that may be posed by a user.
• The tables are constructed properly and efficiently. Each table in
the database represents a single subject, is composed of relatively
distinct fields, keeps redundant data to an absolute minimum,
and is identified throughout the database by a field with unique
values.
• Data integrity is imposed at the field, table, and relationship levels.
These levels of integrity help guarantee that the data structures
and their values will be valid and accurate at all times.
• The database supports business rules relevant to the organization.
The data must provide valid and accurate information that is
always meaningful to the business.
• The database lends itself to future growth. The database structure
should be easy to modify or expand as the information requirements of the business change and grow.
You might find it difficult at times to fulfill these objectives, but you’ll
certainly be pleased with your final database structure once you’ve
met them.

Benefits of Good Design
The time you invest in designing a sound database structure is time
well spent. Good design saves you time in the long run because you
do not constantly have to revamp a quickly and poorly designed

Chapter 2 Design Objectives

structure. You gain the following benefits when you apply good design
techniques.
• The database structure is easy to modify and maintain. Modifications you make to a field or table will not adversely affect other
fields or tables in the database.
• The data is easy to modify. Changes you make to the value of a
given field in a table will not adversely affect the values of other
fields within the table. Furthermore, a well-designed database
keeps duplicate fields to an absolute minimum, so you typically
modify a particular data value in one field only.
• Information is easy to retrieve. You’ll be able to create queries easily because the tables are well constructed and the relationships
between them are properly established.
• End-user applications are easy to develop and build. You can
spend more time on programming and addressing the data
manipulation tasks at hand, instead of working around the inevitable problems that arise when you work with a poorly designed
database.

Database Design Methods
Traditional Design Methods
In general, traditional methods of database design incorporate three
phases: requirements analysis, data modeling, and Normalization.
The requirements analysis phase involves an examination of the business being modeled, interviews with users and management to assess
the current system and to analyze future needs, and an assessment of
information requirements for the business as a whole. This process is
relatively straightforward, and, indeed, the design process presented in
this book follows the same line of thinking.

Database Design Methods

The data modeling phase involves modeling the database structure
using a data modeling method, such as entity-relationship (ER) diagramming, semantic-object modeling, object-role modeling, or UML
modeling. Each of these modeling methods provides a means of visually representing various aspects of the database structure, such as
the tables, table relationships, and relationship characteristics. In fact,
the modeling method used in this book is a basic version of ER diagramming. Figure 2.1 shows an example of a basic ER diagram.

Agents

1:N

Clients

Figure 2.1 An example of a basic ER diagram

❖ Note I’ve incorporated the data modeling method I use in this
book into the design process itself rather than treating it separately. I’ll introduce and explain each modeling technique as
appropriate throughout the process.

Each data modeling method incorporates a set of diagramming symbols used to represent a database’s structure and characteristics. For
example, the diagram in Figure 2.1 provides information on several
aspects of the database.
• The rectangles represent two tables called AGENTS and
CLIENTS.
• The diamond represents a relationship between these two tables,
and the “1:N” within the diamond indicates that it is a one-tomany relationship.
• The vertical line next to the AGENTS table indicates that a client
must be associated with an agent, and the circle next to the

Chapter 2 Design Objectives

CLIENTS table indicates that an agent doesn’t necessarily have
to be associated with a client.
Fields are also defined and associated with the appropriate tables
during the data modeling phase. Each table is assigned a primary key,
various levels of data integrity are identified and implemented, and
relationships are established via foreign keys. Once the initial table
structures are complete and the relationships have been established
according to the data model, the database is ready to go through the
Normalization phase.
Normalization is the process of decomposing large tables into smaller
ones in order to eliminate redundant data and duplicate data, and
to avoid problems with inserting, updating, or deleting data. During
the Normalization process, table structures are tested against normal forms and then modified if any of the aforementioned problems
are found. A normal form is a specific set of rules that can be used to
test a table structure to ensure that it is sound and free of problems.
There are a number of normal forms, and each one is used to test for
a particular set of problems. The normal forms currently in use are
First Normal Form, Second Normal Form, Third Normal Form, Fourth
Normal Form, Fifth Normal Form, Sixth Normal Form, Boyce-Codd
Normal Form, and Domain/Key Normal Form.

The Design Method Presented in This Book
The design method that I use in this book is one that I’ve developed
over the years. It incorporates a requirements analysis and a simple
ER diagramming method to diagram the database structure. However,
it does not incorporate the traditional Normalization process or involve
the use of normal forms. The reason is simple: Normal forms can be
confusing to anyone who has not taken the time to study formal relational database theory. For example, examine the following definition
of Third Normal Form:

Normalization

A relvar is in 3NF if and only if it is in 2NF and every non-key attribute is nontransitively dependent on the primary key.1

This description is relatively meaningless to a reader who is unfamiliar
with the terms relvar, 3NF, 2NF, non-key attribute, transitively dependent, and primary key.
The process of designing a database is not and should not be hard to
understand. As long as the process is presented in a straightforward
manner and each concept or technique is clearly explained, anyone
should be able to design a database properly. For example, the following definition is derived from the results of using Third Normal Form
against a table structure, and I believe most people will find it clear
and easy to understand:
A table should have a field that uniquely identifies each of its records,
and each field in the table should describe the subject that the table
represents.

The process I used to formulate this definition is the same one I used
to develop my entire design methodology.

Normalization
Back in the late 1980s, it occurred to me that the relational model
had been in existence for almost 20 years and that people had been
designing databases using the same basic methodology for about
twelve years. (And I’m still surprised we’re using it some 20+ years
later.) I was using the traditional design methodology at that time, but
I occasionally found it difficult to employ. The two things that bothered
me the most about it were the Normalization process (as a whole) and
the seemingly endless iterations it took to arrive at a proper design. Of
course, these seemed to be sore points with most of the other database
1. C. J. Date, An Introduction to Database Systems, 7th ed. (Boston: Addison-Wesley,
2000), 362; emphasis added.

Chapter 2 Design Objectives

developers that I knew, so I certainly wasn’t alone in my frustrations. I
thought about these problems for quite some time, and then I came up
with a solution.
I already knew that the purpose of Normalization is to take an improperly or poorly designed table and transform it into a table with a sound
structure. I also understood the process: Take a given table and test it
against the normal forms to determine whether it is properly designed.
If it isn’t designed properly, make the appropriate modifications, retest
it, and repeat the entire process until the table structure is sound. Figure 2.2 shows how I visualized the process at this point.
Non-normalized Tables

Normalization Process

Normalized Tables

Figure 2.2 How I viewed the general Normalization process

I kept these facts in mind and then posed the following questions.
1. If we assume that a thoroughly normalized table is properly and
efficiently designed, couldn’t we identify the specific characteristics of such a table and state these to be the attributes of an
ideal table structure?
2. Couldn’t we then use that ideal table as a model for all tables we
create for the database throughout the design process?

Normalization

The answer to both questions, of course, is yes, so I began in earnest
to develop the basis for my “new” design methodology. I first compiled
distinct sets of guidelines for creating sound structures by identifying
the final characteristics of a well-defined database that successfully
passed the tests of each normal form. I then conducted a few tests,
using the new guidelines to create table structures for a new database
and to correct flaws in the table structures of an existing database.
These tests went very well, so I decided to apply this technique to
the entire traditional design methodology. I formulated guidelines to
address other issues associated with the traditional design method,
such as domains, subtypes, relationships, data integrity, and referential integrity. After I completed the new guidelines, I performed more
tests and found that my methodology worked quite well.
The main advantage of my design methodology is that it removes many
aspects of the traditional design methodology that new database developers find intimidating. For example, Normalization, in the traditional
sense, is now transparent to the developer because it is incorporated
(via the new guidelines) throughout the design process. Another major
advantage is that the methodology is clear and easy to implement. I
believe much of this is due to the fact that I’ve written all the guidelines
in plain English, making them easy for most anyone to understand.
It’s important for you to understand that this design methodology will
yield a fully normalized database structure only if you follow it as faithfully as you would any other design methodology. You cannot shortcut,
circumvent, de-emphasize, or omit any part of this methodology (or
any design methodology, for that matter) and expect to develop a sound
structure. You must go through the process diligently, methodically,
and completely in order to reap the expected rewards.

❖ Note I’ve provided a more detailed explanation of how I incorporated Normalization into my design methodology in Appendix G,
“On Normalization.”

Chapter 2 Design Objectives

There are a few basic terms you’ll have to learn before you delve into
the design process, and we’ll cover them in the next chapter.

Summary
At the beginning of this chapter we looked at the importance of being
concerned with database design. You now understand that database
design is crucial to the integrity and consistency of the data contained
in a database. We have seen that the chief problem resulting from
improper or poor design is inaccurate information. Proper design is of
paramount concern because bad design can adversely affect the information used by an organization.
Next, we entered into a discussion of the importance of theory, as well
as its relevance to the relational database model. You learned that the
model’s foundation in mathematical theory makes it a very sound and
reliable structure.
Following this discussion, we looked at the advantages gained by
learning a design methodology. Among other things, using a good
methodology yields an efficient and reliable database structure,
reduces the time it takes to design a database, and allows you to avoid
the typical problems caused by poor design.
Next, we listed the objectives of good design. Meeting these objectives
is crucial to the success of the database design process because they
help you ensure that the database structure is sound. We then enumerated the advantages of good design, and you learned that the time
you invest in designing a sound database structure is time well spent.
We closed this chapter with a short discussion of traditional database
design methods, an explanation of the premise behind the design
method presented in this book, and Normalization. By now, you understand that traditional design methods are complex and can take some

Review Questions

time to learn and comprehend. On the other hand, the design method
used in this book is presented in a clear and straightforward manner,
is easy to implement, and will yield the same results as the traditional
design methodology.

Review Questions
1. When is the best time to use an RDBMS program’s design tools?
2. True or False: Design is crucial to the consistency, integrity, and
accuracy of data.
3. What is the most detrimental result of improper database design?
4. What fact makes the relational database structurally sound and
able to guarantee accurate information?
5. State two advantages of learning a design methodology.
6. True or False: You will use your RDBMS program more effectively
if you understand database design.
7. State two objectives of good design.
8. What helps to guarantee that data structures and their values are
valid and accurate at all times?
9. State two benefits of applying good design techniques.
10. True or False: You can take shortcuts through some of the design
processes and still arrive at a good, sound design.

This page intentionally left blank

3
Terminology
“When I use a word,” Humpty Dumpty said in rather a scornful tone,
“it means just what I choose it to mean—neither more nor less.”
—LEWIS CARROLL
THROUGH THE L OOKING GLASS

Topics Covered in This Chapter
Why This Terminology Is Important
Value-Related Terms
Structure-Related Terms
Relationship-Related Terms
Integrity-Related Terms
Summary
Review Questions
The terms in this chapter are important for you to understand before
you embark upon learning the design process. Indeed, there are
other terms that you’ll need to learn, and I’ll cover them as you work
through the process. There’s also a glossary in the back of the book
that you can use to refresh your memory on any term you learn here
or in the following chapters.

Why This Terminology Is Important
Relational database design has its own unique set of terms, just as any
other profession, trade, or discipline. Here are three good reasons why
it’s important for you to learn these terms.
41

Chapter 3

Terminology

1. They are used to express and define the special ideas and concepts of the relational database model. Much of the terminology
is derived from the mathematical branches of set theory and
first-order predicate logic, which form the basis of the relational
database model.
2. They are used to express and define the database design process
itself. The design process becomes clearer and much easier to
understand once you know these terms.
3, They are used anywhere a relational database or RDBMS is
discussed. You’ll see these terms in publications such as trade
magazines, software manuals, educational course materials,
commercial database software books, and database-related
web sites.
This chapter covers a majority of the terms that define the ideas and
concepts of the design process, including definitions and somewhat
detailed discussions for each term. (I provide pertinent details or necessary further discussion for a given term at the point where the term
is expressly used within a specific technique in the design process.)
There are several other terms that I introduce and discuss later in the
book because I think you’ll more easily understand them within the
context of the specific idea or concept to which they relate.

❖ Note The glossary contains concise definitions for all of the
terms in this chapter and throughout the book.

There are four categories of terms defined in this chapter: valuerelated, structure-related, relationship-related, and integrity-related.

Value-Related Terms

Value-Related Terms
Data
The values you store in the database are data. Data is static in the
sense that it remains in the same state until you modify it by some
manual or automated process. Figure 3.1 shows some sample data.
George Edleman

92883

05/16/96

95.00

Figure 3.1 An example of basic data

This data is meaningless at this point. For example, there is no easy
way for you to determine what “92883” represents. Is it a zip code? Is it
a part number? Even if you know it represents a customer identification number, is it one that is associated with George Edleman? There’s
just no way of knowing until you process the data.

Information
Information is data that you process in a manner that makes it meaningful and useful to you when you work with it or view it. It is dynamic
in the sense that it constantly changes relative to the data stored in
the database, and also in the sense that you can process it and present it in an unlimited number of ways. You can show information as
the result of a SQL SELECT statement, display it in a form on your
computer screen, or print it as a report. The point to remember is that
you must process your data in some manner so that you can turn it into
meaningful information.
Figure 3.2 demonstrates how you might process and transform the
data from the previous example into meaningful information. It has
been manipulated in such a way—in this case as part of a patient
invoice report— that it is now meaningful to anyone who views it.
It is very important that you understand the difference between
data and information. You design a database to provide meaningful

Chapter 3

Terminology

Eastside Medical Clinic
7743 Kingman Dr.
Seattle, WA 98032
(206) 555-9982

Doctors Services

Service Code

Fee

Consultation

92883

119.00

EKG

92773

95.00

Physical
Ultrasound

Patient Name:
Patient ID:

George Edelman
10884

Visit Date:
Physician:

02/16/12
Daniel Chavez

Nursing Services

Service Code

R.N. Exam

89327

Supplies

82372

98377

Nurse Instruction

88332

97399

Insurance Report

81368

Fee

Figure 3.2 An example of data transformed into information

information to someone within a business or organization. This information is available only if the appropriate data exists in the database
and the database is structured in such a way as to support that information. If you ever forget the difference between data and information,
just remember this little axiom:
Data is what you store; information is what you retrieve.

When you fully understand this single, simple concept, the logic
behind the database design process will become crystal clear.

❖ Note Unfortunately, data and information are two terms that
are still frequently used interchangeably (and, therefore, erroneously) throughout the database industry. You’ll encounter this
error in numerous trade magazines, commercial database books,
and web sites, and you’ll even see the terms misused by authors
who should know better.

Value-Related Terms

Null
A null represents a missing or unknown value. You must understand
from the outset that a null does not represent a zero or a text string of
one or more blank spaces. The reasons are quite simple.
• A zero can have a very wide variety of meanings. It can represent
the state of an account balance, the current number of available
first-class ticket upgrades, or the current stock level of a particular product.
• Although a text string of one or more blank spaces is guaranteed to be meaningless to most of us, it is definitely meaningful
to a query language like SQL. A blank space is a valid character
as far as SQL is concerned, and a character string composed of
three blank spaces ('
') is just as legitimate as a character
string composed of three letters ('abc'). In Figure 3.3, a blank
represents the fact that Washington, D.C., is not located in any
county whatsoever.
• A zero-length string—two consecutive single quotes with no space
in between ('')—is also an acceptable value to languages such as
SQL, and can be meaningful under certain circumstances. In an
EMPLOYEES table, for example, a zero-length string value in a

Clients
Client ID

Client First Name

Client Last Name

Client City

Client County

State

<< other fields >>

......

Washington

......

Portland

......

9001

Stewart

Jameson

Seattle

King

9002

Susan

Black

Poulsbo

9003

Estela

Rosales

Fremont

Alameda

9004

Timothy

Ennis

Bellevue

King

9005

Marvin

Russo

9006

Kira

Bently

Figure 3.3 An example of a table containing null values

Chapter 3

Terminology

field called MIDDLE INITIAL may represent the fact that a particular
employee does not have a middle initial in his name.

❖ Note Due to space restrictions, I cannot always show all of
the fields for a given sample table. I will, however, show the fields
that are most relevant to the discussion at hand and use <> to represent fields that are unessential to the example.
You’ll see this convention in many examples throughout the
remainder of the book.

The Value of Nulls
A null is quite useful when you use it for its stated purpose, and the
CLIENTS table in Figure 3.3 clearly illustrates this. Each null in the
CLIENT COUNTY field represents a missing or unknown county name for
the record in which it appears. In order for you to use nulls correctly,
you must first understand why they occur at all.
Missing values are commonly the result of human error. For example,
consider the record for Shannon Black. If you’re entering the data for
Ms. Black and you fail to ask her for the name of the county she lives
in, that data is considered missing and is represented in the record
as a null. Once you recognize the error, however, you can correct it by
calling Ms. Black and asking her for the county name.
Unknown values appear in a table for a variety of reasons. One reason
may be that a specific value you need for a field is as yet undefined.
For instance, you could have a CATEGORIES table in a School Scheduling database that doesn’t currently contain a category for a new set
of classes that you want to offer beginning in the fall session. Another
reason a table might contain unknown values is that they are truly
unknown. Refer to the CLIENTS table in Figure 3.3 once again and
consider the record for Marvin Russo. Say that you’re entering the data

Value-Related Terms

for Mr. Russo and you ask him for the name of the county he lives in.
If he doesn’t know the county name and you don’t happen to know the
county that includes the city in which he lives, then the value for the
county field in his record is truly unknown and is represented within
the record as a null. Obviously, you can correct the problem once
either of you determines the correct county name.
A field value may also be null if none of its values applies to a particular
record. Assume for a moment that you’re working with an EMPLOYEES
table that contains a SALARY field and an HOURLY R ATE field. The value
for one of these two columns is always going to be null because an
employee cannot be paid both a fixed salary and an hourly rate.
It’s important to note that there is a very slim difference between “does
not apply” and “is not applicable.” In the previous example, the value of
one of the two fields literally does not apply. Now assume you’re working with a PATIENTS table that contains a field called H AIR COLOR and
you’re currently updating a record for an existing male patient. If that
patient recently became bald, then the value for that field is definitely
“not applicable.” Although you could just use a null to represent a
value that is not applicable, I always recommend that you use a true
value such as “N/A” or “Not Applicable.” This will make the information
clearer in the long run.
As you can see, whether you allow nulls in a table depends on the
manner in which you’re using the data. Now that you’ve seen the positive side of using nulls, let’s take a look at the negative implication of
using them.

The Problem with Nulls
The major disadvantage of nulls is that they have an adverse effect on
mathematical operations. An operation involving a null evaluates to
null. This is logically reasonable—if a number is unknown then the

Chapter 3

Terminology

result of the operation is necessarily unknown. Note how a null alters
the outcome of the operation in the following example:
(25 × 3) + 4 = 79
(Null × 3) + 4 = Null
(25 × Null) + 4 = Null
(25 × 3) + Null = Null
The PRODUCTS table in Figure 3.4 helps to illustrate the effects
nulls have on mathematical expressions that incorporate fields from
a table. In this case, the value for the TOTAL VALUE field is derived from
the mathematical expression “[SRP] × [Q TY ON H AND].” As you inspect
the records in this table, note that the value for the TOTAL VALUE field is
missing where the Q TY ON H AND value is null, resulting in a null value
for the TOTAL VALUE field as well. This leads to a serious undetected
error that occurs when all the values in the TOTAL VALUE field are added
together: an inaccurate total. This error is “undetected” because an
RDBMS program will not inherently alert you of the error. The only
way to avoid this problem is to ensure that the values for the Q TY ON
H AND field cannot be null.

Products
Product ID

Product Description

Qty On Hand

Total Value

65.00

1,300.00

Accessories

36.00

1,118.00

SureStop 133-MB Brakes

Components

23.50

376.00

70005

Diablo ATM Mountain Bike

Bikes

70006

UltraVision Helmet Mount Mirrors

74.50

70001

Shur-Lok U-Lock

70002

SpeedRite Cyclecomputer

70003

SteelHead Microshell Helmet

70004

Category
Accessories

SRP
75.00

1,200.00
7.45

Figure 3.4 The nulls in this table will have an effect on mathematical operations
involving the table’s fields.

Structure-Related Terms

Figure 3.5 helps to illustrate the effect nulls have on aggregate functions that incorporate the values of a given field in a table. The result
of an aggregate function, such as COUNT (), will be null if it
is based on a field that contains null values. The table in Figure 3.5
shows the results of a summary query that counts the total number
of occurrences of each category in the PRODUCTS table in Figure 3.4.
The value of the TOTAL OCCURRENCES field is the result of the function
expression COUNT ([CATEGORY]). Notice that the summary query shows
“0” occurrences of an unspecified category, implying that each product
has been assigned a category. This information is clearly inaccurate
because there are two products in the PRODUCTS table that have not
been assigned a category.
Category Summary
Category

Total Occurrences
0

Accessories

Bikes

Components

Figure 3.5 Nulls affect the results of an aggregate function.

The issues of missing values, unknown values, and whether a value
will be used in a mathematical expression or aggregate function are all
taken into consideration in the database design process, and we will
revisit and discuss these issues further in later chapters.

Structure-Related Terms
Table
According to the relational model, data in a relational database is
stored in relations, which are perceived by the user as tables. Each
relation is composed of tuples (records) and attributes (fields). Figure 3.6 shows a typical table structure.

Chapter 3

Terminology

Clients
Client ID

Client First Name

Client Last Name

Client City

<< other fields >>

9001

Stewart

Jameson

Seattle

......

9002

Susan

Black

Poulsbo

......

9003

Estela

Rosales

Tacoma

......

9004

Timothy

Ennis

Seattle

......

9005

Marvin

Russo

Bellingham

......

9006

Kira

Bently

Tacoma

......

Records

Fields

Figure 3.6 A typical table structure

Tables are the chief structures in the database and each table always
represents a single, specific subject. The logical order of records and
fields within a table is of absolutely no importance, and every table
contains at least one field—known as a primary key—that uniquely
identifies each of its records. (In Figure 3.6, for example, CLIENT ID is
the primary key of the CLIENTS table.) In fact, data in a relational
database can exist independently of the way it is physically stored in
the computer because of these last two table characteristics. This is
great news for the user because he or she isn’t required to know the
physical location of a record in order to retrieve its data.
The subject that a given table represents can either be an object or an
event. When the subject is an object, it means that the table represents
something that is tangible, such as a person, place, or thing. Regardless of its type, every object has characteristics that you can store as
data and then process as information in an almost infinite number of
ways. Pilots, products, machines, students, buildings, and equipment
are all examples of objects that a table can represent, and Figure 3.6
illustrates one of the most common examples of this type of table.

Structure-Related Terms

When the subject of a table is an event, it means that the table represents something that occurs at a given point in time having characteristics you wish to record. You can store these characteristics as data
and then process the data as information in exactly the same manner
as a table that represents some specific object. Examples of events you
may need to record include judicial hearings, distributions of funds,
lab test results, and geological surveys. Figure 3.7 shows an example
of a table representing an event that we all have experienced at one
time or another—a doctor’s appointment.
Patient Visit
Patient ID

Visit Date

Visit Time

Physician

Blood Pressure

<< other fields >>

92001

02/14/12