| |
| |
| Preface | |
| |
| |
| |
| Database Systems: Architecture and Components | |
| |
| |
| |
| Data, Information, and Metadata | |
| |
| |
| |
| Data Management | |
| |
| |
| |
| Limitations of File-Processing Systems | |
| |
| |
| |
| The ANSI/SPARC Three-Schema Architecture | |
| |
| |
| |
| Characteristics of Database Systems | |
| |
| |
| |
| What Is a Database System? | |
| |
| |
| |
| What Is a Database Management System? | |
| |
| |
| |
| Advantages of Database Systems | |
| |
| |
| |
| Data Models | |
| |
| |
| |
| Data Models and Database Design | |
| |
| |
| |
| The Database Design Life Cycle | |
| |
| |
| Chapter Summary | |
| |
| |
| Exercises | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Conceptual Data Modeling | |
| |
| |
| |
| Foundation Concepts | |
| |
| |
| |
| A Conceptual Modeling Framework | |
| |
| |
| |
| ER Modeling Primitives | |
| |
| |
| |
| Foundations of the ER Modeling Grammar | |
| |
| |
| |
| Entity Types and Attributes | |
| |
| |
| |
| Entity and Attribute-Level Data Integrity Constraints | |
| |
| |
| |
| Relationship Types | |
| |
| |
| |
| Structural Constraints of a Relationship Type | |
| |
| |
| |
| Base Entity Types and Weak Entity Types | |
| |
| |
| |
| Data Modeling Errors | |
| |
| |
| |
| Vignette 1 | |
| |
| |
| |
| Vignette 2 | |
| |
| |
| |
| Vignette 3 | |
| |
| |
| Chapter Summary | |
| |
| |
| Exercises | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Entity-Relationship Modeling | |
| |
| |
| |
| Bearcat Incorporated: A Case Study | |
| |
| |
| |
| Applying the ER Modeling Grammar to the Conceptual Modeling Process | |
| |
| |
| |
| The Presentation Layer ER Model | |
| |
| |
| |
| The Presentation Layer ER Model for Bearcat Incorporated | |
| |
| |
| |
| The Coarse-Granular Design-Specific ER Model | |
| |
| |
| |
| The Fine-granular Design-Specific ER Model | |
| |
| |
| Chapter Summary | |
| |
| |
| Exercises | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Enhanced Entity-Relationship (EER) Modeling | |
| |
| |
| |
| Superclass/subclass Relationship | |
| |
| |
| |
| Vignette 1 | |
| |
| |
| |
| A Motivating Exemplar | |
| |
| |
| |
| General Properties of a Superclass/subclass Relationship | |
| |
| |
| |
| Specialization and Generalization | |
| |
| |
| |
| Specialization Hierarchy and Specialization Lattice | |
| |
| |
| |
| Categorization | |
| |
| |
| |
| Choosing the Appropriate EER Construct | |
| |
| |
| |
| Aggregation | |
| |
| |
| |
| Converting from the Presentation Layer to a Design-Specific EER Diagram | |
| |
| |
| |
| Bearcat Incorporated Data Requirements Revisited | |
| |
| |
| |
| ER Model for the Revised Story | |
| |
| |
| Chapter Summary | |
| |
| |
| Exercises | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Modeling Complex Relationships | |
| |
| |
| |
| The Ternary Relationship Type | |
| |
| |
| |
| Vignette 1-Madeira College | |
| |
| |
| |
| Vignette 2-Get Well Pharmacists, Inc. | |
| |
| |
| |
| Beyond the Ternary Relationship Type | |
| |
| |
| |
| The Case for a Cluster Entity Type | |
| |
| |
| |
| Vignette 3-More on Madeira College | |
| |
| |
| |
| Vignette 4-A More Complex Entity Clustering | |
| |
| |
| |
| Cluster Entity Type-Additional Examples | |
| |
| |
| |
| Madeira College-The Rest of the Story | |
| |
| |
| |
| Clustering a Recursive Relationship Type | |
| |
| |
| |
| The Weak Relationship Type | |
| |
| |
| |
| Composites of Weak Relationship Types | |
| |
| |
| |
| Inclusion Dependency in Composite Relationship Types | |
| |
| |
| |
| Exclusion Dependency in Composites of Weak Relationship Types | |
| |
| |
| |
| Decomposition of Complex Relationship Constructs | |
| |
| |
| |
| Decomposing Ternary and Higher-Order Relationship Types | |
| |
| |
| |
| Decomposing a Relationship Type with a Multi-valued Attribute | |
| |
| |
| |
| Decomposing a Cluster Entity Type | |
| |
| |
| |
| Decomposing a Weak Relationship Type | |
| |
| |
| |
| Validation of the Conceptual Design | |
| |
| |
| |
| Fan Trap | |
| |
| |
| |
| Chasm Trap | |
| |
| |
| |
| Miscellaneous Semantic Traps | |
| |
| |
| |
| Cougar Medical Associates | |
| |
| |
| |
| Conceptual Model for CMA: The Genesis | |
| |
| |
| |
| Conceptual Model for CMA: The Next Generation | |
| |
| |
| |
| The Design-Specific ER Model for CMA: The Final Frontier | |
| |
| |
| Chapter Summary | |
| |
| |
| Exercises | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Logical Data Modeling | |
| |
| |
| |
| The Relational Data Model | |
| |
| |
| |
| Definition | |
| |
| |
| |
| Characteristics of a Relation | |
| |
| |
| |
| Data Integrity Constraints | |
| |
| |
| |
| The Concept of Unique Identifiers | |
| |
| |
| |
| Referential Integrity Constraint in the Relational Data Model | |
| |
| |
| |
| A Brief Introduction to Relational Algebra | |
| |
| |
| |
| Unary Operations: Selection ([sigma]) and Projection ([pi]) | |
| |
| |
| |
| Binary Operations: Union (U), Difference (-), and Intersection ([Characters not reproducible]) | |
| |
| |
| |
| The Natural Join (*) Operation | |
| |
| |
| |
| Views and Materialized Views in the Relational Data Model | |
| |
| |
| |
| The Issue of Information Preservation | |
| |
| |
| |
| Mapping an ER Model to a Logical Schema | |
| |
| |
| |
| Information-Reducing Mapping of ER Constructs | |
| |
| |
| |
| An Information-Preserving Mapping | |
| |
| |
| |
| Mapping Enhanced ER Model Constructs to a Logical Schema | |
| |
| |
| |
| Information-Reducing Mapping of EER Constructs | |
| |
| |
| |
| Information-Preserving Grammar for Enhanced ER Modeling Constructs | |
| |
| |
| Chapter Summary | |
| |
| |
| Exercises | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Normalization | |
| |
| |
| |
| Functional Dependencies | |
| |
| |
| |
| A Motivating Exemplar | |
| |
| |
| |
| Functional Dependencies | |
| |
| |
| |
| Definition of Functional Dependency | |
| |
| |
| |
| Inference Rules for Functional Dependencies | |
| |
| |
| |
| Minimal Cover for a Set of Functional Dependencies | |
| |
| |
| |
| Closure of a Set of Attributes | |
| |
| |
| |
| Whence Do FDs Arise? | |
| |
| |
| |
| Candidate Keys Revisited | |
| |
| |
| |
| Deriving Candidate Key(s) by Synthesis | |
| |
| |
| |
| Deriving Candidate Keys by Decomposition | |
| |
| |
| |
| Deriving a Candidate Key-Another Example | |
| |
| |
| |
| Prime and Non-prime Attributes | |
| |
| |
| Chapter Summary | |
| |
| |
| Exercises | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Normal Forms Based on Functional Dependencies | |
| |
| |
| |
| Normalization | |
| |
| |
| |
| First Normal Form (1NF) | |
| |
| |
| |
| Second Normal Form (2NF) | |
| |
| |
| |
| Third Normal Form (3NF) | |
| |
| |
| |
| Boyce-Codd Normal Form (BCNF) | |
| |
| |
| |
| Side Effects of Normalization | |
| |
| |
| |
| Summary Notes on Normal Forms | |
| |
| |
| |
| The Motivating Exemplar Revisited | |
| |
| |
| |
| A Comprehensive Approach to Normalization | |
| |
| |
| |
| Case 1 | |
| |
| |
| |
| Case 2 | |
| |
| |
| |
| Case 3 | |
| |
| |
| |
| Denormalization | |
| |
| |
| |
| Role of Reverse Engineering in Data Modeling | |
| |
| |
| |
| Reverse Engineering the Normalized Solution of Case 1 | |
| |
| |
| |
| Reverse Engineering the Normalized Solution of Case 2 | |
| |
| |
| |
| Reverse Engineering the Normalized Solution of Case 3 | |
| |
| |
| Chapter Summary | |
| |
| |
| Exercises | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Higher Normal Forms | |
| |
| |
| |
| Multi-valued Dependency | |
| |
| |
| |
| A Motivating Exemplar for Multi-valued Dependency | |
| |
| |
| |
| Multi-valued Dependency Defined | |
| |
| |
| |
| Inference Rules for Multi-valued Dependencies | |
| |
| |
| |
| Fourth Normal Form (4NF) | |
| |
| |
| |
| Resolution of a 4NF Violation-A Comprehensive Example | |
| |
| |
| |
| Generality of Multi-valued Dependencies and 4NF | |
| |
| |
| |
| Join Dependencies and Fifth Normal Form (5NF) | |
| |
| |
| |
| A Note on Domain-Key Normal Form (DK/NF) | |
| |
| |
| Chapter Summary | |
| |
| |
| Exercises | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Database Implementation Using the Relational Data Model | |
| |
| |
| |
| Database Creation | |
| |
| |
| |
| Data Definition Using SQL | |
| |
| |
| |
| Base Table Specification in SQL/DDL | |
| |
| |
| |
| Specification of User-Defined Domains | |
| |
| |
| |
| Schema and Catalog Concepts in SQL/DDL | |
| |
| |
| |
| Data Population Using SQL | |
| |
| |
| |
| The Insert Statement | |
| |
| |
| |
| The Delete Statement | |
| |
| |
| |
| The Update Statement | |
| |
| |
| |
| Access Control in the SQL-92 Standard | |
| |
| |
| |
| The Grant and Revoke Statements | |
| |
| |
| |
| Some Examples of Granting and Revoking Privileges | |
| |
| |
| Chapter Summary | |
| |
| |
| Exercises | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Data Manipulation: Relational Algebra and SQL | |
| |
| |
| |
| Relational Algebra | |
| |
| |
| |
| Unary Operators | |
| |
| |
| |
| Binary Operators | |
| |
| |
| |
| Structured Query Language (SQL) | |
| |
| |
| |
| SQL Queries Based on a Single Table | |
| |
| |
| |
| SQL Queries Based on Binary Operators | |
| |
| |
| |
| Subqueries | |
| |
| |
| Chapter Summary | |
| |
| |
| Exercises | |
| |
| |
| SQL Projects | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Advanced Data Manipulation Using SQL | |
| |
| |
| |
| Assertions, Triggers, and Views | |
| |
| |
| |
| Specifying an Assertion in SQL | |
| |
| |
| |
| Triggers in SQL | |
| |
| |
| |
| Specifying Views in SQL/DDL | |
| |
| |
| |
| The Division Operation | |
| |
| |
| |
| SQL-92 Built-in Functions | |
| |
| |
| |
| The Substring Function | |
| |
| |
| |
| The Char_Length (char) Function | |
| |
| |
| |
| The Trim Function | |
| |
| |
| |
| The Translate Function | |
| |
| |
| |
| The Position Function | |
| |
| |
| |
| Combining the INSTR and SUBSTR Functions | |
| |
| |
| |
| Some Brief Comments on Handling Dates and Times | |
| |
| |
| |
| A Potpourri of Other SQL Queries | |
| |
| |
| |
| Concluding Example 1 | |
| |
| |
| |
| Concluding Example 2 | |
| |
| |
| |
| Concluding Example 3 | |
| |
| |
| |
| Concluding Example 4 | |
| |
| |
| |
| Concluding Example 5 | |
| |
| |
| |
| Concluding Example 6 | |
| |
| |
| Chapter Summary | |
| |
| |
| Exercises | |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Data Modeling Architectures Based on the Inverted Tree and Network Data Structures | |
| |
| |
| |
| Logical Data Structures | |
| |
| |
| |
| Inverted Tree Structure | |
| |
| |
| |
| Network Data Structure | |
| |
| |
| |
| Logical Data Model Architectures | |
| |
| |
| |
| Hierarchical Data Model | |
| |
| |
| |
| CODASYL Data Model | |
| |
| |
| Summary | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Object-Oriented Data Modeling Architectures | |
| |
| |
| |
| The Object-Oriented Data Model | |
| |
| |
| |
| Overview of OO Concepts | |
| |
| |
| |
| A Note on UML | |
| |
| |
| |
| The Object-Relational Data Model | |
| |
| |
| Summary | |
| |
| |
| Selected Bibliography | |
| |
| |
| |
| Overview of SQL Reserved Words | |
| |
| |
| |
| SQL Select Statement Features | |
| |
| |
| Index | |