Database Modeling and Design

by Teorey; Lightstone; Nadeau; Jagadish

• ISBN13: 9780126853520
• ISBN10: 0126853525
• Edition: 4th
• Format: Paperback
• Copyright: 2005-09-06
• Publisher: Elsevier Science

Summary

Database systems and database design technology have undergone significant evolution in recent years. The relational data model and relational database systems dominate business applications; in turn, they are extended by other technologies like data warehousing, OLAP, and data mining. How do you model and design your database application in consideration of new technology or new business needs? In the extensively revised fourth edition, youll get clear explanations, lots of terrific examples and an illustrative case, and the really practical advice you have come to count on--with design rules that are applicable to any SQL-based system. But youll also get plenty to help you grow from a new database designer to an experienced designer developing industrial-sized systems. + a detailed look at the Unified Modeling Language (UML-2) as well as the entity-relationship (ER) approach for data requirements specification and conceptual modeling--with examples throughout the book in both approaches! + the details and examples of how to use data modeling concepts in logical database design, and the transformation of the conceptual model to the relational model and to SQL syntax; + the fundamentals of database normalization through the fifth normal form; + practical coverage of the major issues in business intelligence--data warehousing, OLAP for decision support systems, and data mining; + examples for how to use the most popular CASE tools to handle complex data modeling problems. + Exercises that test understanding of all material, plus solutions for many exercises.

Author Biography

Toby Teorey is a professor in the Electrical Engineering and Computer Science Department at the University of Michigan, Ann Arbor Sam Lightstone is the cofounder and leader of DB2's autonomic computing R&D effort Tom Nadeau is the founder of Aladdin Software

Table of Contents

Preface	xv
Introduction	1	(12)
Data and Database Management	2	(1)
The Database Life Cycle	3	(5)
Conceptual Data Modeling	8	(3)
Summary	11	(1)
Literature Summary	11	(2)
The Entity-Relationship Model	13	(20)
Fundamental ER Constructs	13	(10)
Basic Objects: Entities, Relationships, Attributes	13	(3)
Degree of a Relationship	16	(2)
Connectivity of a Relationship	18	(1)
Attributes of a Relationship	19	(1)
Existence of an Entity in a Relationship	19	(1)
Alternative Conceptual Data Modeling Notations	20	(3)
Advanced ER Constructs	23	(7)
Generalization: Supertypes and Subtypes	23	(2)
Aggregation	25	(1)
Ternary Relationships	25	(3)
General n-ary Relationships	28	(1)
Exclusion Constraint	29	(1)
Referential Integrity	30	(1)
Summary	30	(1)
Literature Summary	31	(2)
The Unified Modeling Language (UML)	33	(20)
Class Diagrams	34	(12)
Basic Class Diagram Notation	35	(2)
Class Diagrams for Database Design	37	(6)
Example from the Music Industry	43	(3)
Activity Diagrams	46	(4)
Activity Diagram Notation Description	46	(2)
Activity Diagrams for Workflow	48	(2)
Rules of Thumb for UML Usage	50	(1)
Summary	51	(1)
Literature Summary	51	(2)
Requirements Analysis and Conceptual Data Modeling	53	(30)
Introduction	53	(1)
Requirements Analysis	54	(1)
Conceptual Data Modeling	55	(11)
Classify Entities and Attributes	56	(1)
Identify the Generalization Hierarchies	57	(1)
Define Relationships	58	(3)
Example of Data Modeling: Company Personnel and Project Database	61	(5)
View Integration	66	(8)
Preintegration Analysis	67	(1)
Comparison of Schemas	68	(1)
Conformation of Schemas	68	(1)
Merging and Restructuring of Schemas	69	(1)
Example of View Integration	69	(5)
Entity Clustering for ER Models	74	(7)
Clustering Concepts	75	(1)
Grouping Operations	76	(2)
Clustering Technique	78	(3)
Summary	81	(1)
Literature Summary	82	(1)
Transforming the Conceptual Data Model to SQL	83	(24)
Transformation Rules and SQL Constructs	83	(20)
Binary Relationships	85	(5)
Binary Recursive Relationships	90	(2)
Ternary and n-ary Relationships	92	(9)
Generalization and Aggregation	101	(2)
Multiple Relationships	103	(1)
Weak Entities	103	(1)
Transformation Steps	103	(3)
Entity Transformation	104	(1)
Many-to-Many Binary Relationship Transformation	104	(1)
Ternary Relationship Transformation	105	(1)
Example of ER-to-SQL Transformation	105	(1)
Summary	106	(1)
Literature Summary	106	(1)
Normalization	107	(32)
Fundamentals of Normalization	107	(9)
First Normal Form	109	(1)
Superkeys, Candidate Keys, and Primary Keys	109	(2)
Second Normal Form	111	(2)
Third Normal Form	113	(2)
Boyce-Codd Normal Form	115	(1)
The Design of Normalized Tables: A Simple Example	116	(2)
Normalization of Candidate Tables Derived from ER Diagrams	118	(4)
Determining the Minimum Set of 3NF Tables	122	(5)
Fourth and Fifth Normal Forms	127	(10)
Multivalued Dependencies	127	(2)
Fourth Normal Form	129	(3)
Decomposing Tables to 4NF	132	(1)
Fifth Normal Form	133	(4)
Summary	137	(1)
Literature Summary	138	(1)
An Example of Logical Database Design	139	(8)
Requirements Specification	139	(2)
Design Problems	140	(1)
Logical Design	141	(4)
Summary	145	(2)
Business Intelligence	147	(40)
Data Warehousing	148	(18)
Overview of Data Warehousing	148	(4)
Logical Design	152	(14)
Online Analytical Processing (OLAP)	166	(12)
The Exponential Explosion of Views	167	(2)
Overview of OLAP	169	(1)
View Size Estimation	170	(3)
Selection of Materialized Views	173	(3)
View Maintenance	176	(1)
Query Optimization	177	(1)
Data Mining	178	(7)
Forecasting	179	(2)
Text Mining	181	(4)
Summary	185	(1)
Literature Summary	186	(1)
CASE Tools for Logical Database Design	187	(26)
Introduction to the CASE Tools	188	(3)
Key Capabilities to Watch For	191	(1)
The Basics	192	(4)
Generating a Database from a Design	196	(3)
Database Support	199	(1)
Collaborative Support	200	(1)
Distributed Development	201	(1)
Application Life Cycle Tooling Integration	202	(2)
Design Compliance Checking	204	(2)
Reporting	206	(1)
Modeling a Data Warehouse	207	(2)
Semi-Structured Data, XML	209	(2)
Summary	211	(1)
Literature Summary	211	(2)
Appendix: The Basics of SQL	213	(18)
Glossary	231	(8)
References	239	(10)
Exercises	249	(10)
Solutions to Selected Exercises	259	(4)
About the Authors	263	(2)
Index	265