E-raamat: Database Modeling and Design: Logical Design

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

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 Teoreys 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 IBMs DB2 product development team. His work includes numerous topics in autonomic computing and relational database management systems. He is cofounder and leader of DB2s 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.