Data Modeling and Database Design / Edition 2

by Narayan S. Umanath

Overview

Data Modeling and Database Design presents 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.

Features: Offers comprehensive coverage of conceptual modeling using the entity-relationship modeling grammar, including substantial details of complex relationships relevant to real-world scenarios. Addresses the often-neglected concept of information preservation in data model mapping across the design tiers and offers a new grammar for information-preserving logical schema. Presents nuances critical to robust logical design rarely found in business database books under the topic of normalization. Includes detailed coverage of relational algebra supported by a significant number of examples of their operationalization in ANSI/ISO SQL. Provides a plethora of vignettes, figures, and tables that comprehensively reinforce key concepts. Features challenging end-of-chapter exercises that provide opportunity to learn and assimilate through practice.

About the Author:
Dr. Narayan S. Umanath is Professor of Information Systems at the University of Cincinnati, Ohio

About the Author:
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

Product Details

ISBN-13:	2901285085257
Publisher:	Cengage Learning
Publication date:	06/13/2014
Edition description:	New Edition
Pages:	720
Product dimensions:	6.50(w) x 1.50(h) x 9.50(d)

About the Author

Narayan S. Umanath is Professor [Emeritus] of Information Systems at the University of Cincinnati (UC) in Cincinnati, Ohio, USA. He has been at UC since 1996 and served as the department head of Information Systems (IS) for 7 years. Before that, he has been a faculty member at the Pennsylvania State University at the main (University Park) campus and the University of Tulsa. Umanath also serves as a member of the faculty in the Department of Mathematics & Computer Science (DMACS) at the Sri Sathya Sai Institute of Higher Learning (SSSIHL) in Prashanti Nilayam, India in the capacity of Visiting Professor since 2003. Entering academia after 17 years of technical and managerial experience in software development, he received his Ph.D. in Business Administration from the University of Houston in 1987. His undergraduate and graduate educations are in mechanical engineering and industrial engineering, respectively. Umanath has research publications in the domains of data visualization for decision support, agency theory applications in the design of compensation contracts, organizational computing, and electronic integration in supply channels; his current research interests are in the domains of knowledge exchange in supply-chain relationships, and data modeling & data warehousing. His research publications have appeared in Management Science, Decision Sciences, Journal of MIS, Communications of the ACM, International Journal of Information Management, Information & Management, Information Resources Management Journal, Journal of Managerial Issues and National Academy Science Letters. Umanath has also co-authored a text book titled "Data Modeling & Database Design".

Richard W. Scamell serves as Associate Dean for Student Affairs and Professor of Decision and Information Sciences in the C. T. Bauer College of Business at the University of Houston. He received his Ph.D. degree from The University of Texas at Austin. Since joining the faculty at Houston in 1972, he has taught more than two dozen different courses at the undergraduate, masters, and doctoral levels, three of which have been focused in the database area. His publications have appeared in journals such as Management Science, MIS Quarterly, Academy of Management Journal, Decision Sciences, IEEE Transactions on Software Engineering, Communications of the ACM, Omega, and Information and Management.

Table of Contents

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