Modeling

Data
A

Beginners Guide

Andy Oppel
TECHNISCHE

INFORMATIONSBtBUOTHEK
UNIVERSITATSBiBLIOTHEK
HANNOVER

Mc
Gravu
Hill
New York
Lisbon

Chicago

London

San Francisco

Madrid

Mexico

Milan

New Delhi San Juan

Seoul

Singapore Sydney

City

Toronto

Contents
ACKNOWLEDGMENTS

xvii

INTRODUCTION

PART I

Data

xix

Modeling Concepts

1 Introduction to Data Modeling

Data-Centric Design

Anatomy of a Data Model

Layers of Data Abstraction
Types of Data Models
Importance of Data Modeling

6
6
8
12

Documentation of Business Rules

12

Visualization

12

Illustration of Alternatives

13

Foundation for Future

13

Expansion

Promotion of Common and Standard Structures

13

Provisions for Automation

13

Measures of

Good Data Model

14

Enforcement of Business Rules

14

Flexible and

15

Adaptable

ix

Dafp Modeling: A Beginner's .Guide

Easily Understood
Balanced Perspective
Promotion of Data Reusability
Data Integration
How Data Models Fit Into

Application Development

Process-Oriented Methodologies
Data-Oriented Methodologies

Hybrid Methodologies
Object-Oriented Methodologies
Prototyping Methodologies
Agile Methodologies
Data Modeling Participants
Try This 1 -1: Refining a Conceptual Model
Chapter 1 Self Test
2 Relational Model

Conceptual

and

Components

Logical Model Components

15
15
16
16
16
16
17
17
17

17
18
18
19
20
23
24

Entities

25

Attributes

30

Relationships

32

Business Rules

39

Physical Model Components

Tables

39

Columns and Data

Types

Constraints

Integrity

41
43

Constraints

Views

46
48

Try This 2-1: Conceptual Model Modification

Chapter 2 Self Test
3 Data and Process
Data Model

39

Modeling

Diagramming Alternatives

48
50

S3
54

ERD Formats

54

Representing Supertypes and Subtypes

Guidelines for Drawing ERDs

60

Process Models

63

63

The Flowchart

64

The Function

66

Hierarchy Diagram

The Swim Lane

The Data Flow

Diagram

Diagram

67
68

Contents

Unified Modeling Language (UML)

UML Class Diagrams
Other UML

Relating
Try

Drawing

72

73

Conceptual

Model with Nested

75

Subtypes

3 Self Test

76

Organizing Database Project Work

The Traditional Life

81
82

Cycle

Planning
Requirements Gathering
Conceptual Design
Logical Design
Physical Design

84

Construction

90

Implementation and Rollout

90

Ongoing Support

91

85
88
89
89

Nontraditional Life Cycles

92

Prototyping
Rapid Application Development
The Project Triangle
Try This 4-1: Project Database Management Tasks
Chapter 4 Self Test

92

PART II
5

70

Diagrams

Entities and Processes

This 3-1:

Chapter

70

Data

Modeling

93
93
94
96

Details

Conceptual Data Modeling

101

The Conceptual

102

Process

Modeling
Preparation

102

Solution Design

105

What Differentiates Conceptual Modeling from Logical Modeling?

105
106

Creating the Model

Generic Models and Patterns

106

Subtypes

109

Roles

vs.

Dealing

with Hierarchies,

Bottom

Down

Up

vs.

Top

Networks, and Linked Lists

113

Modeling

118
122

Areas

Subject
Evaluating the

What Makes
This 5-1:

Try
Chapter 5

123

Model
a

Conceptual

Self Test

Model?

123

Model for International Addresses

124

Good

Conceptual

125

xi

xii

Data

Modeling: A Beginner's.Guide

Logical Database Design Using Normalization

127

The Need for Normalization

130

Insert

Anomaly

130

Delete Anomaly

130

Update Anomaly
Applying the Normalization Process
Choosing a Primary Key
First Normal Form: Eliminating Repeating Data
Second Normal Form: Eliminating Partial Dependencies

131
133
135
137

Third Normal Form: Eliminating Transitive Dependencies

Denormalization

139

Practice Problems

142

Try This

6-1: UTLA Academic

Tracking

The User Views

Try This 6-2: Computer

Chapter 6

142

143
143

Books

Company

The User Views

131

Self Test

