Data Modeling and Database Design

by Scamell, Richard W.; Umanath, Narayan S.

• ISBN13: 9781423900832
• ISBN10: 1423900839
• Edition: 1st
• Format: Hardcover
• Copyright: 2007-01-17
• Publisher: Course Technology

Summary

Data Modeling and Database Design presents a conceptually complete coverage of indispensable topics that each MIS student should learn if that student takes only one database course. Database design and data modeling encompass the minimal set of topics addressing the core competency of knowledge students should acquire in the database area. The text, rich examples, and figures work together to cover material with a depth and precision that is not available in more introductory database books.

Author Biography

Dr. Narayan S. Umanath is Professor of Information Systems at the University of Cincinnati, Ohio Dr. Richard W. Scamell serves as Professor of Decision and Information Sciences in the C. T. Bauer College of Business at the University of Houston

Table of Contents

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