Transcription
RelationalDatabase DesignClearlyExplainedJan L. Harrington4JtAP PROFESSIONALAP PROFESSIONAL is a division of Academic PressSan Diego London BostonNew York Sydney Tokyo Toronto
ContentsPreface xiWhat You Need to KnowAcknowledgments xiiixiiPart One: TheoryChapter 1: Introduction13Effects of Poor Database Design 4Unnecessary Duplicated Data and Data Consistency 6Data Insertion Problems 7Data Deletion Problems 8Meaningful Identifiers 8What You Will Find in This Book 9v
Chapter 2: Entities and Data RelationshipsEntities and Their Attributes 12Entity Identifiers 13Single-Valued Versus Multivalued Attributes 15Avoiding Collections of Entities 16Documenting Entities and Attributes 16Entities and Attributes for Lasers Only 18Domains 19Documenting Domains 19Practical Domain Choices 20Basic Data Relationships 22One-to-One Relationships 23One-to-Many Relationships 24Many-to-Many Relationships 25Weak Entities and Mandatory Relationships 26Documenting Relationships 27Basic Relationships for Lasers Only 30Dealing with Many-to-Many Relationships 32Composite Entities 32Documenting Composite Entities 34Resolving Lasers Only's Many-to-Many RelationshipsRelationships and Business Rules 35Data Modeling versus Data Flow 37Schemas 41For Further Reading 42Chapter 3: The Relational Data Model45Understanding Relations 46Columns and Column Characteristics 47Rows and Row Characteristics 48Types of Tables 48A Notation for Relations 49Primary Keys 49Primary Keys to Identify People 50Avoiding Meaningful Primary Keys 51Concatenated Primary Keys 52All-Key Relations 53Representing Data Relationships 54Referential Integrity 57Foreign Keys and Primary Keys in the Same Table 57
CONTENTSViews 58The View Mechanism 58Why Use Views? 59The Data Dictionary 59Sample Data Dictionary Tables 60For Further Reading 62Chapter 4: Normalization 63Translating an ER Diagram into Relations 64Normal Forms 65First Normal Form 66Understanding Repeating Groups 67Handling Repeating Groups 68Problems with First Normal Form 70Second Normal Form 72Understanding Functional Dependencies 73Using Functional Dependencies to Reach 2NF 74Problems with 2NF Relations 75Third Normal Form 76Transitive Dependencies 76Boyce-Codd Normal Form 78Fourth Normal Form 79Multivalued Dependencies 81Normalized Relations and Database Performance 82Equi-Joins 82What's Really Going On: PRODUCT and RESTRICT 85The Bottom Line 88Chapter 5: Database Structure and Performance TuningIndexing 90Deciding Which Indexes to Create 91Clustering 92Partitioning 93Horizontal Partitioning 93Vertical Partitioning 94Chapter 6: Codd's Rules 97Rule 1: The Information Rule 98Rule 2: The Guaranteed Access Rule99
VlllRule 3: Systematic Treatment of Null Values 100Rule 4: Dynamic Online Catalog Based on the Relational ModelRule 5: The Comprehensive Data Sublanguage Rule 102Rule 6: The View Updating Rule 103Rule 7: High-Level Insert, Update, and Delete 103Rule 8: Physical Data Independence 104Rule 9: Logical Data Independence 105Rule 10: Integrity Independence 105Rule 11: Distribution Independence 107Rule 12: Nonsubversion Rule 107Part Two: Practice109Chapter 7: Using SQL to Implement a RelationalDesign 111Database Object Hierarchy 112Naming and Identifying Objects 113Schemas 114Creating a Schema 115Identifying the Schema You Want to Use 116Domains 116Tables 118Column Data Types 119Default Values 122NOT NULL Constraints 122Primary Keys 124Foreign Keys 126Additional Column Constraints 128Views 131Deciding Which Views to Create 131View Updatability Issues 132Creating Views 132Temporary Tables 134Creating Temporary Tables 135Loading Temporary Tables with Data 136Disposition of Temporary Table Rows 136Creating Indexes 137
CONTENTSIXModifying Database Elements 138Adding New Columns 138Adding Table Constraints 139Modifying Columns 139Deleting Elements 140Renaming Elements 141Deleting Database Elements 142Granting and Revoking Access RightsTypes of Access Rights 143Storing Access Rights 143Granting Rights 144Revoking Rights 145142Chapter 8: Using CASE Tools for Database DesignCASE Capabilities 148ER Diagram Reports 150Data Flow Diagrams 152The Data Dictionary 154Code Generation 158Sample Input and Output DesignsThe Drawing Environment 161159Chapter 9: Database Design Case Study #1:Mighty-Mite Motors 163Corporate Overview 164Product Development Division 164Manufacturing Division 170Marketing & Sales Division 171Current Information Systems 171Reengineering Project 172New Information Systems Division 173Basic System Goals 173Current Business Processes 174Designing the Database 182Examining the Data Flows 184Creating the ER Diagram 187Creating the Tables 193Generating the SQL 194147
CONTENTSXChapter 10: Database Design Case Study #2:East Coast Aquarium 201Organizational O v e r v i e w 202Animal Tracking Needs 204The Volunteer Organization 208The Volunteers Database 210Creating the Application Prototype 210Creating the ER Diagram 220Designing the Tables 220Generating the SQL 221The A n i m a l Tracking Database 221Highlights of the Application Prototype 223Creating the ER Diagram 228Creating the Tables 231Generating the SQL 232Chapter 11: Database Design Case Study #3:Independent Intelligence Agency 237Organizational O v e r v i e w 238Current Information Systems 240Summary of IS Needs 243System Specifications 244Designing the Database 249ER Diagram for People 250ER Diagram for Intelligence Gathering and SalesER Diagram for System Security 256ER Diagram for Equipment Tracking 257Designing the Tables 259Generating the SQL 260GlossaryIndex281273253
Relational Database Design Clearly Explained Jan L. Harrington 4Jt AP PROFESSIONAL AP
