Data modeling and database design

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 Notes

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

Preface	p. xv
Chapter 1 Database Systems: Architecture and Components	p. 1
1.1 Data, Information, and Metadata	p. 1
1.2 Data Management	p. 2
1.3 Limitations of File-Processing Systems	p. 3
1.4 The ANSI/SPARC Three-Schema Architecture	p. 5
1.5 Characteristics of Database Systems	p. 8
1.5.1 What Is a Database System?	p. 10
1.5.2 What Is a Database Management System?	p. 11
1.5.3 Advantages of Database Systems	p. 13
1.6 Data Models	p. 14
1.6.1 Data Models and Database Design	p. 15
1.6.2 The Database Design Life Cycle	p. 16
Chapter Summary	p. 19
Exercises	p. 20
Selected Bibliography	p. 21
Part I Conceptual Data Modeling
Chapter 2 Foundation Concepts	p. 26
2.1 A Conceptual Modeling Framework	p. 26
2.2 ER Modeling Primitives	p. 26
2.3 Foundations of the ER Modeling Grammar	p. 28
2.3.1 Entity Types and Attributes	p. 28
2.3.2 Entity and Attribute-Level Data Integrity Constraints	p. 30
2.3.3 Relationship Types	p. 33
2.3.4 Structural Constraints of a Relationship Type	p. 38
2.3.5 Base Entity Types and Weak Entity Types	p. 49
2.4 Data Modeling Errors	p. 54
2.4.1 Vignette 1	p. 54
2.4.2 Vignette 2	p. 60
2.4.3 Vignette 3	p. 61
Chapter Summary	p. 68
Exercises	p. 69
Selected Bibliography	p. 73
Chapter 3 Entity-Relationship Modeling	p. 75
3.1 Bearcat Incorporated: A Case Study	p. 75
3.2 Applying the ER Modeling Grammar to the Conceptual Modeling Process	p. 77
3.2.1 The Presentation Layer ER Model	p. 78
3.2.2 The Presentation Layer ER Model for Bearcat Incorporated	p. 81
3.2.3 The Coarse-Granular Design-Specific ER Model	p. 95
3.2.4 The Fine-granular Design-Specific ER Model	p. 106
Chapter Summary	p. 113
Exercises	p. 113
Selected Bibliography	p. 118
Chapter 4 Enhanced Entity-Relationship (EER) Modeling	p. 119
4.1 Superclass/subclass Relationship	p. 119
4.1.1 Vignette 1	p. 120
4.1.2 A Motivating Exemplar	p. 124
4.1.3 General Properties of a Superclass/subclass Relationship	p. 125
4.1.4 Specialization and Generalization	p. 126
4.1.5 Specialization Hierarchy and Specialization Lattice	p. 133
4.1.6 Categorization	p. 136
4.1.7 Choosing the Appropriate EER Construct	p. 139
4.1.8 Aggregation	p. 144
4.2 Converting from the Presentation Layer to a Design-Specific EER Diagram	p. 146
4.3 Bearcat Incorporated Data Requirements Revisited	p. 148
4.4 ER Model for the Revised Story	p. 149
Chapter Summary	p. 157
Exercises	p. 157
Selected Bibliography	p. 162
Chapter 5 Modeling Complex Relationships	p. 163
5.1 The Ternary Relationship Type	p. 164
5.1.1 Vignette 1-Madeira College	p. 164
5.1.2 Vignette 2-Get Well Pharmacists, Inc.	p. 169
5.2 Beyond the Ternary Relationship Type	p. 171
5.2.1 The Case for a Cluster Entity Type	p. 171
5.2.2 Vignette 3-More on Madeira College	p. 172
5.2.3 Vignette 4-A More Complex Entity Clustering	p. 176
5.2.4 Cluster Entity Type-Additional Examples	p. 179
5.2.5 Madeira College-The Rest of the Story	p. 182
5.2.6 Clustering a Recursive Relationship Type	p. 186
5.3 The Weak Relationship Type	p. 190
5.4 Composites of Weak Relationship Types	p. 196
5.4.1 Inclusion Dependency in Composite Relationship Types	p. 196
5.4.2 Exclusion Dependency in Composites of Weak Relationship Types	p. 197
5.5 Decomposition of Complex Relationship Constructs	p. 198
5.5.1 Decomposing Ternary and Higher-Order Relationship Types	p. 198
5.5.2 Decomposing a Relationship Type with a Multi-valued Attribute	p. 200
5.5.3 Decomposing a Cluster Entity Type	p. 204
5.5.4 Decomposing a Weak Relationship Type	p. 206
5.6 Validation of the Conceptual Design	p. 209
5.6.1 Fan Trap	p. 210
5.6.2 Chasm Trap	p. 213
5.6.3 Miscellaneous Semantic Traps	p. 216
5.7 Cougar Medical Associates	p. 221
5.7.1 Conceptual Model for CMA: The Genesis	p. 223
5.7.2 Conceptual Model for CMA: The Next Generation	p. 228
5.7.3 The Design-Specific ER Model for CMA: The Final Frontier	p. 229
Chapter Summary	p. 236
Exercises	p. 236
Selected Bibliography	p. 240
Part II Logical Data Modeling
Chapter 6 The Relational Data Model	p. 244
6.1 Definition	p. 244
6.2 Characteristics of a Relation	p. 245
6.3 Data Integrity Constraints	p. 247
6.3.1 The Concept of Unique Identifiers	p. 248
6.3.2 Referential Integrity Constraint in the Relational Data Model	p. 252
6.4 A Brief Introduction to Relational Algebra	p. 254
6.4.1 Unary Operations: Selection ([sigma]) and Projection ([pi])	p. 254
6.4.2 Binary Operations: Union (U), Difference (-), and Intersection ([Characters not reproducible])	p. 256
6.4.3 The Natural Join (*) Operation	p. 258
6.5 Views and Materialized Views in the Relational Data Model	p. 259
6.6 The Issue of Information Preservation	p. 260
6.7 Mapping an ER Model to a Logical Schema	p. 261
6.7.1 Information-Reducing Mapping of ER Constructs	p. 261
6.7.2 An Information-Preserving Mapping	p. 277
6.8 Mapping Enhanced ER Model Constructs to a Logical Schema	p. 281
6.8.1 Information-Reducing Mapping of EER Constructs	p. 281
6.8.2 Information-Preserving Grammar for Enhanced ER Modeling Constructs	p. 289
Chapter Summary	p. 296
Exercises	p. 298
Selected Bibliography	p. 304
Part III Normalization
Chapter 7 Functional Dependencies	p. 308
7.1 A Motivating Exemplar	p. 308
7.2 Functional Dependencies	p. 314
7.2.1 Definition of Functional Dependency	p. 314
7.2.2 Inference Rules for Functional Dependencies	p. 315
7.2.3 Minimal Cover for a Set of Functional Dependencies	p. 317
7.2.4 Closure of a Set of Attributes	p. 322
7.2.5 Whence Do FDs Arise?	p. 323
7.3 Candidate Keys Revisited	p. 324
7.3.1 Deriving Candidate Key(s) by Synthesis	p. 325
7.3.2 Deriving Candidate Keys by Decomposition	p. 329
7.3.3 Deriving a Candidate Key-Another Example	p. 332
7.3.4 Prime and Non-prime Attributes	p. 336
Chapter Summary	p. 340
Exercises	p. 340
Selected Bibliography	p. 344
Chapter 8 Normal Forms Based on Functional Dependencies	p. 345
8.1 Normalization	p. 345
8.1.1 First Normal Form (1NF)	p. 346
8.1.2 Second Normal Form (2NF)	p. 347
8.1.3 Third Normal Form (3NF)	p. 351
8.1.4 Boyce-Codd Normal Form (BCNF)	p. 354
8.1.5 Side Effects of Normalization	p. 357
8.1.6 Summary Notes on Normal Forms	p. 367
8.2 The Motivating Exemplar Revisited	p. 369
8.3 A Comprehensive Approach to Normalization	p. 372
8.3.1 Case 1	p. 373
8.3.2 Case 2	p. 380
8.3.3 Case 3	p. 386
8.4 Denormalization	p. 391
8.5 Role of Reverse Engineering in Data Modeling	p. 392
8.5.1 Reverse Engineering the Normalized Solution of Case 1	p. 394
8.5.2 Reverse Engineering the Normalized Solution of Case 2	p. 399
8.5.3 Reverse Engineering the Normalized Solution of Case 3	p. 401
Chapter Summary	p. 406
Exercises	p. 407
Selected Bibliography	p. 416
Chapter 9 Higher Normal Forms	p. 417
9.1 Multi-valued Dependency	p. 417
9.1.1 A Motivating Exemplar for Multi-valued Dependency	p. 417
9.1.2 Multi-valued Dependency Defined	p. 419
9.1.3 Inference Rules for Multi-valued Dependencies	p. 420
9.2 Fourth Normal Form (4NF)	p. 422
9.3 Resolution of a 4NF Violation-A Comprehensive Example	p. 425
9.4 Generality of Multi-valued Dependencies and 4NF	p. 428
9.5 Join Dependencies and Fifth Normal Form (5NF)	p. 429
9.6 A Note on Domain-Key Normal Form (DK/NF)	p. 434
Chapter Summary	p. 435
Exercises	p. 435
Selected Bibliography	p. 439
Part IV Database Implementation Using the Relational Data Model
Chapter 10 Database Creation	p. 444
10.1 Data Definition Using SQL	p. 444
10.1.1 Base Table Specification in SQL/DDL	p. 445
10.1.2 Specification of User-Defined Domains	p. 462
10.1.3 Schema and Catalog Concepts in SQL/DDL	p. 466
10.2 Data Population Using SQL	p. 469
10.2.1 The Insert Statement	p. 470
10.2.2 The Delete Statement	p. 472
10.2.3 The Update Statement	p. 474
10.3 Access Control in the SQL-92 Standard	p. 475
10.3.1 The Grant and Revoke Statements	p. 476
10.3.2 Some Examples of Granting and Revoking Privileges	p. 477
Chapter Summary	p. 486
Exercises	p. 487
Selected Bibliography	p. 492
Chapter 11 Data Manipulation: Relational Algebra and SQL	p. 493
11.1 Relational Algebra	p. 493
11.1.1 Unary Operators	p. 496
11.1.2 Binary Operators	p. 499
11.2 Structured Query Language (SQL)	p. 516
11.2.1 SQL Queries Based on a Single Table	p. 518
11.2.2 SQL Queries Based on Binary Operators	p. 543
11.2.3 Subqueries	p. 557
Chapter Summary	p. 572
Exercises	p. 573
SQL Projects	p. 577
Selected Bibliography	p. 577
Chapter 12 Advanced Data Manipulation Using SQL	p. 579
12.1 Assertions, Triggers, and Views	p. 579
12.1.1 Specifying an Assertion in SQL	p. 579
12.1.2 Triggers in SQL	p. 585
12.1.3 Specifying Views in SQL/DDL	p. 598
12.1.4 The Division Operation	p. 601
12.2 SQL-92 Built-in Functions	p. 604
12.2.1 The Substring Function	p. 606
12.2.2 The Char_Length (char) Function	p. 608
12.2.3 The Trim Function	p. 610
12.2.4 The Translate Function	p. 614
12.2.5 The Position Function	p. 614
12.2.6 Combining the INSTR and SUBSTR Functions	p. 616
12.3 Some Brief Comments on Handling Dates and Times	p. 617
12.4 A Potpourri of Other SQL Queries	p. 622
12.4.1 Concluding Example 1	p. 622
12.4.2 Concluding Example 2	p. 624
12.4.3 Concluding Example 3	p. 626
12.4.4 Concluding Example 4	p. 626
12.4.5 Concluding Example 5	p. 627
12.4.6 Concluding Example 6	p. 628
Chapter Summary	p. 629
Exercises	p. 629
SQL Project 1 p. 630
SQL Project 2 p. 639
SQL Project 3 p. 645
Selected Bibliography	p. 652
Appendix A Data Modeling Architectures Based on the Inverted Tree and Network Data Structures	p. 653
A.1 Logical Data Structures	p. 653
A.1.1 Inverted Tree Structure	p. 653
A.1.2 Network Data Structure	p. 654
A.2 Logical Data Model Architectures	p. 655
A.2.1 Hierarchical Data Model	p. 656
A.2.2 CODASYL Data Model	p. 660
Summary	p. 663
Selected Bibliography	p. 663
Appendix B Object-Oriented Data Modeling Architectures	p. 665
B.1 The Object-Oriented Data Model	p. 665
B.1.1 Overview of OO Concepts	p. 666
B.1.2 A Note on UML	p. 669
B.2 The Object-Relational Data Model	p. 671
Summary	p. 672
Selected Bibliography	p. 672
Appendix C Overview of SQL Reserved Words	p. 673
Appendix D SQL Select Statement Features	p. 683
Index	p. 689

Available:*

On Order

Summary

Summary

Author Notes

Table of Contents