Database Modeling and Design: Logical Design / Edition 5

by Toby J. Teorey, Sam S. Lightstone, Tom Nadeau, H.V. Jagadish

Overview

Database Modeling and Design, Fifth Edition, focuses on techniques for database design in relational database systems.

This extensively revised fifth edition features clear explanations, lots of terrific examples and an illustrative case, and practical advice, with design rules that are applicable to any SQL-based system. The common examples are based on real-life experiences and have been thoroughly class-tested.

This book is immediately useful to anyone tasked with the creation of data models for the integration of large-scale enterprise data. It is ideal for a stand-alone data management course focused on logical database design, or a supplement to an introductory text for introductory database management.

Product Details

ISBN-13:	9780123820204
Publisher:	Elsevier Science
Publication date:	02/10/2011
Series:	The Morgan Kaufmann Series in Data Management Systems
Edition description:	New Edition
Pages:	352
Product dimensions:	7.40(w) x 9.10(h) x 1.00(d)

About the Author

Toby J. Teorey is a professor in the Electrical Engineering and Computer Science Department at the University of Michigan, Ann Arbor. He received his B.S. and M.S. degrees in electrical engineering from the University of Arizona, Tucson, and a Ph.D. in computer sciences from the University of Wisconsin, Madison. He was general chair of the 1981 ACM SIGMOD Conference and program chair for the 1991 Entity-Relationship Conference. Professor Teorey’s current research focuses on database design and data warehousing, OLAP, advanced database systems, and performance of computer networks. He is a member of the ACM and the IEEE Computer Society.

Sam Lightstone is a Senior Technical Staff Member and Development Manager with IBM’s DB2 product development team. His work includes numerous topics in autonomic computing and relational database management systems. He is cofounder and leader of DB2’s autonomic computing R&D effort. He is Chair of the IEEE Data Engineering Workgroup on Self Managing Database Systems and a member of the IEEE Computer Society Task Force on Autonomous and Autonomic Computing. In 2003 he was elected to the Canadian Technical Excellence Council, the Canadian affiliate of the IBM Academy of Technology.
He is an IBM Master Inventor with over 25 patents and patents pending; he has published widely on autonomic computing for relational database systems. He has been with IBM since 1991.

Tom Nadeau is the founder of Aladdin Software (aladdinsoftware.com) and works in the area of data and text mining. He received his B.S. degree in computer science and M.S. and Ph.D. degrees in electrical engineering and computer science from the University of Michigan, Ann Arbor. His technical interests include data warehousing, OLAP, data mining and machine learning. He won the best paper award at the 2001 IBM CASCON Conference.

H.V. Jagadish is a professor in EE and CS at the University of Michigan, Ann Arbor, where he is part of the database group affiliated with the bioinformatics program and the Center for Computational Medicine and Bioinformatics. Prior to joining the Michigan faculty, he spent over a decade at AT&T Bell Laboratories as a research scientist where he became head of the Database division.

Table of Contents

Database Modeling & Design: The Fundamental Principles, Second Edition
by Toby J. Teorey

Chapter 1 Introduction
1.1 Data and Database Management
1.2 Data Modeling and the Entity-Relationship Approach
1.3 The Database Life Cycle
1.4 Summary
Literature Summary

Chapter 2 The ER Model: Basic Concepts
2.1 Fundamental ER Constructs
2.1.1 Basic Objects: Entities, Relationships, Attributes
2.1.2 Degree of a Relationship
2.1.3 Connectivity and Cardinality of a Relationship
2.1.4 Attributes of a Relationship
2.1.5 Existence of an Entity in a Relationship
2.1.6 Alternative ER Notations
2.2 Advanced ER Constructs
2.2.1 Generalization: Supertypes and Subtypes
2.2.2 Aggregation
2.2.3 Ternary Relationships
2.2.4 General n-ary Relationships
2.2.5 ER Constraints: Extensions from the NIAM Model
2.2.6 Entity Integrity, Referential Integrity, and ID Dependency
2.3 Object-Oriented Data Modeling
2.3.1 Object-Oriented Concepts
2.3.2 Object Modeling Versus ER Modeling
2.4 Summary
Literature Summary
Exercises

Chapter 3 ER Modeling in Logical Database Design
3.1 Introduction
3.2 Requirements Analysis and ER Modeling
3.2.1 Classify Entities and Attributes
3.2.2 Identify the Generalization Hierarchies
3.2.3 Define Relationships
3.2.4 Example of ER Modeling: Company Personnel and Project Database
3.3 View Integration
3.3.1 Preintegration Analysis
3.3.2 Comparison of Schemas
3.3.3 Conformation of Schemas
3.3.4 Merging and Restructuring of Schemas
3.3.5 Example of View Integration
3.4 Entity Clustering
3.4.1 Clustering Concepts
3.4.2 Grouping Operations
3.4.3 Clustering Technique
3.5 Summary
Literature Summary
Exercises

