9780201752847

The bestselling book on database design is now fully updated and revised!

Michael J. Hernandez is a program manager for the Visual Studio .NET group at Microsoft, and is a veteran relational database developer with more than fourteen years of experience. He has been a premiere instructor with training organizations such as AppDev Training Co., Focal Point, Inc., and Deep Training, and was one of the first two hundred Microsoft-authorized .NET instructors. He speaks regularly at conferences.

0201752840AB02032003

Foreword

xxv

Preface (Second Edition)

xxix

Preface (First Edition)

xxxiii

Introduction

xxxvii

What's New in the Second Edition

xxxix

Who Should Read This Book

xli

The Purpose of This Book

xlii

How to Read This Book

xlv

How This Book Is Organized

xlvi

Part I: Relational Database Design

xlvi

Part II: The Design Process

xlvi

Part III: Other DatabaseDesign Issues

xlviii

Part IV: Appendixes

xlviii

A Word About the Examples and Techniques in This Book

xlix

A New Approach to Learning

1

(2)

PART I: RELATIONAL DATABASE DESIGN

1

(74)

Chapter 1: The Relational Database

3

(24)

Topics Covered in This Chapter

3

(1)

Types of Databases

4

(1)

Early Database Models

5

(7)

The Hierarchical Database Model

5

(4)

The Network Database Model

9

(3)

The Relational Database Model

12

(6)

Retrieving Data

15

(2)

Advantages of a Relational Database

17

(5)

Relational Database Management Systems

18

(3)

Beyond the Relational Model

21

(1)

What the Future Holds

22

(2)

A Final Note

24

(10)

Summary

24

(2)

Review Questions

26

(1)

Chapter 2: Design Objectives

27

(16)

Topics Covered in This Chapter

27

(1)

Why Should You Be Concerned with Database Design?

27

(4)

The Importance of Theory 29

31

(1)

The Advantage of Learning a Good Design Methodology

Objectives of Good Design

32

(1)

Benefits of Good Design

33

(1)

Database-Design Methods

34

(6)

Traditional Design Methods

34

(2)

The Design Method Presented in This Book

36

(9)

Summary

40

(1)

Review Questions

41

(2)

Chapter 3: Terminology

43

(32)

Topics Covered in This Chapter

43

(1)

Why This Terminology Is Important

44

(1)

Value-Related Terms

45

(7)

Data

45

(1)

Information

45

(2)

Null

47

(1)

The Value of Nulls

48

(2)

The Problem with Nulls

50

(2)

Structure-Related Terms

52

(10)

Table

52

(3)

Field

55

(1)

Record

56

(1)

View

57

(2)

Keys

59

(2)

Index

61

(1)

Relationship-Related Terms

62

(8)

Relationships

62

(1)

Types of Relationships

63

(5)

Types of Participation

68

(1)

Degree of Participation

69

(1)

Integrity-Related Terms

70

(2)

Field Specification

70

(1)

Data Integrity

71

(20)

Summary

72

(1)

Review Questions

73

(4)

PART II: THE DESIGN PROCESS

75

(414)

Chapter 4: Conceptual Overview

77

(14)

Topics Covered in This Chapter 77

79

The Importance of Completing the Design Process

78

(2)

Defining a Mission Statement and Mission Objectives

Analyzing the Current Database

80

(2)

Creating the Data Structures

82

(1)

Determining and Establishing Table Relationships

83

(1)

Determining and Defining Business Rules

84

(1)

Determining and Defining Views

85

(1)

Reviewing Data Integrity

85

(1)

Summary

86

(2)

Review Questions

88

(3)

Chapter 5: Starting the Process

91

(28)

Topics Covered in This Chapter

91

(1)

Conducting Interviews

91

(9)

Participant Guidelines

93

(2)

Interviewer Guidelines (These Are for You)

95

(6)

The Case Study: Mike's Bikes

100

(1)

Defining the Mission Statement

101

(7)

The Well-Written Mission Statement

102

(2)

Composing a Mission Statement

104

(2)

Case Study

106

(2)

Defining the Mission Objectives

108

(7)

Well-Written Mission Objectives

108

(2)

Composing Mission Objectives

110

(4)

Case Study

114

(5)

Summary

115

(1)

Review Questions

116

(3)

Chapter 6: Analyzing the Current Database

119

(62)

Topics Covered in This Chapter

119

(1)

Getting to Know the Current Database

119

(62)

Paper-Based Databases 123

129

Legacy Databases

123

(2)

Conducting the Analysis

125

(1)

Looking at How Data Is Collected

125

(8)

Looking at How Information Is Presented

Conducting Interviews

133

(9)

Basic Interview Techniques

135

(6)

Before You Begin the Interview Process

141

(1)

