Data Modeling Essentials

by Simsion, Witt

• ISBN13: 9780126445510
• ISBN10: 0126445516
• Edition: 3rd
• Format: Paperback
• Copyright: 2004-11-04
• Publisher: Elsevier Science

Summary

Data Modeling Essentials, Third Edition provides expert tutelage for data modelers, business analysts and systems designers at all levels. Beginning with the basics, this book provides a thorough grounding in theory before guiding the reader through the various stages of applied data modeling and database design. Later chapters address advanced subjects, including business rules, data warehousing, enterprise-wide modeling and data management. The third edition of this popular book retains its distinctive hallmarks of readability and usefulness, but has been given significantly expanded coverage and reorganized for greater reader comprehension. Authored by two leaders in the field, Data Modeling Essentials, Third Edition is the ideal reference for professionals and students looking for a real-world perspective. Thorough coverage of the fundamentals and relevant theory. Recognition and support for the creative side of the process. Expanded coverage of applied data modeling includes new chapters on logical and physical database design. New material describing a powerful technique for model verification. Unique coverage of the practical and human aspects of modeling, such as working with business specialists, managing change, and resolving conflict. Extensive online component including course notes and other teaching aids (www.mkp.com). UML diagrams now available! Visit the companion site for more details. Click here to view a book review by Steve Hoberman!

Table of Contents

