Data Modelling

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

Data Modelling

Uploaded by

Copyright:

Available Formats

Data Modelling

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Data Modelling

Uploaded by

Copyright:

Available Formats

Modeling

New Delhi San Juan

1 Introduction to Data Modeling

Anatomy of a Data Model

Documentation of Business Rules

Foundation for Future

Promotion of Common and Standard Structures

Provisions for Automation

Good Data Model

Enforcement of Business Rules

Dafp Modeling: A Beginner's .Guide

Logical Model Components

Physical Model Components

Columns and Data

Try This 2-1: Conceptual Model Modification

Representing Supertypes and Subtypes

The Swim Lane

Unified Modeling Language (UML)

Model with Nested

Organizing Database Project Work

Implementation and Rollout

Nontraditional Life Cycles

Entities and Processes

Conceptual Data Modeling

What Differentiates Conceptual Modeling from Logical Modeling?

Creating the Model

Networks, and Linked Lists

Model for International Addresses

Logical Database Design Using Normalization

The Need for Normalization

Third Normal Form: Eliminating Transitive Dependencies

6-1: UTLA Academic

The User Views

Try This 6-2: Computer

The User Views

Beyond Third Normal Form

Fourth Normal Form

Fifth Normal Form

Alternatives for Reference Data

Common Code Structures

Try This 7-1: Complex Logical Data Model

8 Physical Database Design

NOT NULL Constraints

Primary Key Constraints

Types, Precision, and Scale

Physical Database Design

9 Alternatives for Incorporating Business Rules

Anatomy of a Business Rule

Business Rules in Data Models

Constraints That Cannot Be Shown in

Entity Relationship Models

Functional Classification of Business Rules

Data Validation Rules

Data Derivation Rules

Chapter 9 Self Test

Handling Temporal Data

When Does Time Matter?

Calendar Data Structures

Modeling:. A Beginner's .Guide.