Database modeling and design : logical design

Cover......Page 1
Front matter\r......Page 2
Copyright\r......Page 5
Dedication\r......Page 6
Preface\r......Page 7
Organization......Page 8
Solutions Manual......Page 10
About the Authors\r......Page 11
Introduction......Page 13
Class Diagrams......Page 14
Database Life Cycle......Page 15
Identity......Page 150
Query Execution Plan Viewing......Page 22
Literature Summary......Page 23
The Entity-Relationship Model......Page 24
Relationships......Page 26
SQL Select Command......Page 272
Attributes and Keys......Page 29
Degree of a Relationship......Page 30
Connectivity of a Relationship......Page 31
Attributes of a Relationship......Page 32
Existence of an Entity in a Relationship......Page 33
Generalization: Supertypes and Subtypes......Page 34
Aggregation......Page 38
Ternary Relationships......Page 39
Exclusion Constraint......Page 42
Summary......Page 43
Tips and Insights for Database Professionals......Page 44
Literature Summary......Page 45
The Unified Modeling Language......Page 46
Basic Class Diagram Notation......Page 48
Class Diagrams for Database Design......Page 50
Example from the Music Industry......Page 55
Third Normal Form......Page 58
Activity Diagram Notation Description......Page 59
Activity Diagrams for Workflow......Page 61
Tips and Insights for Database Professionals......Page 63
Literature Summary......Page 64
Requirements Analysis and Conceptual Data Modeling......Page 65
Introduction......Page 66
Key Capabilities to Watch for......Page 67
Classify Entities and Attributes......Page 68
Multivalued Attributes......Page 69
Optimization Depth......Page 313
Identify the Generalization Hierarchies......Page 70
Redundant Relationships......Page 71
Ternary Relationships......Page 72
ER Modeling of Individual Views Based on Requirements......Page 74
Proof:......Page 77
View Integration......Page 78
Comparison of Schemas: Identifying Conflicts......Page 80
Conformation of Schemas: Resolving Conflicts......Page 81
Merging and Restructuring of Schemas......Page 83
Entity Clustering for ER Models......Page 86
Clustering Concepts......Page 87
Grouping Operations......Page 88
Clustering Technique......Page 89
Summary......Page 91
Literature Summary......Page 93
Transforming the Conceptual Data Model to SQL......Page 95
Transformation Rules and SQL Constructs......Page 96
Conceptual Data Modeling and Integration......Page 97
Ternary and n-ary Relationships......Page 102
Database Support......Page 105
Collaborative Support......Page 109
Weak Entities......Page 110
Normalization of Candidate Tables Derived from ER Diagrams......Page 130
Entity Transformation......Page 113
Many-to-Many Binary Relationship Transformation......Page 115
Example of ER-to-SQL Transformation......Page 116
Summary......Page 117
Literature Summary......Page 118
Normalization......Page 119
Fundamentals of Normalization......Page 120
First Normal Form......Page 121
Superkeys, Candidate Keys, and Primary Keys......Page 122
Second Normal Form......Page 123
Boyce-Codd Normal Form......Page 127
The Design of Normalized Tables: A Simple Example......Page 128
Inference Rules (Armstrong Axioms)......Page 134
Superkey Rule 2......Page 135
Reduction Rule 1......Page 136
Step 4: Merge of Equivalent Keys (Merge of Tables)......Page 137
Summary......Page 138
Tips and Insights for Database Professionals......Page 139
Literature Summary......Page 140
Requirements Specification......Page 141
Classes and Instances......Page 142
Logical Design......Page 143
Encapsulation......Page 147
Object-Relational Design......Page 148
Object Orientation......Page 149
Abstraction......Page 151
The Impedance Mismatch......Page 152
Object-Relational Mapping......Page 153
Persistent Programming Languages......Page 155
Features of Object-Oriented Database Systems......Page 158
User-Defined Functions and Abstract Data Types......Page 162
An Evaluation of Object-Relational Systems......Page 163
Design Considerations......Page 164
Summary......Page 167
Estimating the Selectivity Factor for a Join......Page 168
Background......Page 170
Definitions in XML......Page 172
Schema Design......Page 177
Text......Page 182
XML Data in an RDBMS......Page 185
Query Cost Evaluation-An Example......Page 187
Option 1A Cost Estimation: Brute-Force Method of Doing All Joins First, with No Indexes......Page 315
Dynamic Pages......Page 191
Website Structure......Page 193
Summary......Page 194
Literature Summary......Page 195
Business Intelligence......Page 197
Overview of Data Warehousing......Page 198
Network Time Delays......Page 334
The Life Cycle for Data Warehouses......Page 202
Union and Intersection Commands......Page 204
Snowflake Schema......Page 206
Dimensional Modeling Example......Page 207
Business Processes......Page 208
Estimating Process......Page 209
Scheduling Process......Page 211
Productivity Tracking......Page 214
Job Costing......Page 215
Summarizing Data......Page 216
Online Analytical Processing......Page 217
The Exponential Explosion of Views......Page 218
Overview of OLAP......Page 220
View Size Estimation......Page 221
Selection of Materialized Views......Page 224
View Maintenance......Page 227
Query Optimization......Page 228
Data Mining......Page 230
Forecasting......Page 231
Text Mining......Page 235
Summary......Page 237
Tips and Insights for Database Professionals......Page 238
Literature Summary......Page 239
CASE Tools for Logical Database Design......Page 240
Introduction to the CASE Tools......Page 241
Transformation of the Conceptual Model to SQL......Page 292
Generating a Database from a Design......Page 247
Distributed Development......Page 254
Design Compliance Checking......Page 256
Reporting......Page 258
Modeling a Data Warehouse......Page 259
Semistructured Data-XML......Page 263
Tips and Insights for Database Professionals......Page 265
Literature Summary......Page 266
Appendix the Basics of SQL\r......Page 267
SQL Names and Operators......Page 268
Data Definition Language......Page 269
Basic Commands......Page 274
Aggregate Functions......Page 276
Joins and Subqueries......Page 278
Referential Integrity......Page 281
SQL Views......Page 282
References......Page 283
References\r......Page 284
ER and UML Conceptual Data Modeling......Page 290
Normalization and Minimum Set of Tables......Page 293
Histograms......Page 312
OLAP......Page 295
Solutions to selected Exercises\r......Page 296
Glossary\r......Page 299
C\r......Page 305
F\r......Page 306
R\r......Page 307
X\r......Page 308
Bonus Chapter Opener\r......Page 309
Query optimization and Plan selection\r......Page 310
Useful Optimization Features in Database Systems......Page 311
Example Query 3.1......Page 314
Option 1B Cost Estimation: Do All Selections (Including Projections) before Joins, without Indexes (and an Exploration of the P......Page 318
Query Execution Plan Development......Page 321
Transformation Rules for Query Execution Plans......Page 322
Selectivity Factors, Table Size, and Query Cost Estimation......Page 323
Estimating Selectivity Factor for a Selection Operation or Predicate......Page 324
Histograms......Page 325
Example Query 3.2......Page 326
Case 1: Join Executed First......Page 327
Case 2: Selections Executed First......Page 328
Option 1A (Figure 3.1)......Page 329
Summary......Page 330
Literature Summary\r......Page 331
I/O Time Cost-Individual Block Access......Page 332
I/O Time Cost-Table Scans and Sorts......Page 333
References......Page 335