Data Modeling Essentials : A Comprehensive Guide to Data Analysis, Design, and Innovation

by Simsion, Graeme C.

• ISBN13: 9781576108727
• ISBN10: 1576108724
• Edition: 2nd
• Format: Paperback
• Copyright: 2000-12-01
• Publisher: Coriolis Group

Author Biography

Graeme C. Simsion is Principal of Simsion Bowles and Associates, a division of Tier Technologies, Inc., in Melbourne, Australia, and is a regular speaker and publisher on the subjects of data modeling and data management. Graham C. Witt is a Senior Consultant with Simsion Bowles and Associates, specializing in data modeling, database design, and data management. He is also an experienced teacher of data modeling and publishes regularly.

Table of Contents

Part I: Overview of Data Modeling	1	(194)
What is Data Modeling?	3	(32)
Introduction	3	(1)
A Data-Centered Perspective	3	(1)
A Simple Example	4	(2)
Design, Choice, and Creativity	6	(2)
Why Is the Data Model so Important?	8	(3)
Leverage	8	(2)
Conciseness	10	(1)
Data Quality	10	(1)
Evaluation Criteria	11	(4)
Completeness	11	(1)
Nonredundancy	11	(1)
Enforcement of Business Rules	11	(1)
Data Reusability	12	(1)
Stability and Flexibility	12	(2)
Simplicity and Elegance	14	(1)
Communication Effectiveness	15	(1)
Conflicting Objectives	15	(1)
Performance and Implementation Issues	16	(6)
Logical and Physical Database Designs	16	(4)
Modifying the Data Model to Improve Performance	20	(1)
Roles of Data Modeler and Physical Database Designer	20	(1)
When Performance Is Paramount	21	(1)
Where Does the Data Model Fit In?	22	(4)
Function-Driven Approaches	22	(1)
Data-Driven Approaches	23	(1)
Data-Driven versus Function-Driven	24	(1)
Object-Oriented Approaches	24	(1)
Prototyping Approaches	25	(1)
Who Should Be Involved in Data Modeling?	26	(1)
Costs and Benefits of Data Modeling	27	(1)
Is Data Modeling Still Relevant?	28	(2)
Terminology - ``Data Model''	30	(1)
Alternative Approaches to Data Modeling	31	(1)
Where to from Here?	31	(2)
Summary	33	(2)
Basic Normalization	35	(32)
An Informal Example of Normalization	35	(4)
Relational Notation	39	(1)
A More Complex Example	40	(2)
Determining Columns	42	(3)
One Fact per Column	42	(1)
Hidden Data	43	(1)
Derivable Data	43	(1)
Determining the Key	44	(1)
Repeating Groups and First Normal Form	45	(5)
Limit on Maximum Number of Occurrences	45	(1)
Data Reusability and Program Complexity	46	(1)
Recognizing Repeating Groups	46	(1)
Removing Repeating Groups	47	(1)
Determining the Key of the New Table	48	(1)
First Normal Form	49	(1)
Second and Third Normal Forms	50	(7)
Problems with Tables in First Normal Form	50	(1)
Eliminating Redundancy	50	(1)
Determinants	51	(3)
Third Normal Form	54	(3)
Definitions and a Few Refinements	57	(5)
Determinants and Functional Dependency	57	(1)
Primary Keys	57	(1)
Candidate Keys	57	(1)
A Formal Definition of Third Normal Form	58	(1)
Foreign Keys	59	(1)
Referential Integrity	60	(1)
Denormalization and Unnormalization	60	(2)
Choice, Creativity, and Normalization	62	(4)
Terminology	66	(1)
Summary	66	(1)
The Entity-Relationship Approach	67	(44)
Introduction	67	(1)
A Diagrammatic Representation	68	(8)
The Basic Symbols	69	(1)
Diagrammatic Representation of Foreign Keys	70	(1)
Interpreting the Diagram	70	(1)
Optionality	71	(1)
Verifying the Model	72	(2)
Redundant Lines	74	(2)
The Top-Down Approach: Entity-Relationship Modeling	76	(3)
Developing the Diagram Top Down	77	(1)
Terminology	78	(1)
Entities	79	(7)
Entity Diagramming Convention	80	(1)
Entity Naming	81	(1)
Entity Definitions	82	(4)
Relationships	86	(18)
Relationships Diagramming Conventions	86	(5)
Many-to-Many Relationships	91	(6)
One-to-One Relationships	97	(1)
Self-Referencing Relationships	98	(3)
Relationships of Higher Degree	101	(1)
Relationship Names	102	(2)
Attributes	104	(1)
Using UML Object Class Diagrams	105	(1)
Creativity and E-R Modeling	106	(3)
Summary	109	(2)
Subtypes and Supertypes	111	(38)
Introduction	111	(1)
Different Levels of Generalization	111	(3)
Rules versus Stability	114	(2)
Representation of Subtypes and Supertypes	116	(2)
Subtypes and Supertypes as Entities	118	(1)
Diagramming Conventions	119	(1)
Definitions	120	(1)
Attributes and Primary Keys of Supertypes and Subtypes	121	(1)
Conversion to a Relational Model	122	(7)
Implementation at a Single Level of Generalization	122	(2)
Implementation at Multiple Levels of Generalization	124	(1)
Construction of Views	125	(1)
What Happens to Relationships?	126	(3)
Nonoverlapping and Exhaustive	129	(2)
Overlapping Subtypes and Roles	131	(2)
Ignoring Real World Overlaps	131	(1)
Modeling Only the Supertype	132	(1)
Modeling the Roles as Participation in Relationships	132	(1)
Using Role Entities and One-to-One Relationships	133	(4)
Multiple Partitions	134	(3)
Hierarchy of Subtypes	137	(1)
Benefits of Using Subtypes and Supertypes	137	(6)
Creativity	137	(1)
Presentation - Level of Detail	138	(2)
Communication	140	(1)
Classifying Common Patterns	141	(1)
Divide and Conquer	142	(1)
When Do We Stop Supertyping and Subtyping?	143	(4)
Differences in Keys	143	(1)
Different Attribute Groups	144	(1)
Different Relationships	144	(1)
Different Processes	145	(1)
Migration from One Subtype to Another	145	(1)
Communication	145	(1)
Capturing Meaning and Rules	146	(1)
Summary	147	(1)
Theoretical Background	147	(1)
Summary	148	(1)
Putting It All Together	149	(26)
Designing Real Models	149	(2)
Getting Started - Identifying Entities	151	(1)
Patterns and Generic Models	152	(9)
Using Patterns	152	(2)
Using a Generic Model	154	(1)
Adapting Generic Models from Other Applications	155	(5)
Developing a Generic Model	160	(1)
When There Isn't a Generic Model	161	(1)
Bottom-Up Modeling	161	(5)
Top-Down Modeling	166	(1)
When the Problem is Too Complex	167	(2)
Developing Entity Definitions	169	(1)
Handling Exceptions	170	(1)
The Right Attitude	171	(3)
Being Aware	171	(1)
Being Creative	172	(1)
Analyzing or Designing	172	(1)
Being Brave	173	(1)
Having a Plan	173	(1)
Being Understanding and Understood	174	(1)
Summary	174	(1)
Organizing the Data Modeling Task	175	(20)
Data Modeling in the Real World	175	(1)
Roles and Responsibilities	176	(2)
Sequence	178	(2)
Inputs to the Modeling Task	180	(9)
Interviews and Workshops	180	(4)
Existing Systems and Reverse Engineering	184	(2)
Function Models	186	(3)
Testing the Model	189	(3)
Comparison with the Function Model	189	(1)
Review with the Users	190	(1)
Sample Data	191	(1)
Prototypes	191	(1)
Packaging It Up	192	(1)
Maintaining the Model	193	(1)
Summary	194	(1)
Part II: Advanced Data Modeling	195	(242)
More About Relationships and Foreign Keys	197	(40)
Introduction	197	(1)
Translating Relationships into Foreign Keys	198	(9)
The Basic Rule	198	(1)
Overlapping Foreign Keys	199	(2)
Split Foreign Keys	201	(2)
Derivable Foreign Keys	203	(4)
Optional Relationships	207	(1)
Transferability	208	(4)
The Concept of Transferability	209	(1)
The Importance of Transferability	210	(1)
Documenting Transferability	210	(2)
One-to-One Relationships	212	(7)
One-to-One Relationships and Creativity	212	(2)
When to Use One-to-One Relationships	214	(4)
Implementing One-to-One Relationships	218	(1)
Self-Referencing Relationships	219	(8)
Hierarchies (One-to-Many Relationships)	219	(3)
Networks (Many-to-Many Relationships)	222	(3)
Chains (One-to-One Relationships)	225	(2)
Relationships Involving Three or More Entities	227	(1)
Generalization of Relationships	227	(6)
Generalizing Multiple One-to-Many Relationships to a Single Many-to-Many Relationship	231	(1)
Generalizing Multiple One-to-Many Relationships to a Single One-to-Many Relationship	232	(1)
Myths and Folklore	233	(2)
Entities without Relationships	233	(1)
Allowed Combinations of Cardinality and Optionality	234	(1)
Summary	235	(2)
Advanced Normalization	237	(30)
Introduction	237	(1)
Introduction to the Higher Normal Forms	238	(1)
Common Misconceptions	238	(1)
Boyce-Codd Normal Form	239	(6)
Example of Structure in 3NF but Not in BCNF	239	(3)
Definition of BCNF	242	(1)
Enforcement of Rules Versus BCNF	243	(1)
A Note on Domain Key Normal Form	244	(1)
Fourth Normal Form (4NF) and Fifth Normal Form (5NF)	245	(11)
Data in BCNF but Not in 4NF	245	(3)
Fifth Normal Form (5NF)	248	(4)
Recognizing 4NF and 5NF Situations	252	(3)
Checking for 4NF and 5NF with the Business Specialist	255	(1)
Further Reading on Higher Normal Forms	255	(1)
Beyond 5NF - Splitting Tables Based on Candidate Keys	256	(1)
Other Normalization Issues	257	(8)
Normalization and Redundancy	257	(2)
Look-Up Tables Produced by Normalization	259	(4)
Selecting the Primary Key after Removing Repeating Groups	263	(1)
Sequence of Normalization and Cross-Table Anomalies	264	(1)
Advanced Normalization in Perspective	265	(1)
Summary	266	(1)
Primary Keys and Identity	267	(24)
Basic Requirements and Trade-Offs	267	(2)
Applicability	269	(2)
Special Cases	269	(1)
Data Unavailable at Time of Entry	270	(1)
Broadening of Scope	270	(1)
Uniqueness	271	(1)
Minimality	272	(1)
Stability	273	(2)
Structured Keys and Weak Entities	275	(7)
Problems with Structured Keys	275	(1)
Using Structured Keys to Control Transferability	276	(1)
Weak Entities	277	(2)
Programming and Structured Keys	279	(1)
Performance Issues with Structured Keys	280	(2)
Surrogate Keys	282	(4)
When to Use Surrogate Keys	282	(1)
Should Surrogate Keys Be Visible?	283	(1)
Surrogate Keys and Subtypes	284	(1)
Singularity	285	(1)
Null and Partially-Null Keys	286	(2)
Multiple Candidate Keys	288	(1)
Choosing a Primary Key	288	(1)
Normalization Issues	288	(1)
Summary	289	(2)
Attributes	291	(36)
Introduction	291	(1)
Attribute Definition	292	(1)
Attribute Disaggregation - One Fact per Attribute	293	(6)
Simple Aggregation	295	(1)
Complex Codes	296	(1)
Meaningful Ranges	297	(1)
Inappropriate Generalization	298	(1)
Is a Foreign Key an Attribute?	299	(1)
Types of Attributes	300	(10)
The Attribute Taxonomy in Detail	301	(5)
Attribute Datatype and Length Requirements	306	(3)
Conversion between External and Internal Representations	309	(1)
Attribute Names	310	(4)
Objectives of Standardizing Attribute Names	310	(1)
Some Guidelines for Attribute Naming	311	(3)
Attribute Generalization	314	(11)
Options and Trade-Offs	314	(1)
Attribute Generalization Resulting From Entity Generalization	315	(2)
Attribute Generalization within Entities	317	(6)
Limits to Attribute Generalization	323	(2)
Summary	325	(2)
Time-Dependent Data	327	(24)
The Problem	327	(1)
Audit Trails and Snapshots	328	(10)
The Basic Audit Trail Approach	329	(5)
Handling Nonnumeric Data	334	(1)
The Basic Snapshot Approach	335	(3)
Sequences and Versions	338	(1)
Handling Deletions	338	(1)
Archiving	339	(1)
Modeling Time-Dependent Relationships	340	(7)
One-to-Many Relationships	340	(5)
Many-to-Many Relationships	345	(2)
Self-Referencing Relationships	347	(1)
Date Entities	347	(2)
Changes to the Data Structure	347	(2)
Putting it into Practice	349	(1)
Summary	350	(1)
Modeling Business Rules	351	(38)
Introduction	351	(2)
Types of Business Rules	353	(3)
Data Rules	353	(2)
Process Rules	355	(1)
What Rules are Relevant to the Data Modeler?	355	(1)
Discovery and Verification of Business Rules	356	(2)
Cardinality Rules	356	(1)
Data Validation Rules	357	(1)
Data Derivation Rules	357	(1)
Documentation of Business Rules	358	(4)
Documentation in an Entity-Relationship Diagram	358	(1)
Documentation in a Relational Model	359	(1)
Documenting Other Rules	360	(2)
Use of Subtypes to Document Rules	362	(1)
Implementation of Business Rules	362	(26)
Implementing Particular Rules	366	(5)
Implementation Options: a Detailed Example	371	(4)
Implementing Mandatory Relationships	375	(2)
Referential Integrity	377	(1)
Restricting an Attribute to a Discrete Set of Values	378	(4)
Rules Involving Multiple Attributes	382	(1)
Recording Data that Supports Rules	383	(3)
Recording Information about Breaking the Rules	386	(1)
Enforcement of Rules through Primary Key Selection	387	(1)
Summary	388	(1)
Modeling for Data Warehouses and Data Marts	389	(26)
Introduction	389	(3)
Characteristics of Data Warehouses and Data Marts	392	(3)
Data Integration - Working with Existing Databases	392	(1)
Loads Rather than Updates	393	(1)
Less Predictable Database ``Hits''	393	(1)
Complex Queries - Simple Interface	394	(1)
Summarization	394	(1)
History	394	(1)
Quality Criteria for Warehouse and Mart Models	395	(3)
Completeness	395	(1)
Nonredundancy	396	(1)
Enforcement of Business Rules	396	(1)
Data Reusability	397	(1)
Stability and Flexibility	397	(1)
Simplicity and Elegance	397	(1)
Communication Effectiveness	398	(1)
Performance	398	(1)
The Basic Design Principle	398	(1)
Modeling for the Data Warehouse	399	(4)
An Initial Model	400	(1)
Understanding Existing Data	400	(1)
Determining Requirements	401	(1)
Determining Sources and Dealing with Differences	401	(1)
Shaping Data for Data Marts	402	(1)
Modeling for the Data Mart	403	(11)
The Basic Challenge	403	(1)
Multidimensional Databases, Stars and Snowflakes	404	(7)
Modeling Time-Dependent Data	411	(3)
Summary	414	(1)
Corporate Data Modeling	415	(22)
Introduction	415	(1)
Data Management	416	(3)
Problems of Data Mismanagement	416	(2)
Managing Data as a Shared Resource	418	(1)
Classification of Existing Data	419	(1)
A Target for Planning	420	(2)
A Context for Specifying New Databases	422	(2)
Determining Scope and Interfaces	422	(1)
Incorporating the Corporate Data Model in the Development Lifecycle	422	(2)
Guidance for Database Design	424	(1)
Input to Business Planning	425	(1)
Specification of a Corporate Database	426	(2)
Characteristics of Corporate Data Models	428	(1)
Developing a Corporate Data Model	429	(4)
The Development Cycle	429	(1)
Partitioning the Task	430	(1)
Inputs to the Task	431	(1)
Expertise Requirements	432	(1)
External Standards	433	(1)
Choice, Creativity, and Corporate Data Models	433	(2)
Summary	435	(2)
Appendices	437	(14)
Appendix A: Check List for Data Model Review	439	(6)
The Context of the Model	439	(1)
The Model as a Whole	440	(1)
Entities	441	(1)
Relationships	442	(1)
Attributes	443	(1)
Keys and Normalization	443	(1)
Business Rules	444	(1)
Appendix B: An Approach to Presenting Large Data Models	445	(6)
Step 1: Partition Data Model into Subject Areas	445	(1)
Step 2: Produce Context Data Model	446	(1)
Step 3: Produce Subject Area Data Models	447	(4)
Index	451