Cover image for Relational database design clearly explained
Title:
Relational database design clearly explained
Author:
Harrington, Jan L.
Personal Author:
Edition:
Second edition.
Publication Information:
New York : Morgan Kaufmann Publishers, [2002]

©2002
Physical Description:
xvi, 393 pages : illustrations ; 24 cm
General Note:
Includes index.
Language:
English
ISBN:
9781558608207
Format :
Book

Available:*

Library
Call Number
Material Type
Home Location
Status
Central Library QA76.9.D26 H38 2002 Adult Non-Fiction Non-Fiction Area
Searching...

On Order

Summary

Summary

This revised textbook teaches database practitioners the principles of good relational database design, approaching the subject with a balance of theory and practice. It presents an entity-relationship approach and a relational theory approach, explaining how they are related and how they cross check each other to validate the design. The book looks at tradeoffs between theory and practice (that is, when it is acceptable to violate a principle of good design in order to improve performance). It also shows readers how to construct the SQL statements needed to install well-designed relational databases, and discusses other performance-related database design issues, such as indexes and clustering. This second edition includes a chapter on integrating object-relational and object-oriented database design, and adds object-relational design examples to all three in-depth case studies.


Author Notes

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.


Table of Contents

Preface to the Second Editionp. xiii
What You Need to Knowp. xv
Acknowledgmentsp. xv
Part 1 Theory
Chapter 1 Introductionp. 3
Effects of Poor Database Designp. 4
Unnecessary Duplicated Data and Data Consistencyp. 6
Data Insertion Problemsp. 7
Data Deletion Problemsp. 8
Meaningful Identifiersp. 9
What You Will Find in This Bookp. 9
Chapter 2 Entities and Data Relationshipsp. 11
Entities and Their Attributesp. 12
Entity Identifiersp. 13
Single-Valued versus Multivalued Attributesp. 15
Avoiding Collections of Entitiesp. 17
Documenting Logical Data Relationshipsp. 18
Entities and Attributes for Lasers Onlyp. 19
Domainsp. 20
Documenting Domainsp. 21
Practical Domain Choicesp. 21
Basic Data Relationshipsp. 23
One-to-One Relationshipsp. 24
One-to-Many Relationshipsp. 26
Many-to-Many Relationshipsp. 27
Weak Entities and Mandatory Relationshipsp. 27
Documenting Relationshipsp. 28
Basic Relationships for Lasers Onlyp. 32
Dealing with Many-to-Many Relationshipsp. 33
Composite Entitiesp. 34
Documenting Composite Entitiesp. 36
Resolving Lasers Only's Many-to-Many Relationshipsp. 36
Relationships and Business Rulesp. 37
Data Modeling versus Data Flowp. 39
Schemasp. 43
For Further Readingp. 44
Chapter 3 Historical Antecedentsp. 47
File Processing Systemsp. 48
Early File Processingp. 48
ISAM Filesp. 50
Limitations of File Processingp. 52
File Processing on the Desktopp. 53
The Hierarchical Data Modelp. 54
Characteristics of the Hierarchical Data Modelp. 54
IMSp. 57
The Simple Network Data Modelp. 59
Characteristics of a Simple Networkp. 59
CODASYLp. 62
The Complex Network Data Modelp. 65
Chapter 4 The Relational Data Modelp. 73
Understanding Relationsp. 74
Columns and Column Characteristicsp. 75
Rows and Row Characteristicsp. 76
Types of Tablesp. 76
A Notation for Relationsp. 77
Primary Keysp. 77
Primary Keys to Identify Peoplep. 78
Avoiding Meaningful Primary Keysp. 80
Concatenated Primary Keysp. 81
All-Key Relationsp. 82
Representing Data Relationshipsp. 82
Referential Integrityp. 85
Foreign Keys and Primary Keys in the Same Tablep. 86
Viewsp. 86
The View Mechanismp. 86
Why Use Views?p. 87
The Data Dictionaryp. 88
Sample Data Dictionary Tablesp. 89
A Bit of Historyp. 90
For Further Readingp. 92
Chapter 5 Normalizationp. 93
Translating an ER Diagram into Relationsp. 94
Normal Formsp. 95
First Normal Formp. 96
Understanding Repeating Groupsp. 97
Handling Repeating Groupsp. 98
Problems with First Normal Formp. 100
Second Normal Formp. 102
Understanding Functional Dependenciesp. 103
Using Functional Dependencies to Reach 2NFp. 104
Problems with 2NF Relationsp. 105
Third Normal Formp. 106
Transitive Dependenciesp. 106
Boyce-Codd Normal Formp. 108
Fourth Normal Formp. 110
Multivalued Dependenciesp. 111
Normalized Relations and Database Performancep. 112
Equi-Joinsp. 113
What's Really Going On: PRODUCT and RESTRICTp. 116
The Bottom Linep. 119
For Further Readingp. 119
Chapter 6 Database Structure and Performance Tuningp. 121
Indexingp. 122
Deciding Which Indexes to Createp. 124
Clusteringp. 124
Partitioningp. 126
Horizontal Partitioningp. 126
Vertical Partitioningp. 127
For Further Referencep. 128
Chapter 7 Codd's Rulesp. 129
Rule 1 The Information Rulep. 130
Rule 2 The Guaranteed Access Rulep. 132
Rule 3 Systematic Treatment of Null Valuesp. 133
Rule 4 Dynamic Online Catalog Based on the Relational Modelp. 134
Rule 5 The Comprehensive Data Sublanguage Rulep. 135
Rule 6 The View Updating Rulep. 136
Rule 7 High-Level Insert, Update, and Deletep. 137
Rule 8 Physical Data Independencep. 138
Rule 9 Logical Data Independencep. 139
Rule 10 Integrity Independencep. 139
Rule 11 Distribution Independencep. 141
Rule 12 Nonsubversion Rulep. 142
Chapter 8 Integrating Objectsp. 143
An Introduction to Object-Oriented Conceptsp. 144
Writing Instructionsp. 145
Objectsp. 146
Classesp. 151
Class Relationshipsp. 157
Benefits of Object Orientationp. 165
Integrating Objects into a Relational Databasep. 166
ER Diagrams for Object-Relational Designsp. 167
For Further Readingp. 172
Part 2 Practice
Chapter 9 Using SQL to Implement a Relational Designp. 177
Database Object Hierarchyp. 178
Naming and Identifying Objectsp. 179
Schemasp. 181
Creating a Schemap. 181
Identifying the Schema You Want to Usep. 182
Domainsp. 183
Tablesp. 184
Column Data Typesp. 185
Default Valuesp. 190
NOT NULL Constraintsp. 190
Primary Keysp. 191
Foreign Keysp. 191
Additional Column Constraintsp. 194
Viewsp. 198
Deciding Which Views to Createp. 198
View Updatability Issuesp. 199
Creating Viewsp. 199
Temporary Tablesp. 201
Creating Temporary Tablesp. 202
Loading Temporary Tables with Datap. 203
Disposition of Temporary Table Rowsp. 203
Creating Indexesp. 204
Modifying Database Elementsp. 205
Adding New Columnsp. 205
Adding Table Constraintsp. 206
Modifying Columnsp. 206
Deleting Elementsp. 207
Renaming Elementsp. 208
Deleting Database Elementsp. 209
Granting and Revoking Access Rightsp. 209
Types of Access Rightsp. 210
Storing Access Rightsp. 210
Granting Rightsp. 211
Revoking Rightsp. 212
Object-Relational Extensionsp. 213
Chapter 10 Using CASE Tools for Database Designp. 215
CASE Capabilitiesp. 216
ER Diagram Reportsp. 218
Data Flow Diagramsp. 220
The Data Dictionaryp. 222
Code Generationp. 225
Sample Input and Output Designsp. 228
The Drawing Environmentp. 229
For Further Readingp. 230
Chapter 11 Database Design Case Study #1: Mighty-Mite Motorsp. 231
Corporate Overviewp. 232
Product Development Divisionp. 232
Manufacturing Divisionp. 238
Marketing & Sales Divisionp. 239
Current Information Systemsp. 239
Reengineering Projectp. 240
New Information Systems Divisionp. 241
Basic System Goalsp. 241
Current Business Processesp. 242
Designing the Databasep. 251
Examining the Data Flowsp. 253
Creating the ER Diagramp. 256
Creating the Tablesp. 261
Generating the SQLp. 262
The Object-Relational Designp. 268
Creating the Classesp. 273
Using the Classes in the Schemap. 275
Chapter 12 Database Design Case Study #2: East Coast Aquariump. 281
Organizational Overviewp. 282
Animal Tracking Needsp. 284
The Volunteer Organizationp. 288
The Volunteers Databasep. 290
Creating the Application Prototypep. 290
Creating the ER Diagramp. 300
Designing the Tablesp. 300
Generating the SQLp. 301
The Animal Tracking Databasep. 301
Highlights of the Application Prototypep. 303
Creating the ER Diagramp. 308
Creating the Tablesp. 311
Generating the SQLp. 312
The Object-Relational Designp. 316
The Volunteers Databasep. 316
The Animal Tracking Databasep. 317
Chapter 13 Database Design Case Study #3: Independent Intelligence Agencyp. 321
Organizational Overviewp. 322
Current Information Systemsp. 324
Summary of IS Needsp. 327
System Specificationsp. 328
Designing the Databasep. 333
ER Diagram for Peoplep. 334
ER Diagram for Intelligence Gathering and Salesp. 337
ER Diagram for System Securityp. 340
ER Diagram for Equipment Trackingp. 341
Designing the Tablesp. 343
Generating the SQLp. 344
The Object-Relational Designp. 355
Creating the Classesp. 362
Writing the Schemap. 365
Glossaryp. 375
Indexp. 387

Google Preview