Data Modeling and Database Design

Preface     xvDatabase Systems: Architecture and Components     1Data, Information, and Metadata     1Data Management     2Limitations of File-Processing Systems     3The ANSI/SPARC Three-Schema Architecture     5Characteristics of Database Systems     8What Is a Database System?     10What Is a Database Management System?     11Advantages of Database Systems     13Data Models     14Data Models and Database Design     15The Database Design Life Cycle     16Chapter Summary     19Exercises     20Selected Bibliography     21Conceptual Data ModelingFoundation Concepts     26A Conceptual Modeling Framework     26ER Modeling Primitives     26Foundations of the ER Modeling Grammar     28Entity Types and Attributes     28Entity and Attribute-Level Data Integrity Constraints     30Relationship Types     33Structural Constraints of a Relationship Type     38Base Entity Types and Weak Entity Types     49Data Modeling Errors     54Vignette 1     54Vignette 2     60Vignette 3     61Chapter Summary     68Exercises     69Selected Bibliography     73Entity-Relationship Modeling     75Bearcat Incorporated: A Case Study     75Applying the ER Modeling Grammar to the Conceptual Modeling Process     77The Presentation Layer ER Model     78The Presentation Layer ER Model for Bearcat Incorporated     81The Coarse-Granular Design-Specific ER Model     95The Fine-granular Design-Specific ER Model     106Chapter Summary     113Exercises     113Selected Bibliography     118Enhanced Entity-Relationship (EER) Modeling     119Superclass/subclass Relationship     119Vignette 1     120A Motivating Exemplar     124General Properties of a Superclass/subclass Relationship     125Specialization and Generalization     126Specialization Hierarchy and Specialization Lattice     133Categorization     136Choosing the Appropriate EER Construct     139Aggregation     144Converting from the Presentation Layer to a Design-Specific EER Diagram     146Bearcat Incorporated Data Requirements Revisited     148ER Model for the Revised Story     149Chapter Summary     157Exercises     157Selected Bibliography     162Modeling Complex Relationships     163The Ternary Relationship Type     164Vignette 1-Madeira College     164Vignette 2-Get Well Pharmacists, Inc.     169Beyond the Ternary Relationship Type     171The Case for a Cluster Entity Type     171Vignette 3-More on Madeira College     172Vignette 4-A More Complex Entity Clustering     176Cluster Entity Type-Additional Examples     179Madeira College-The Rest of the Story     182Clustering a Recursive Relationship Type     186The Weak Relationship Type     190Composites of Weak Relationship Types     196Inclusion Dependency in Composite Relationship Types     196Exclusion Dependency in Composites of Weak Relationship Types     197Decomposition of Complex Relationship Constructs     198Decomposing Ternary and Higher-Order Relationship Types     198Decomposing a Relationship Type with a Multi-valued Attribute      200Decomposing a Cluster Entity Type     204Decomposing a Weak Relationship Type     206Validation of the Conceptual Design     209Fan Trap     210Chasm Trap     213Miscellaneous Semantic Traps     216Cougar Medical Associates     221Conceptual Model for CMA: The Genesis     223Conceptual Model for CMA: The Next Generation     228The Design-Specific ER Model for CMA: The Final Frontier     229Chapter Summary     236Exercises     236Selected Bibliography     240Logical Data ModelingThe Relational Data Model     244Definition     244Characteristics of a Relation     245Data Integrity Constraints     247The Concept of Unique Identifiers     248Referential Integrity Constraint in the Relational Data Model     252A Brief Introduction to Relational Algebra     254Unary Operations: Selection ([sigma]) and Projection ([pi])     254Binary Operations: Union (U), Difference (-), and Intersection ([Characters not reproducible])     256The Natural Join (*) Operation     258Views and Materialized Views in the Relational Data Model      259The Issue of Information Preservation     260Mapping an ER Model to a Logical Schema     261Information-Reducing Mapping of ER Constructs     261An Information-Preserving Mapping     277Mapping Enhanced ER Model Constructs to a Logical Schema     281Information-Reducing Mapping of EER Constructs     281Information-Preserving Grammar for Enhanced ER Modeling Constructs     289Chapter Summary     296Exercises     298Selected Bibliography     304NormalizationFunctional Dependencies     308A Motivating Exemplar     308Functional Dependencies     314Definition of Functional Dependency     314Inference Rules for Functional Dependencies     315Minimal Cover for a Set of Functional Dependencies     317Closure of a Set of Attributes     322Whence Do FDs Arise?     323Candidate Keys Revisited     324Deriving Candidate Key(s) by Synthesis     325Deriving Candidate Keys by Decomposition     329Deriving a Candidate Key-Another Example     332Prime and Non-prime Attributes     336Chapter Summary      340Exercises     340Selected Bibliography     344Normal Forms Based on Functional Dependencies     345Normalization     345First Normal Form (1NF)     346Second Normal Form (2NF)     347Third Normal Form (3NF)     351Boyce-Codd Normal Form (BCNF)     354Side Effects of Normalization     357Summary Notes on Normal Forms     367The Motivating Exemplar Revisited     369A Comprehensive Approach to Normalization     372Case 1     373Case 2     380Case 3     386Denormalization     391Role of Reverse Engineering in Data Modeling     392Reverse Engineering the Normalized Solution of Case 1     394Reverse Engineering the Normalized Solution of Case 2     399Reverse Engineering the Normalized Solution of Case 3     401Chapter Summary     406Exercises     407Selected Bibliography     416Higher Normal Forms     417Multi-valued Dependency     417A Motivating Exemplar for Multi-valued Dependency     417Multi-valued Dependency Defined      419Inference Rules for Multi-valued Dependencies     420Fourth Normal Form (4NF)     422Resolution of a 4NF Violation-A Comprehensive Example     425Generality of Multi-valued Dependencies and 4NF     428Join Dependencies and Fifth Normal Form (5NF)     429A Note on Domain-Key Normal Form (DK/NF)     434Chapter Summary     435Exercises     435Selected Bibliography     439Database Implementation Using the Relational Data ModelDatabase Creation     444Data Definition Using SQL     444Base Table Specification in SQL/DDL     445Specification of User-Defined Domains     462Schema and Catalog Concepts in SQL/DDL     466Data Population Using SQL     469The Insert Statement     470The Delete Statement     472The Update Statement     474Access Control in the SQL-92 Standard     475The Grant and Revoke Statements     476Some Examples of Granting and Revoking Privileges     477Chapter Summary     486Exercises     487Selected Bibliography     492Data Manipulation: Relational Algebra and SQL      493Relational Algebra     493Unary Operators     496Binary Operators     499Structured Query Language (SQL)     516SQL Queries Based on a Single Table     518SQL Queries Based on Binary Operators     543Subqueries     557Chapter Summary     572Exercises     573SQL Projects     577Selected Bibliography     577Advanced Data Manipulation Using SQL     579Assertions, Triggers, and Views     579Specifying an Assertion in SQL     579Triggers in SQL     585Specifying Views in SQL/DDL     598The Division Operation     601SQL-92 Built-in Functions     604The Substring Function     606The Char_Length (char) Function     608The Trim Function     610The Translate Function     614The Position Function     614Combining the INSTR and SUBSTR Functions     616Some Brief Comments on Handling Dates and Times     617A Potpourri of Other SQL Queries     622Concluding Example 1     622Concluding Example 2      624Concluding Example 3     626Concluding Example 4     626Concluding Example 5     627Concluding Example 6     628Chapter Summary     629Exercises     629SQL Project 1     630SQL Project 2     639SQL Project 3     645Selected Bibliography     652Data Modeling Architectures Based on the Inverted Tree and Network Data Structures     653Logical Data Structures     653Inverted Tree Structure     653Network Data Structure     654Logical Data Model Architectures     655Hierarchical Data Model     656CODASYL Data Model     660Summary     663Selected Bibliography     663Object-Oriented Data Modeling Architectures     665The Object-Oriented Data Model     665Overview of OO Concepts     666A Note on UML     669The Object-Relational Data Model     671Summary     672Selected Bibliography     672Overview of SQL Reserved Words     673SQL Select Statement Features     683Index     689

\ From the Publisher"The writing style is very good compared to other books. Discussion of the content is accompanied by detailed explanation and adequate examples which make these concepts easy to understand." - Jaymeen Shah, Texas State University\ \