Data Modeling and Database Design Book

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