Relational Database Design Clearly Explained

Front Cover
Morgan Kaufmann, 2002 - Computers - 393 pages


Fully revised and updated, Relational Database Design, Second Edition is the most lucid and effective introduction to relational database design available. Here, you'll find the conceptual and practical information you need to develop a design that ensures data accuracy and user satisfaction while optimizing performance, regardless of your experience level or choice of DBMS.


Supporting the book's step-by-step instruction are three case studies illustrating the planning, analysis, and design steps involved in arriving at a sound design. These real-world examples include object-relational design techniques, which are addressed in greater detail in a new chapter devoted entirely to this timely subject.

* Concepts you need to master to put the book's practical instruction to work.
* Methods for tailoring your design to the environment in which the database will run and the uses to which it will be put.
* Design approaches that ensure data accuracy and consistency.
* Examples of how design can inhibit or boost database application performance.
* Object-relational design techniques, benefits, and examples.
* Instructions on how to choose and use a normalization technique.
* Guidelines for understanding and applying Codd's rules.
* Tools to implement a relational design using SQL.
* Techniques for using CASE tools for database design.

 

Contents

Documenting Domains
21
Basic Data Relationships
23
OnetoOne Relationships
24
OnetoMany Relationships
26
ManytoMany Relationships
27
Documenting Relationships
28
Basic Relationships for Lasers Only
32
Dealing with ManytoMany Relationships
33
Composite Entities
34
Documenting Composite Entities
36
Relationships and Business Rules
37
Data Modeling versus Data Flow
39
Schemas
43
For Further Reading
44
Historical Antecedents
47
File Processing Systems
48
ISAM Files
50
Limitations of File Processing
52
File Processing on the Desktop
53
The Hierarchical Data Model
54
IMS
57
The Simple Network Data Model
59
CODASYL
62
The Complex Network Data Model
65
The Relational Data Model
73
Understanding Relations
74
Columns and Column Characteristics
75
Rows and Row Characteristics
76
A Notation for Relations
77
Primary Keys to ldentify People
78
Avoiding Meaningful Primary Keys
80
Concatenated Primary Keys
81
AllKey Relations
82
Referential Integrity
85
Foreign Keys and Primary Keys in the Same Table
86
Why Use Views?
87
The Data Dictionary
88
Sample Data Dictionary Tables
89
A Bit of History
90
For Further Reading
92
Normalization
93
Translating an ER Diagram into Relations
94
Normal Forms
95
First Normal Form
96
Understanding Repeating Groups
97
Handling Repeating Groups
98
Problems with First Normal Form
100
Second Normal Form
102
Understanding Functional Dependencies
103
Using Functional Dependencies to Reach 2NF
104
Problems with 2NF Relations
105
Third Normal Form
106
BoyceCodd Normal Form
108
Fourth Normal Form
110
Multivalued Dependencies
111
Normalized Relations and Database Performance
112
EquiJoins
113
PRODUCT and RESTRlCT
116
The Bottom Line
119
Database Structure and Performance Tuning
121
Indexing
122
Deciding Which Indexes to Create
124
Partitioning
126
Vertical Partitioning
127
For Further Reference
128
Codds Rules
129
The Information Rule
130
The Guaranteed Access Rule
132
Systematic Treatment of Null Values
133
Dynamic Online Catalog Based on the Relational Model
134
The Comprehensive Data Sublanguage Rule
135
The View Updating Rule
136
HighLevel Insert Update and Delete
137
Physical Data Independence
138
Logical Data Independence
139
Distribution Independence
141
Nonsubversion Rule
142
Integrating Objects
143
An Introduction to ObjectOriented Concepts
144
Writing Instructions
145
Objects
146
Database Object Hierarchy
178
Naming and Identifying Objects
179
Schemas
181
Identifying the Schema You Want to Use
182
Domains
183
Tables
184
Column Data Types
185
Default Values
190
Primary Keys
191
Additional Column Constraints
194
Views
198
View Updatability lssues
199
Temporary Tables
201
Creating Temporary Tables
202
Loading Temporary Tables with Data
203
Creating indexes
204
Modifying Database Elements
205
Adding Table Constraints
206
Deleting Elements
207
Renaming Elements
208
Deleting Database Elements
209
Types of Access Rights
210
Granting Rights
211
Revoking Rights
212
ObjectRelational Extensions
213
Using CASE Tools for Database Design
215
CASE Capabilities
216
ER Diagram Reports
218
Data Flow Diagrams
220
The Data Dictionary
222
Code Generation
225
Sample Input and Output Designs
228
The Drawing Environment
229
For Further Reading
230
Database Design Case Study 1 MightyMite Motors
231
Corporate Overview
232
Manufacturing Division
238
Marketing Sales Division
239
Reengineering Project
240
New Information Systems Division
241
Current Business Processes
242
Designing the Database
251
Examining the Data Flows
253
Creating the ER Diagram
256
Creating the Tables
261
Generating the SQL
262
The ObjectRelational Design
268
Creating the Classes
273
Using the Classes in the Schema
275
Database Design Case Study 2 East Coast Aquarium
281
Organizational Overview
282
Animal Tracking Needs
284
The Volunteer Organization
288
The Volunteers Database
290
Creating the ER Diagram
300
Generating the SQL
301
Highlights of the Application Prototype
303
Creating the ER Diagram
308
Creating the Tables
311
Generating the SQL
312
The ObjectRelational Design
316
The Animal Tracking Database
317
Database Design Case Study 3 Independent Intelligence Agency
321
Organizational Overview
322
Current Information Systems
324
Summary of IS Needs
327
System Specifications
328
Designing the Database
333
ER Diagram for People
334
ER Diagram for Intelligence Gathering and Sales
337
ER Diagram for System Security
340
ER Diagram for Equipment Tracking
341
Designing the Tables
343
Generating the SQL
344
The ObjectRelational Design
355
Creating the Classes
362
Writing the Schema
365
Glossary
375
Index
387
Copyright

Other editions - View all

Common terms and phrases

Popular passages

Page 134 - A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all of the following items: data definition, view definition, data manipulation (interactive and by program), integrity constraints, and transaction boundaries (begin, commit and rollback).
Page 138 - Rule 10: Integrity Independence Integrity constraints specific to a particular relational database must be definable in the relational data sublanguage and storable in the catalog, not in the application programs.
Page 134 - The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.
Page 136 - Rule 7: High-Level Insert, Update, and Delete The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insertion, update and deletion of data.
Page 138 - Rule 9: Logical data independence Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit unimpairment are made to the base tables.
Page 203 - Indexes provide an efficient way to access the rows in a table based on the values in one or more columns.
Page 137 - Rule 8: Physical Data Independence Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representation or access methods.
Page 129 - Rule 1 : The Information Rule All information in a relational database is represented explicitly at the logical level in exactly one way — by values in tables.
Page 132 - ... 3. Systematic treatment of null values. Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.

About the author (2002)

Jan L. Harrington, the author of 30 books, including SQL, Clearly Explained (Academic Press), has been writing about databases since 1984. She is a professor and chair of the department of computer science and information systems at Marist College, where she teaches database design and management, object-oriented programming, data communications, and computer architecture.