Database Modeling and Design

Logical Design


  • Toby Teorey, University of Michigan, Ann Arbor, USA
  • Sam Lightstone, IBM, Toronto, Canada
  • Tom Nadeau, Ubiquiti Inc., Ann Arbor, MI
  • H.V. Jagadish, Univ of Mich, Ann Arbor (EE/CS dept)

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, you’ll 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 you’ll also get plenty to help you grow from a new database designer to an experienced designer developing industrial-sized systems.
View full description


Professional data modelers and database design professionals, including database application designers, database admininstrators (DBAs), and new/novice data management professionals, including those working on object oriented database design; students in second courses in database focusing on design.


Book information

  • Published: September 2005
  • ISBN: 978-0-12-685352-0


"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

Table of Contents

Chapter 1 Introduction1.1 Data and Database Management1.2 The Database Life Cycle1.2 Conceptual Data Modeling 1.4 SummaryLiterature SummaryChapter 2 The Entity-Relationship Model2.1 Fundamental ER Constructs2.1.1 Basic Objects: Entities, Relationships, Attributes2.1.2 Degree of a Relationship2.1.3 Connectivity of a Relationship2.1.4 Attributes of a Relationship2.1.5 Existence of an Entity in a Relationship2.1.6 Alternative Conceptual Data Modeling Notations2.2 Advanced ER Constructs2.2.1 Generalization: Supertypes and Subtypes2.2.2 Aggregation2.2.3 Ternary Relationships2.2.4 General n-ary Relationships2.2.5 Exclusion constraint2.2.6 Referential Integrity2.3 SummaryLiterature SummaryChapter 3 Unified Modeling Language3.1 Class Diagrams 3.1.1 Class Diagram Notation Description 3.1.2 Class Diagrams for Software Design 3.1.3 Class Diagrams for Database Design3.2 Activity Diagrams 3.2.1 Activity Diagram Notation Description 3.2.2 Activity Diagrams for Software Design 3.2.3 Activity Diagrams for Workflow3.3 Rules of Thumb for UML Usage3.4 Summary Literature Summary Chapter 4 Requirements Analysis and Conceptual Data Modeling4.1 Introduction4.2 Requirements Analysis4.3 Conceptual Data Modeling 4.3.1 Classify Entities and Attributes4.3.2 Identify the Generalization Hierarchies4.3.3 Define Relationships4.3.4 Example of Data Modeling: Company Project Database4.4 View Integration4.4.1 Pre-integration Analysis4.4.2 Comparison of Schemas4.4.3 Conformation of Schemas4.4.4 Merging and Restructuring of Schemas4.4.5 Example of View Integration4.5 Entity Clustering for ER Models4.5.1 Clustering Concepts4.5.2 Grouping Operations4.5.3 Clustering Technique4.6 Summary Literature SummaryChapter 5 Transforming the Conceptual Data Model to SQL5.1 Transformation Rules and SQL Constructs5.1.1 Binary Relationships5.1.2 Binary Recursive Relationships5.1.3 Ternary and n-ary Relationships5.1.4 Generalization and Aggregation5.1.5 Multiple Relationships5.1.6 Weak Entities5.2 Transformation Steps5.2.1 Entity Transformation5.2.2 Many-to-Many Binary Relationship Transformation5.2.3 Ternary Relationship Transformation5.2.4 Example of ER-to-SQL Transformation5.3 Summary Literature SummaryChapter 6 Normalization6.1 Fundamentals of Normalization6.1.1 First Normal Form6.1.2 Superkeys, Candidate Keys, and Primary Keys6.1.3 Second Normal Form6.1.4 Third Normal Form6.1.5 Boyce-Codd Normal Form6.2 The Design of Normalized Tables: Simple Example6.3 Normalization of Candidate Tables Derived from ER Diagrams6.4 Determining the Minimum Set of 3NF Tables6.4.1 Elimination of Extraneous Attributes6.4.2 Search for a Nonredundant Cover6.4.3 Partitioning of the Nonredundant Cover6.4.4 Merge of Equivalent Keys6.4.5 Definition of Tables6.5 Fourth and Fifth Normal Forms6.5.1 Multivalued Dependencies6.5.2 Fourth Normal Form6.5.3 Decomposing Tables to 4NF6.5.4 Fifth Normal Form6.6 Summary Literature SummaryChapter 7 An Example of Logical Database Design7.1 Requirements Specification7.2 Logical Design7.3 SummaryChapter 8 Business Intelligence8.1 Data Warehousing 8.1.1 Overview of Data Warehousing 8.1.2 Logical Design8.2 On-Line Analytical Processing (OLAP) 8.2.1 The Exponential Explosion of Views 8.2.2 Overview of OLAP 8.2.3 View Size Estimation 8.2.4 Selection of Material Views 8.2.5 View Maintenance 8.2.6 Query Optimization8.3 Data Mining 8.3.1 Forecasting 8.3.2 Text Mining8.4 Summary Literature SummaryChapter 9 CASE Tools for Logical Database Design 9.1 Introduction to Software Tools9.2 The Key Capabilities to Watch For9.3 The Basics9.4 Generating a Database From a Design9.5 Database Support9.6 Collaborative Support9.7 Distributed Development9.8 Application Lifecycle Tooling Integration9.9 Design Compliance Checking9.10 Reporting9.11 Semi-Structured Data, XML9.12 Summary Literature SummaryAppendix The Basics of SQLA.1 SQL Names and OperatorsA.2 Data Definition Language (DDL)A.3 Data Manipulation Language (DML)A.3.1 SQL Select CommandA.3.2 SQL Update CommandsA.3.3 Referential IntegrityA.3.4 SQL Views ReferencesExercises for Logical Design Solutions to Selected Exercises Glossary