Chapter 4 Transformation of the ER Model to SQL
4.1 Transformation Rules and SQL Constructs
4.1.1 Binary Relationships
4.1.2 Binary Recursive Relationships
4.1.3 Ternary and n-ary Relationships
4.1.4 Generalization and Aggregation
4.1.5 Multiple Relationships
4.1.6 Weak Entities
4.2 Transformation Steps
4.2.1 Entity Transformation
4.2.2 Many-to-Many Binary Relationship Transformation
4.2.3 Ternary Relationship Transformation
4.2.4 Example of ER-to-SQL Transformation
4.3 Summary
Literature Summary
Exercises

Chapter 5 Normalization
5.1 Fundamentals of Normalization
5.1.1 First Normal Form
5.1.2 Superkeys, Candidate Keys, and Primary Keys
5.1.3 Second Normal Form
5.1.4 Third Normal Form
5.1.5 Boyce-Codd Normal Form
5.2 The Design of Normalized Tables: A Simple Example
5.3 Normalization of Candidate Tables Derived from ER Diagrams
5.4 Determining the Minimum Set of 3NF Tables
5.4.1 Elimination of Extraneous Attributes
5.4.2 Search for a Nonredundant Cover
5.4.3 Partitioning of the Nonredundant Cover
5.4.4 Merge of Equivalent Keys
5.4.5 Nonredundant Cover Again and Definition of Tables
5.5 Fourth and Fifth Normal Forms
5.5.1 Multivalued Dependencies
5.5.2 Fourth Normal from
5.5.3 Decomposing Tables to 4NF
5.5.4 Fifth Normal Form
5.6 Summary
Literature Summary
Exercises

Chapter 6 Access Methods
6.1 Sequential Files
6.1.1 Sequential Processing for an Entire File
6.1.2 Sequential Processing for a Single Record
6.1.3 Batch Processing of k Records
6.2 Random Files
6.2.1 Hashing
6.2.2 B-Trees and B+-trees
6.3 Secondary Indices
6.4 Usage Refinement or Denormalization of Logical Databases
6.5 Table Usage Refinement Algorithm
6.6 Join Strategies
6.7 Summary
Literature Summary
Exercises

Chapter 7 An Example of Relational Database Design
7.1 Requirement Specification
7.2 Logical Design
7.3 Physical Design
7.3.1 Schema Refinement Based on Usage
7.3.2 Index Selection Problem
7.4 Summary

Chapter 8 Data Allocation Strategies
8.1 Introduction
8.2 Distributed and Multidatabase Design
8.2.1 Fragmentation
8.2.2 Data Allocation
8.3 The General Data Allocation Problem
8.4 Data Allocation Strategies
8.5 Summary
Literature Summary
Exercises

Chapter 9 Optimal Distributed Data Allocation
9.1 A distributed Database Design Problem
9.1.1 Database Description
9.1.2 Database Transactions
9.1.3 Network and Local Site Specifications
9.2 Global Schema and Fragmentation Design
9.2.1 Analysis of the Transactions
9.2.2 Global Schema Design Decisions
9.2.3 Normalization of the Global Schema
9.2.4 Fragmentation and Nonredundant Allocation
9.3 Redundant Data Allocation Methods
9.3.1 Cost/Benefit Analysis: Basic Performance Statistics
9.3.2 Exhaustive Enumeration Method
9.3.3 All Beneficial Sites Method
9.3.4 Variations of All Beneficial Sites
9.4 Summary
Literature Summary
Exercises

Chapter 10 Database Dependability
10.1 Introduction
10.2 Availability
10.3 Reliability
10.3.1 Example 1: Query Reliability for a Simple Distributed Database
10.3.2 Example 2: Trade-offs Between Performance and Reliability
10.4 Mean Transaction completion Time
10.5 Summary
Literature Summary
Exercises

Appendix A Review of SQL
A.1 SQL Names and Operators
A.2 Data Definition Language (DDL)
A.3 Data Manipulation Language (DML)
A.3.1 SQL Select Command
A.3.2 SQL Update Commands
A.3.3 Referential Integrity
A.3.4 SQL Views
Literature Summary
Exercises

Appendix B Database Performance Tuning
B.1 User-Defined Parameters
B.2 Database Administrator and System Defined Parameters
Literature Summary
References
Solutions to Selected Exercises
Index

What People are Saying About This

From the Publisher

Extensively revised edition of the classic logical database design reference.

Editorial Reviews

"An explicit presentation on Business Intelligence is a major strength of this book. For beginners, there is an elegant presentation on SQL in the appendix and the book is supplemented by a detailed glossary. Exercises, examples and solutions constitute an important part of this book. This book is useful reading for both beginners and advanced users as the contents integrate elements that would address various audiences at different levels." —P. Pichappan, Department of Information Science, Annamalai University, India

From the Publisher