Table of Contents
Contents vii
List of Figures xi
Preface xvii
About the Author xix
Chapter 1 The Evolution of Relational Database Modeling 1
1.1 From File Systems to Object-Relational Databases 2
1.1.1 File Systems 3
1.1.2 The Hierarchical Database Model 3
1.1.3 The Network Database Model 4
1.1.4 The Relational Database Model 5
1.1.5 The Object Database Model 7
1.1.6 The Object-Relational Database Model 9
1.2 General Types of Database Models 10
1.2.1 Transactional Databases 10
1.2.2 Decision Support Databases 11
1.2.3 Hybrid Databases 13
1.3 Conclusion 14
Chapter 2 The Pieces of the Relational Data Model 15
2.1 Why Discuss the Pieces? 16
2.2 Tables 17
2.2.1 Columns or Fields 18
2.2.2 Rows 30
2.3 Relationships 31
2.3.1 Representing Relationships in an ERD 31
2.3.2 Keys to Enforce Referential Integrity 41
2.4 Indexes 51
2.4.1 What is an Index? 51
2.4.2 Alternate Indexing 52
2.4.3 Types of Indexes 53
2.4.4 Different Ways to Build Indexes 56
2.5 Specialized Objects 57
2.6 Conclusion 60
Chapter 3 Intuitive Relational Data Modeling and Normalization 61
3.1 Normalization and Normal Forms 63
3.1.1 Defining the Normal Forms 63
3.2 Intuitive Database Modeling 65
3.2.1 Defining Data Modeling Intuitively 66
3.2.2 Master-Detail Relationship 67
3.2.3 Dynamic-Static Relationship 73
3.2.4 Advanced Relationships 79
3.3 Data Model Design with Normalization 87
3.3.1 Anomalies 88
3.3.2 Dependency and Determinants 89
3.3.3 Defining Normal Forms Again 93
3.3.4 1st Normal Form 94
3.3.5 2nd Normal Form 96
3.3.6 3rd Normal Form 98
3.3.7 Advanced Normalization Beyond 3rd Normal Form 100
3.4 Conclusion 113
Chapter 4 Reading and Writing Relational Data with SQL 115
4.1 What is SQL? 116
4.1.1 The Origins of SQL 117
4.1.2 SQL for Different Databases 118
4.1.3 Introducing SQL 118
4.2 Querying a Database Using SELECT 120
4.2.1 Filtering with the WHERE Clause 122
4.2.2 Sorting with the ORDER BY Clause 126
4.2.3 Aggregating with the GROUP BY Clause 127
4.2.4 Join Queries 128
4.2.5 Nested Queries 136
4.2.6 Composite Queries 137
4.3 Changing Data in a Database 138
4.3.1 Understanding Transactions 139
4.4 Changing Database Metadata 141
4.5 Conclusion 146
Chapter 5 Advanced Relational Database Modeling 147
5.1 Understanding Denormalization 149
5.1.1 Normal Form Definitions 149
5.1.2 Intuitive Data Modeling Definitions 150
5.1.3 Denormalizing Granularity Created in Chapter 3 151
5.1.4 Denormalization Using Specialized Database Objects 156
5.1.5 Denormalization Tricks 158
5.2 Understanding the Object Database Model 160
5.3 Introducing the Data Warehouse Model 165
5.4 Conclusion 167
Chapter 6 Understanding Data Warehouse Database Modeling 169
6.1 What Is a Data Warehouse? 170
6.1.1 The Relational Database Model and Data Warehouses 172
6.2 The Dimensional Database Model 174
6.2.1 What Is a Star Schema? 175
6.2.2 What Is a Snowflake Schema? 176
6.2.3 Kimball and Inmon on Data Warehousing 181
6.3 Data Warehouse Modeling 183
6.3.1 Understanding Business Processes 184
6.3.2 Granularity 184
6.3.3 Commonly Occurring Types of Dimension Tables 186
6.3.4 Understanding the Basics of Fact Tables 189
6.4 Conclusion 195
Chapter 7 Modeling for BigData Databases 197
7.1 Dimensional Modeling and Staging Databases in the Age of BigData 198
7.1.1 The Data Vault Model 199
7.1.2 The Anchor Model 203
7.1.3 Connecting the Dots from Relations and Dimensions Through to BigData 204
7.2 What Is BigData Modeling? 206
7.2.1 Some Useful BigData Modeling Terminology 206
7.2.2 Comparing ACID and BASE Consistency Models 209
7.2.3 Risks with BigData Modeling 211
7.2.4 Schema on Read (Schema-Less) 212
7.3 Four Main Types of BigData Modeling Architectures 216
7.3.1 Columnar BigData Modeling 216
7.3.2 Key-Value Store Data Modeling 221
7.3.3 Document Collection Data Modeling 231
7.3.4 Graph Data Modeling 238
7.4 Conclusion 242
Index 243
