Data Modeling Essentials

Cover

Title Page

Copyright Page

Contents

Preface

Part I: The Basics

Chapter 1. What Is Data Modeling?

1.1 Introduction

1.2 A Data-Centered Perspective

1.3 A Simple Example

1.4 Design, Choice, and Creativity

1.5 Why Is the Data Model Important?

1.6 What Makes a Good Data Model?

1.7 Performance

1.8 Database Design Stages and Deliverables

1.9 Where Do Data Models Fit In? 20

1.10 Who Should Be Involved in Data Modeling?

1.11 Is Data Modeling Still Relevant?

1.12 Alternative Approaches to Data Modeling

1.13 Terminology

1.14 Where to from Here?„An Overview of Part I

1.15 Summary

Chapter 2. Basics of Sound Structure

2.1 Introduction

2.2 An Informal Example of Normalization

2.3 Relational Notation

2.4 A More Complex Example

2.5 Determining Columns

2.6 Repeating Groups and First Normal Form

2.7 Second and Third Normal Forms

2.8 Definitions and a Few Refinements

2.9 Choice, Creativity, and Normalization

2.10 Terminology

2.11 Summary

Chapter 3. The Entity-Relationship Approach

3.1 Introduction

3.2 A Diagrammatic Representation

3.3 The Top-Down Approach: Entity-Relationship Modeling

3.4 Entity Classes

3.5 Relationships

3.6 Attributes

3.7 Myths and Folklore

3.8 Creativity and E-R Modeling

3.9 Summary

Chapter 4. Subtypes and Supertypes

4.1 Introduction

4.2 Different Levels of Generalization

4.3 Rules versus Stability

4.4 Using Subtypes and Supertypes

4.5 Subtypes and Supertypes as Entity Classes

4.6 Diagramming Conventions

4.7 Definitions

4.8 Attributes of Supertypes and Subtypes

4.9 Nonoverlapping and Exhaustive

4.10 Overlapping Subtypes and Roles

4.11 Hierarchy of Subtypes

4.12 Benefits of Using Subtypes and Supertypes

4.13 When Do We Stop Supertyping and Subtyping?

4.14 Generalization of Relationships

4.15 Theoretical Background

4.16 Summary

Chapter 5. Attributes and Columns

5.1 Introduction

5.2 Attribute Definition

5.3 Attribute Disaggregation: One Fact per Attribute

5.4 Types of Attributes

5.5 Attribute Names

5.6 Attribute Generalization

5.7 Summary

Chapter 6. Primary Keys and Identity

6.1 Basic Requirements and Trade-Offs

6.2 Basic Technical Criteria

6.3 Surrogate Keys

6.4 Structured Keys

6.5 Multiple Candidate Keys

6.6 Guidelines for Choosing Keys

6.7 Partially-Null Keys

6.8 Summary

Chapter 7. Extensions and Alternatives

7.1 Introduction

7.2 Extensions to the Basic E-R Approach

7.3 The Chen E-R Approach

7.4 Using UML Object Class Diagrams

7.5 Object Role Modeling

7.6 Summary

Part II: Putting It Together

Chapter 8. Organizing the Data Modeling Task

8.1 Data Modeling in the Real World

8.2 Key Issues in Project Organization

8.3 Roles and Responsibilities

8.4 Partitioning Large Projects

8.5 Maintaining the Model

8.6 Packaging It Up

8.7 Summary

Chapter 9. The Business Requirements

9.1 Purpose of the Requirements Phase

9.2 The Business Case

9.3 Interviews and Workshops

9.4 Riding the Trucks

9.5 Existing Systems and Reverse Engineering

9.6 Process Models

9.7 Object Class Hierarchies

9.8 Summary

Chapter 10. Conceptual Data Modeling

10.1 Designing Real Models

10.2 Learning from Designers in Other Disciplines

10.3 Starting the Modeling

10.4 Patterns and Generic Models

10.5 Bottom-Up Modeling

10.6 Top-Down Modeling

10.7 When the Problem Is Too Complex

10.8 Hierarchies, Networks, and Chains

10.9 One-to-One Relationships

10.10 Developing Entity Class Definitions

10.11 Handling Exceptions

10.12 The Right Attitude

10.13 Evaluating the Model

10.14 Direct Review of Data Model Diagrams

10.15 Comparison with the Process Model

10.16 Testing the Model with Sample Data

10.17 Prototypes

10.18 The Assertions Approach

10.19 Summary

Chapter 11. Logical Database Design

11.1 Introduction

11.2 Overview of the Transformations Required

11.3 Table Specification

11.4 Basic Column Definition

11.5 Primary Key Specification

11.6 Foreign Key Specification

11.7 Table and Column Names

11.8 Logical Data Model Notations

11.9 Summary

Chapter 12. Physical Database Design

12.1 Introduction

12.2 Inputs to Database Design

12.3 Options Available to the Database Designer

12.4 Design Decisions Which Do Not Affect Program Logic

12.5 Crafting Queries to Run Faster

12.6 Logical Schema Decisions

12.7 Views

12.8 Summary

Part III: Advanced Topics

Chapter 13. Advanced Normalization

13.1 Introduction

13.2 Introduction to the Higher Normal Forms

13.3 Boyce-Codd Normal Form

13.4 Fourth Normal Form (4NF) and Fifth Normal Form (5NF)

13.5 Beyond 5NF: Splitting Tables Based on Candidate Keys

13.6 Other Normalization Issues

13.7 Advanced Normalization in Perspective

13.8 Summary

Chapter 14. Modeling Business Rules

14.1 Introduction

14.2 Types of Business Rules

14.3 Discovery and Verification of Business Rules

14.4 Documentation of Business Rules

14.5 Implementing Business Rules

14.6 Rules on Recursive Relationships

14.7 Summary

Chapter 15. Time-Dependent Data

15.1 The Problem

15.2 When Do We Add the Time Dimension?

15.3 Audit Trails and Snapshots

15.4 Sequences and Versions

15.5 Handling Deletions

15.6 Archiving

15.7 Modeling Time-Dependent Relationships

15.8 Date Tables

15.9 Temporal Business Rules

15.10 Changes to the Data Structure

15.11 Putting It into Practice

15.12 Summary

Chapter 16. Modeling for Data Warehouses Data Marts

16.1 Introduction

16.2 Characteristics of Data Warehouses and Data Marts

16.3 Quality Criteria for Warehouse and Mart Models

16.4 The Basic Design Principle

16.5 Modeling for the Data Warehouse

16.6 Modeling for the Data Mart

16.7 Summary

Chapter 17. Enterprise Data Models and Data Management

17.1 Introduction

17.2 Data Management

17.3 Classification of Existing Data

17.4 A Target for Planning

17.5 A Context for Specifying New Databases

17.6 Guidance for Database Design

17.7 Input to Business Planning

17.8 Specification of an Enterprise Database

17.9 Characteristics of Enterprise Data Models

17.10 Developing an Enterprise Data Model

17.11 Choice, Creativity, and Enterprise Data Models

17.12 Summary

Further Reading