146

146
148

Beyond Third Normal Form

151

Advanced Normalization

152

Boyce-Codd

152

Normal Form

Fourth Normal Form

155

Fifth Normal Form

157

Domain-Key
(DKNF)
Resolving Supertypes and Subtypes
Generalizing Attributes

158

Alternatives for Reference Data

166

Common Code Structures

166

Crosswalk Tables

167

Normal Form

Language

Translation Tables

158
162

168

Try This 7-1: Complex Logical Data Model

169

Chapter 7

171

Self Test

8 Physical Database Design

The

Process

Physical Design
Designing Tables
Try This 8-1: Drawing a Physical Data Model
Implementing Supertypes and Subtypes
Naming Conventions
Integrating Business Rules and Data Integrity

173
174
176
182
183

186
189

NOT NULL Constraints

191

Primary Key Constraints

191

Contents

Referential

(Foreign Key)
Unique Constraints

192

Check Constraints

193

Data

193

Types, Precision, and Scale

Triggers
Adding Indexes for Performance
Designing Views
Try This 8-2: Mapping a Logical Model
Chapter 8 Self Test
PART III

191

Constraints

194
194
196
to a

Physical Database Design

199

Design Alternatives

9 Alternatives for Incorporating Business Rules

The

Anatomy of a Business Rule

The Origin of Business Rules
Implementing Business Rules in Data Models
Implementing Terms
Implementing Facts
Implementing Derivations
Limitations

Business Rules in Data Models

Implementing
Implementing Constraints
on

205
206
207
207
207
209
210

211
211

Constraints That Cannot Be Shown in

Entity Relationship Models

Functional Classification of Business Rules

212
214

Definitional Rules

214

Data Validation Rules

214

Data Derivation Rules

215

Cardinality

Rules

Referential

Integrity

215
Rules

215

Process Rules

Try

198

This 9-1:

215

Modeling

Business Rules

216

Chapter 9 Self Test

10 Alternatives for

Temporal

217

Handling Temporal Data

Data Structures

220

When Does Time Matter?

220

Structures

Adding History
Processing Rules for History
Handling Deletions
to Data

This 10-1:

Try
Chapter

Temporal

228
230
231

Data

Adding History

10 Self Test

221
228

Calendar Data Structures

Business Rules for

219

to Data

Structures

233
235

xiii

xiv

Modeling:. A Beginner's .Guide.

Data

11

Modeling for Analytical Databases

237

Data Warehouses

239

OLTP

240

Systems Compared with Data Warehouse Systems

Data Warehouse Architecture

245

Modeling Analytical Data Structures

247

OLAP Database

Requirements
Data Warehouse Modeling

247

Data Mart

250

Modeling
Loading Data into Analytical Databases

12

240

Data Marts

248
257

The Extract Process

258

The Transform Process

258

The Load Process

259

TryThis 11-1: Design Star SchemaFact andDimension Tables

Chapter 11 Self Test

259

Enterprise

265

Data

Modeling

Enterprise Data Management

The Case for Data Management
Alternatives to Centralized Data Management
The Enterprise Data Model
What Is

266
266
267
268

Enterprise Data Model?

The Anatomy of an Enterprise Data Model
Building an Enterprise Data Model
TryThis 12-1: Enterprise Conceptual Model Development
Chapter 12 Self Test
PART IV

261

an

268
269

272
274
274

Appendixes

A Answers

to Self Tests

B Solutions to

TryThis
Try This
Try This
Try This
Try This
Try This
Try This
Try This
Try This

Try This

279
Exercises

1-1:

313

Refining a Conceptual Model

2-1: Conceptual Model Modification
3-1: Drawing a Conceptual Model with Nested
Subtypes
4-1: The Database Life Cycle
5-1: Conceptual Model for International Addresses

314

6-1: UTLA Academic

319

6-2:
7-1:
8-1:

Tracking

Computer Books Company

Complex Logical Data Model
Drawing a Physical Data Model

315
316
317
318

322
324
325

Content?

Try This
Try This
Try This
Try This
Try This

Index

8-2:

326

9-1:

Mapping a Logical Model to a Physical Database Design

Modeling Business Rules
10-1: Adding History to Data Structures
11-1: Design Star Schema Fact and Dimension Tables
12-1: Enterprise Conceptual Model Development

326
327
328
329

331

xv