Interviewing Users

142

(15)

Reviewing Data Type and Usage

142

(2)

Reviewing the Samples

144

(4)

Reviewing Information Requirements

148

(9)

Interviewing Management

157

(5)

Reviewing Current Information Requirements

158

(1)

Reviewing Additional Information Requirements

159

(1)

Reviewing Future Information Requirements

160

(1)

Reviewing Overall Information Requirements

161

(1)

Compiling a Complete List of Fields

162

(15)

The Preliminary Field List

162

(8)

The Calculated-Field List

170

(1)

Reviewing Both Lists with Users and Management

171

(1)

Case Study

172

(5)

Summary

177

(2)

Review Questions

179

(3)

Chapter 7: Establishing Table Structures

181

(70)

Topics Covered in This Chapter

181

(1)

Defining the Preliminary Table List

182

(9)

Identifying Implied Subjects

182

(2)

Using the List of Subjects

184

(5)

Using the Mission Objectives

189

(2)

Defining the Final Table List

191

(14)

Refining the Table Names

193

(5)

Indicating the Table Types

198

(1)

Composing the Table Descriptions

199

(9)

Associating Fields with Each Table

205

(3)

Refining the Fields 208

208

(18)

Improving the Field Names

Using an Ideal Field to Resolve Anomalies

213

(13)

Resolving Multipart Fields 216

226

Resolving Multivalued Fields

219

(7)

Refining the Table Structures

226

(22)

A Word About Redundant Data and Duplicate Fields

Using an Ideal Table to Refine Table Structures

227

(8)

Establishing Subset Tables

235

(4)

Case Study

239

(13)

Summary

248

(1)

Review Questions

249

(2)

Chapter 8: Keys

251

(30)

Topics Covered in This Chapter

251

(1)

Why Keys Are Important

252

(1)

Establishing Keys for Each Table

252

(17)

Candidate Keys

253

(8)

Primary Keys

261

(7)

Alternate Keys

268

(1)

Non-keys

268

(2)

Table-Level Integrity

269

(1)

Reviewing the Initial Table Structures

270

(7)

Case Study

271

(14)

Summary

277

(2)

Review Questions

279

(2)

Chapter 9: Field Specifications

281

(40)

Topics Covered in This Chapter

281

(1)

Why Field Specifications Are Important

282

(2)

Field-Level Integrity 283

284

(1)

Anatomy of a Field Specification

General Elements

285

(8)

Physical Elements

293

(7)

Logical Elements

300

(14)

Using Unique, Generic, and Replica Field Specifications

308

(6)

Defining Field Specifications for Each Field in the Database

314

(4)

Case Study

316

(7)

Summary

318

(2)

Review Questions

320

(1)

Chapter 10: Table Relationships

321

(82)

Topics Covered in This Chapter

321

(1)

Why Relationships Are important

322

(1)

Types of Relationships

323

(18)

One-to-One Relationships

324

(3)

One-to-Many Relationships

327

(3)

Many-to-Many Relationships

330

(7)

Self-Referencing Relationships

337

(16)

Identifying Existing Relationships

341

(12)

Establishing Each Relationship

353

(21)

One-to-One and One-to-Many Relationships

353

(8)

The Many-to-Many Relationship

361

(6)

Self-Referencing Relationships

367

(6)

Reviewing the Structure of Each Table

373

(1)

Refining All Foreign Keys

374

(7)

Elements of a Foreign Key

374

(7)

Establishing Relationship Characteristics

381

(13)

Defining a Deletion Rule for Each Relationship

381

(6)

Identifying the Type of Participation for Each Table

387

(3)

Identifying the Degree of Participation for Each Table

390

(3)

Verifying Table Relationships with Users and Management

393

(1)

A Final Note

393

(1)

Relationship-Level Integrity

394

(6)

Case Study

395

(9)

Summary

400

(2)

Review Questions

402

(1)

Chapter 11: Business Rules

403

(44)

Topics Covered in This Chapter

403

(1)

What Are Business Rules?

404

(5)

Types of Business Rules

407

(2)

Categories of Business Rules

409

(3)

Field Specific Business Rules

409

(1)

Relationship Specific Business Rules

410

(2)

Defining and Establishing Business Rules

412

(16)

Working with Users and Management

413

(1)

Defining and Establishing Field Specific Business Rules

413

(8)

Defining and Establishing Relationship Specific Business Rules

421

(7)

Validation Tables

428

(6)

What Are Validation Tables?

430

(1)

Using Validation Tables to Support Business Rules

431

(3)

Reviewing the Business Rule Specifications Sheets

434

(8)

Case Study

437

(12)

Summary

442

(3)

Review Questions

445

(2)

Chapter 12: Views

447

(34)