Preface	xxiii
Part I The Basics	1	(228)
What Is Data Modeling?	3	(30)
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 Important?	8	(2)
Leverage	8	(1)
Conciseness	9	(1)
Data Quality	10	(1)
Summary	10	(1)
What Makes a Good Data Model?	10	(5)
Completeness	10	(1)
NonRedundancy	11	(1)
Enforcement of Business Rules	11	(1)
Data Reusability	11	(1)
Stability and Flexibility	12	(1)
Elegance	13	(1)
Communication	14	(1)
Integration	14	(1)
Conflicting Objectives	15	(1)
Performance	15	(1)
Database Design Stages and Deliverables	16	(4)
Conceptual, Logical, and Physical Data Models	16	(1)
The Three-Schema Architecture and Terminology	17	(3)
Where Do Data Models Fit In?	20	(3)
Process-Driven Approaches	20	(1)
Data-Driven Approaches	20	(2)
Parallel (Blended) Approaches	22	(1)
Object-Oriented Approaches	22	(1)
Prototyping Approaches	23	(1)
Agile Methods	23	(1)
Who Should Be Involved in Data Modeling?	23	(1)
Is Data Modeling Still Relevant?	24	(5)
Costs and Benefits of Data Modeling	25	(1)
Data Modeling and Packaged Software	26	(1)
Data Integration	27	(1)
Data Warehouses	27	(1)
Personal Computing and User-Developed Systems	28	(1)
Data Modeling and XML	28	(1)
Summary	28	(1)
Alternative Approaches to Data Modeling	29	(1)
Terminology	30	(1)
Where to from Here?---An Overview of Part I	31	(1)
Summary	32	(1)
Basics of Sound Structure	33	(32)
Introduction	33	(1)
An Informal Example of Normalization	34	(2)
Relational Notation	36	(1)
A More Complex Example	37	(3)
Determining Columns	40	(3)
One Fact per Column	40	(1)
Hidden Data	41	(1)
Derivable Data	41	(1)
Determining the Primary Key	41	(2)
Repeating Groups and First Normal Form	43	(4)
Limit on Maximum Number of Occurrences	43	(1)
Data Reusability and Program Complexity	43	(1)
Recognizing Repeating Groups	44	(1)
Removing Repeating Groups	45	(1)
Determining the Primary Key of the New Table	46	(1)
First Normal Form	47	(1)
Second and Third Normal Forms	47	(6)
Problems with Tables in First Normal Form	47	(1)
Eliminating Redundancy	48	(1)
Determinants	48	(3)
Third Normal Form	51	(2)
Definitions and a Few Refinements	53	(7)
Determinants and Functional Dependency	53	(1)
Primary Keys	54	(1)
Candidate Keys	54	(1)
A More Formal Definition of Third Normal Form	55	(1)
Foreign Keys	55	(1)
Referential Integrity	56	(1)
Update Anomalies	57	(1)
Denormalization and Unnormalization	58	(1)
Column and Table Names	59	(1)
Choice, Creativity, and Normalization	60	(2)
Terminology	62	(1)
Summary	63	(2)
The Entity-Relationship Approach	65	(46)
Introduction	65	(1)
A Diagrammatic Representation	65	(7)
The Basic Symbols: Boxes and Arrows	66	(1)
Diagrammatic Representation of Foreign Keys	67	(1)
Interpreting the Diagram	68	(1)
Optionality	69	(1)
Verifying the Model	70	(1)
Redundant Arrows	71	(1)
The Top-Down Approach: Entity-Relationship Modeling	72	(4)
Developing the Diagram Top Down	74	(1)
Terminology	75	(1)
Entity Classes	76	(6)
Entity Diagramming Convention	77	(1)
Entity Class Naming	78	(2)
Entity Class Definitions	80	(2)
Relationships	82	(22)
Relationship Diagramming Conventions	82	(5)
Many-to-Many Relationships	87	(5)
One-to-One Relationships	92	(1)
Self-Referencing Relationships	93	(3)
Relationships Involving Three or More Entity Classes	96	(2)
Transferability	98	(4)
Dependent and Independent Entity Classes	102	(1)
Relationship Names	103	(1)
Attributes	104	(1)
Attribute Identification and Definition	104	(1)
Primary Keys and the Conceptual Model	105	(1)
Myths and Folklore	105	(1)
Entity Classes without Relationships	106	(1)
Allowed Combinations of Cardinality and Optionality	106	(1)
Creativity and E-R Modeling	106	(3)
Summary	109	(2)
Subtypes and Supertypes	111	(34)
Introduction	111	(1)
Different Levels of Generalization	111	(2)
Rules versus Stability	113	(2)
Using Subtypes and Supertypes	115	(1)
Subtypes and Supertypes as Entity Classes	116	(1)
Naming Subtypes	117	(1)
Diagramming Conventions	117	(2)
Boxes in Boxes	117	(1)
UML Conventions	118	(1)
Using Tools That Do Not Support Subtyping	119	(1)
Definitions	119	(1)
Attributes of Supertypes and Subtypes	119	(1)
Nonoverlapping and Exhaustive	120	(3)
Overlapping Subtypes and Roles	123	(4)
Ignoring Real-World Overlaps	123	(1)
Modeling Only the Supertype	124	(1)
Modeling the Roles as Participation in Relationships	124	(1)
Using Role Entity Classes and One-to-One Relationships	125	(1)
Multiple Partitions	126	(1)
Hierarchy of Subtypes	127	(1)
Benefits of Using Subtypes and Supertypes	128	(6)
Creativity	129	(1)
Presentation: Level of Detail	129	(1)
Communication	130	(2)
Input to the Design of Views	132	(1)
Classifying Common Patterns	132	(1)
Divide and Conquer	133	(1)
When Do We Stop Supertyping and Subtyping?	134	(4)
Differences in Identifiers	134	(1)
Different Attribute Groups	135	(1)
Different Relationships	135	(1)
Different Processes	136	(1)
Migration from One Subtype to Another	136	(1)
Communication	136	(1)
Capturing Meaning and Rules	137	(1)
Summary	137	(1)
Generalization of Relationships	138	(4)
Generalizing Several One-to-Many Relationships to a Single Many-to-Many Relationship	138	(1)
Generalizing Several One-to-Many Relationships to a Single One-to-Many Relationship	139	(2)
Generalizing One-to-Many and Many-to-Many Relationships	141	(1)
Theoretical Background	142	(1)
Summary	143	(2)
Attributes and Columns	145	(38)
Introduction	145	(1)
Attribute Definition	146	(1)
Attribute Disaggregation: One Fact per Attribute	147	(5)
Simple Aggregation	148	(2)
Conflated Codes	150	(1)
Meaningful Ranges	151	(1)
Inappropriate Generalization	151	(1)
Types of Attributes	152	(14)
DBMS Datatypes	152	(2)
The Attribute Taxonomy in Detail	154	(4)
Attribute Domains	158	(4)
Column Datatype and Length Requirements	162	(4)
Conversion Between External and Internal Representations	166	(1)
Attribute Names	166	(5)
Objectives of Standardizing Attribute Names	166	(2)
Some Guidelines for Attribute Naming	168	(3)
Attribute Generalization	171	(9)
Options and Trade-Offs	171	(1)
Attribute Generalization Resulting from Entity Generalization	172	(1)
Attribute Generalization within Entity Classes	173	(4)
``First Among Equals''	177	(1)
Limits to Attribute Generalization	178	(2)
Summary	180	(3)
Primary Keys and Identity	183	(24)
Basic Requirements and Trade-Offs	183	(2)
Basic Technical Criteria	185	(6)
Applicability	185	(1)
Uniqueness	186	(2)
Minimality	188	(1)
Stability	189	(2)
Surrogate Keys	191	(3)
Performance and Programming Issues	191	(1)
Matching Real-World Identifiers	191	(1)
Should Surrogate Keys Be Visible?	192	(1)
Subtypes and Surrogate Keys	193	(1)
Structured Keys	194	(7)
When to Use Structured Keys	196	(1)
Programming and Structured Keys	197	(1)
Performance Issues with Structured Keys	198	(1)
Running Out of Numbers	199	(2)
Multiple Candidate Keys	201	(1)
Choosing a Primary Key	201	(1)
Normalization Issues	201	(1)
Guidelines for Choosing Keys	202	(2)
Tables Implementing Independent Entity Classes	202	(1)
Tables Implementing Dependent Entity Classes and Many-to-Many Relationships	203	(1)
Partially-Null Keys	204	(2)
Summary	206	(1)
Extensions and Alternatives	207	(22)
Introduction	207	(2)
Extensions to the Basic E-R Approach	209	(7)
Introduction	209	(1)
Advanced Attribute Concepts	210	(6)
The Chen E-R Approach	216	(4)
The Basic Conventions	216	(1)
Relationships with Attributes	217	(1)
Relationships Involving Three or More Entity Classes	217	(1)
Roles	218	(1)
The Weak Entity Concept	219	(1)
Chen Conventions in Practice	220	(1)
Using UML Object Class Diagrams	220	(7)
A Conceptual Data Model in UML	221	(1)
Advantages of UML	222	(5)
Object Role Modeling	227	(1)
Summary	228	(1)
Part II Putting It Together	229	(160)
Organizing the Data Modeling Task	231	(20)
Data Modeling in the Real World	231	(2)
Key Issues in Project Organization	233	(5)
Recognition of Data Modeling	233	(1)
Clear Use of the Data Model	234	(1)
Access to Users and Other Business Stakeholders	234	(1)
Conceptual, Logical, and Physical Models	235	(1)
Cross-Checking with the Process Model	236	(1)
Appropriate Tools	237	(1)
Roles and Responsibilities	238	(2)
Partitioning Large Projects	240	(2)
Maintaining the Model	242	(6)
Examples of Complex Changes	242	(5)
Managing Change in the Modeling Process	247	(1)
Packaging It Up	248	(1)
Summary	249	(2)
The Business Requirements	251	(22)
Purpose of the Requirements Phase	251	(2)
The Business Case	253	(1)
Interviews and Workshops	254	(4)
Should You Model in Interviews and Workshops?	255	(1)
Interviews with Senior Managers	256	(1)
Interviews with Subject Matter Experts	257	(1)
Facilitated Workshops	257	(1)
Riding the Trucks	258	(1)
Existing Systems and Reverse Engineering	259	(2)
Process Models	261	(1)
Object Class Hierarchies	261	(9)
Classifying Object Classes	263	(2)
A Typical Set of Top-Level Object Classes	265	(2)
Developing an Object Class Hierarchy	267	(3)
Potential Issues	270	(1)
Advantages of the Object Class Hierarchy Technique	270	(1)
Summary	270	(3)
Conceptual Data Modeling	273	(48)
Designing Real Models	273	(2)
Learning from Designers in Other Disciplines	275	(1)
Starting the Modeling	276	(1)
Patterns and Generic Models	277	(8)
Using Patterns	277	(1)
Using a Generic Model	278	(1)
Adapting Generic Models from Other Applications	279	(3)
Developing a Generic Model	282	(2)
When There Is Not a Generic Model	284	(1)
Bottom-Up Modeling	285	(3)
Top-Down Modeling	288	(1)
When the Problem Is Too Complex	288	(2)
Hierarchies, Networks, and Chains	290	(5)
Hierarchies	291	(2)
Networks (Many-to-Many Relationships)	293	(2)
Chains (One-to-One Relationships)	295	(1)
One-to-One Relationships	295	(5)
Distinct Real-World Concepts	296	(1)
Separating Attribute Groups	297	(1)
Transferable One-to-One Relationships	298	(1)
Self-Referencing One-to-One Relationships	299	(1)
Support for Creativity	299	(1)
Developing Entity Class Definitions	300	(1)
Handling Exceptions	301	(1)
The Right Attitude	302	(3)
Being Aware	303	(1)
Being Creative	303	(1)
Analyzing or Designing	303	(1)
Being Brave	304	(1)
Being Understanding and Understood	304	(1)
Evaluating the Model	305	(1)
Direct Review of Data Model Diagrams	306	(2)
Comparison with the Process Model	308	(1)
Testing the Model with Sample Data	308	(1)
Prototypes	309	(1)
The Assertions Approach	309	(10)
Naming Conventions	310	(1)
Rules for Generating Assertions	311	(8)
Summary	319	(2)
Logical Database Design	321	(38)
Introduction	321	(1)
Overview of the Transformations Required	322	(3)
Table Specification	325	(9)
The Standard Transformation	325	(1)
Exclusion of Entity Classes from the Database	325	(1)
Classification Entity Classes	325	(1)
Many-to-Many Relationship Implementation	326	(2)
Relationships Involving More Than Two Entity Classes	328	(1)
Supertype/Subtype Implementation	328	(6)
Basic Column Definition	334	(7)
Attribute Implementation: The Standard Transformation	334	(1)
Category Attribute Implementation	335	(1)
Derivable Attributes	336	(1)
Attributes of Relationships	336	(1)
Complex Attributes	337	(1)
Multivalued Attribute Implementation	337	(2)
Additional Columns	339	(1)
Column Datatypes	340	(1)
Column Nullability	340	(1)
Primary Key Specification	341	(1)
Foreign Key Specification	342	(12)
One-to-Many Relationship Implementation	343	(3)
One-to-One Relationship Implementation	346	(1)
Derivable Relationships	347	(1)
Optional Relationships	348	(2)
Overlapping Foreign Keys	350	(2)
Split Foreign Keys	352	(2)
Table and Column Names	354	(1)
Logical Data Model Notations	355	(2)
Summary	357	(2)
Physical Database Design	359	(30)
Introduction	359	(2)
Inputs to Database Design	361	(1)
Options Available to the Database Designer	362	(1)
Design Decisions Which Do Not Affect Program Logic	363	(9)
Indexes	363	(7)
Data Storage	370	(2)
Memory Usage	372	(1)
Crafting Queries to Run Faster	372	(2)
Locking	373	(1)
Logical Schema Decisions	374	(10)
Alternative Implementation of Relationships	374	(1)
Table Splitting	374	(2)
Table Merging	376	(1)
Duplication	377	(1)
Denormalization	378	(1)
Ranges	379	(1)
Hierarchies	380	(2)
Integer Storage of Dates and Times	382	(1)
Additional Tables	383	(1)
Views	384	(2)
Views of Supertypes and Subtypes	385	(1)
Inclusion of Derived Attributes in Views	385	(1)
Denormalization and Views	385	(1)
Views of Split and Merged Tables	386	(1)
Summary	386	(3)
Part III Advanced Topics	389	(130)
Advanced Normalization	391	(26)
Introduction	391	(1)
Introduction to the Higher Normal Forms	392	(2)
Common Misconceptions	392	(2)
Boyce-Codd Normal Form	394	(4)
Example of Structure in 3NF but not in BCNF	394	(2)
Definition of BCNF	396	(1)
Enforcement of Rules versus BCNF	397	(1)
A Note on Domain Key Normal Form	398	(1)
Fourth Normal Form (4NF) and Fifth Normal Form (5NF)	398	(9)
Data in BCNF but not in 4NF	399	(2)
Fifth Normal Form (5NF)	401	(3)
Recognizing 4NF and 5NF Situations	404	(1)
Checking for 4NF and 5NF with the Business Specialist	405	(2)
Beyond 5NF: Splitting Tables Based on Candidate Keys	407	(1)
Other Normalization Issues	408	(7)
Normalization and Redundancy	408	(2)
Reference Tables Produced by Normalization	410	(1)
Selecting the Primary Key after Removing Repeating Groups	411	(3)
Sequence of Normalization and Cross-Table Anomalies	414	(1)
Advanced Normalization in Perspective	415	(1)
Summary	416	(1)
Modeling Business Rules	417	(34)
Introduction	417	(1)
Types of Business Rules	418	(2)
Data Rules	418	(2)
Process Rules	420	(1)
What Rules are Relevant to the Data Modeler?	420	(1)
Discovery and Verification of Business Rules	420	(2)
Cardinality Rules	420	(1)
Other Data Validation Rules	421	(1)
Data Derivation Rules	421	(1)
Documentation of Business Rules	422	(5)
Documentation in an E-R Diagram	422	(1)
Documenting Other Rules	422	(2)
Use of Subtypes to Document Rules	424	(3)
Implementing Business Rules	427	(19)
Where to Implement Particular Rules	428	(5)
Implementation Options: A Detailed Example	433	(3)
Implementing Mandatory Relationships	436	(2)
Referential Integrity	438	(1)
Restricting an Attribute to a Discrete Set of Values	439	(3)
Rules Involving Multiple Attributes	442	(1)
Recording Data That Supports Rules	442	(1)
Rules That May Be Broken	443	(2)
Enforcement of Rules Through Primary Key Selection	445	(1)
Rules on Recursive Relationships	446	(4)
Types of Rules on Recursive Relationships	447	(2)
Documenting Rules on Recursive Relationships	449	(1)
Implementing Constraints on Recursive Relationships	449	(1)
Analogous Rules in Many-to-Many Relationships	450	(1)
Summary	450	(1)
Time-Dependent Data	451	(24)
The Problem	451	(1)
When Do We Add the Time Dimensions?	452	(1)
Audit Trails and Snapshots	452	(10)
The Basic Audit Trail Approach	453	(5)
Handling Nonnumeric Data	458	(1)
The Basic Snapshot Approach	458	(4)
Sequences and Verisions	462	(1)
Handling Deletions	463	(1)
Archiving	463	(1)
Modeling Time-Dependent Relationships	464	(5)
One-to-Many Relationships	464	(2)
Many-to-Many Relationships	466	(2)
Self-Referencing Relationships	468	(1)
Date Tables	469	(1)
Temporal Business Rules	469	(4)
Changes to the Data Structure	473	(1)
Putting It into Practice	473	(1)
Summary	474	(1)
Modeling for Data Warehouses and Data Marts	475	(24)
Introduction	475	(3)
Characteristics of Data Warehouses and Data Marts	478	(2)
Data Integration: Working with Existing Databases	478	(1)
Loads Rather Than Updates	478	(1)
Less Predictable Database ``Hits''	479	(1)
Complex Queries---Simple Interface	479	(1)
History	480	(1)
Summarization	480	(1)
Quality Criteria for Warehouse and Mart Models	480	(3)
Completeness	480	(1)
Nonredundancy	481	(1)
Enforcement of Business Rules	482	(1)
Data Reusability	482	(1)
Stability and Flexibility	482	(1)
Simplicity and Elegance	483	(1)
Communication Effectiveness	483	(1)
Performance	483	(1)
The Basic Design Principle	483	(1)
Modeling for the Data Warehouse	484	(4)
An Initial Model	484	(1)
Understanding Existing Data	485	(1)
Determining Requirements	485	(1)
Determining Sources and Dealing with Differences	485	(2)
Shaping Data for Data Marts	487	(1)
Modeling for the Data Mart	488	(8)
The Basic Challenge	488	(1)
Multidimensional Databases, Stars and Snowflakes	488	(6)
Modeling Time-Dependent Data	494	(2)
Summary	496	(3)
Enterprise Data Models and Data Management	499	(20)
Introduction	499	(1)
Data Management	500	(3)
Problems of Data Mismanagement	500	(1)
Managing Data as a Shared Resource	501	(1)
The Evolution of Data Management	501	(2)
Classification of Existing Data	503	(1)
A Target for Planning	504	(2)
A Context for Specifying New Databases	506	(2)
Determining Scope and Interfaces	506	(1)
Incorporating the Enterprise Data Model in the Development Life Cycle	506	(2)
Guidance for Database Design	508	(1)
Input to Business Planning	508	(1)
Specification of an Enterprise Database	509	(2)
Characteristics of Enterprise Data Models	511	(1)
Developing an Enterprise Data Model	512	(4)
The Development Cycle	512	(1)
Partitioning the Task	513	(1)
Inputs to the Task	514	(1)
Expertise Requirements	515	(1)
External Standards	515	(1)
Choice, Creativity, and Enterprise Data Models	516	(1)
Summary	517	(2)
Further Reading	519