did-you-know? rent-now

Amazon no longer offers textbook rentals. We do!

did-you-know? rent-now

Amazon no longer offers textbook rentals. We do!

We're the #1 textbook rental company. Let us show you why.

9780201752847

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

by
  • ISBN13:

    9780201752847

  • ISBN10:

    0201752840

  • Edition: 2nd
  • Format: Paperback
  • Copyright: 2003-03-03
  • Publisher: Addison-Wesley Professional
  • View Upgraded Edition
  • Purchase Benefits
List Price: $74.99

Summary

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

Author Biography

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

Table of Contents

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

Supplemental Materials

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.

Excerpts

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,

Rewards Program