Topics Covered in This Chapter

447

(1)

What Are Views?

447

(2)

Anatomy of a View

449

(11)

Data View

449

(5)

Aggregate View

454

(4)

Validation View

458

(2)

Determining and Defining Views

460

(17)

Working with Users and Management

461

(1)

Defining Views

462

(8)

Reviewing the Documentation for Each View

470

(2)

Case Study

472

(11)

Summary

477

(1)

Review Questions

478

(3)

Chapter 13: Reviewing Data Integrity

481

(8)

Topics Covered in This Chapter

481

(1)

Why You Should Review Data Integrity

482

(1)

Reviewing and Refining Data Integrity

483

(3)

At the Table Level

483

(1)

At the Field Level

484

(1)

At the Relationship Level

484

(1)

At the Level of Business Rules

484

(1)

At the Level of Views

485

(2)

Assembling the Database Documentation

486

(1)

Done at Last!

487

(1)

Case Study-Wrap Up

487

(6)

Summary

488

(3)

PART III: OTHER DATABASEDESIGN ISSUES

489

(22)

Chapter 14: Bad Design-What Not to Do

491

(10)

Topics Covered in This Chapter

491

(1)

Flat-File Design

492

(1)

Spreadsheet Design

493

(4)

Dealing with the Spreadsheet View Mindset

495

(6)

Database Design Based on the Database Software

497

(1)

A Final Thought

498

(1)

Summary

499

(2)

Chapter 15: Bending or Breaking the Rules

501

(8)

Topics Covered in This Chapter

501

(1)

When May You Bend or Break the Rules?

501

(4)

Designing an Analytical Database

501

(1)

Improving Processing Performance

502

(55)

Documenting Your Actions

505

(2)

Summary

507

(6)

In Closing

509

(2)

PART IV APPENDIXES

511

Appendix A: Answers to Review Questions

513

(24)

Chapter 1

513

(1)

Chapter 2

514

(2)

Chapter 3

516

(1)

Chapter 4

517

(1)

Chapter 5

518

(2)

Chapter 6

520

(2)

Chapter 7

522

(3)

Chapter 8

525

(3)

Chapter 9

528

(2)

Chapter 10

530

(2)

Chapter 11

532

(1)

Chapter 12

533

(22)

Appendix B: Diagram of the Database-Design Process

537

(18)

Appendix C: Design Guidelines

555

(12)

Defining and Establishing Field Specific Business Rules

555

(1)

Defining and Establishing Relationship Specific Business Rules

555

(1)

Elements of a Candidate Key

556

(1)

Elements of a Foreign Key

556

(1)

Elements of a Primary Key

557

(1)

Rules for Establishing a Primary Key

557

(5)

Elements of the Ideal Field

557

(1)

Elements of the Ideal Table

558

(1)

Field-Level Integrity

558

(1)

Guidelines for Composing a Field Description

559

(1)

Guidelines for Composing a Table Description

559

(1)

Guidelines for Creating Field Names

560

(1)

Guidelines for Creating Table Names

560

(1)

Identifying Relationships

561

(1)

Identifying View Requirements

561

(1)

Interview Guidelines

562

(1)

Participant Guidelines

562

(1)

Interviewer Guidelines

562

(1)

Mission Statements

563

(1)

Mission Objectives

563

(1)

Relationship-Level Integrity

564

(1)

Resolving a Multivalued Field

564

(1)

Table-Level Integrity

565

(2)

Appendix D: Documentation Forms

567

(4)

Appendix E: Database-Design Diagram Symbols

571

(2)

Appendix F: Sample Designs

573

(8)

Appendix G: Recommended Reading

581

(2)

Glossary

583

(16)

References

599

(2)

Index

601

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

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 information technology (IT) personnel 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 that they were using. As technology advanced, however, those educational requirements evolved. Since the mid-1980s, many software vendors have developed database software programs that run on desktop computers and can be more easily programmed to collect, store, and manage data than their mainframe counterparts. They have also produced software that allows groups of people to access and share centralized data within a variety of environments, such as client/server architectures on computers connected within local-area networks (LANs) and wide-area networks (WANs), and even via the Internet. People within a company or organization are no longer strictly dependent on mainframe databases or on having their information needs met by centralized IT departments. Over the years, vendors have added new features and enhanced the tool sets in their database software, enabling database developers to create more powerful and flexible database applications. They've also improved the ease with which the software can be used, inspiring 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 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, 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,

Amazon no longer offers textbook rentals. We do!

Database Design for Mere Mortals A Hands-On Guide to Relational Database Design

Database Design for Mere Mortals A Hands-On Guide to Relational Database Design

0201752840

Summary

Author Biography

Table of Contents

Supplemental Materials

Excerpts