Information Modeling and Relational Databases (eBook)

Front Cover 1
Information Modeling and Relational Databases 4
Copyright Page 5
Contents 8
Foreword 14
Foreword 18
Foreword 20
Preface 24
Chapter 1. Introduction 30
1.1 Information Modeling 31
1.2 Modeling Approaches 35
1.3 Some Historical Background 47
1.4 The Relevant Skills 51
1.5 Summary 52
Chapter 2. Information Levels and Frameworks 54
2.1 Four Information Levels 55
2.2 The Conceptual Level 59
2.3 From External to Conceptual to Relational 69
2.4 Development Frameworks 76
2.5 Summary 82
Chapter 3. Conceptual Modeling: First Steps 84
3.1 Conceptual Modeling Language Criteria 85
3.2 ORM’s Conceptual Schema Design Procedure 87
3.3 CSDP Step 1: From Examples to Elementary Facts 89
3.4 CSDP Step 2: Draw Fact Types, and Populate 107
3.5 CSDP Step 3:. Trim Schema. Note Basic Derivations 122
3.6 Summary 133
Chapter 4. Uniqueness Constraints 138
4.1 CSDP Step 4: Uniqueness Constraints Arity Check
4.2 Uniqueness Constraints on Unaries and Binaries 140
4.3 Uniqueness Constraints on Longer Fact Types 152
4.4 External Uniqueness Constraints 158
4.5 Key Length Check 169
4.6 Projection-Join Check 182
4.7 Summary 187
Chapter 5. Mandatory Roles 192
5.1 Introduction to CSDP Step 5 193
5.2 Mandatory and Optional Roles 195
5.3 Logical Derivation Check 208
5.4 Reference Schemes 215
5.5 Case Study: A Compact Disc Retailer 235
5.6 Summary 239
Chapter 6. Value, Set-Comparison, and Subtype Constraints 244
6.1 CSDP Step 6: Value, Set, and Subtype Constraints 245
6.2 Basic Set Theory 245
6.3 Value Constraints and Independent Objects 249
6.4 Subset, Equality, and Exclusion Constraints 258
6.5 Subtyping 273
6.6 Generalization of Object Types 295
6.7 Summary 303
Chapter 7. Other Constraints and Final Checks 306
7.1 CSDP Step 7: Other Constraints Final Checks
7.2 Occurrence Frequencies 307
7.3 Ring Constraints 312
7.4 Other Constraints 324
7.5 Final Checks 333
7.6 Summary 339
Chapter 8. Entity Relationship Modeling 342
8.1 Overview of ER 343
8.2 Barker Notation 345
8.3 Information Engineering Notation 356
8.4 IDEF1X 361
8.5 Mapping from ORM to ER 372
8.6 Summary 375
Chapter 9. Data Modeling in UML 378
9.1 Introduction 379
9.2 Object Reference and Single-Valued Attributes 381
9.3 Multi-valued Attributes 386
9.4 Associations 391
9.5 Set Comparison Constraints 399
9.6 Subtyping 407
9.7 Other Constraints and Derivation Rules 411
9.8 Mapping from ORM to UML 425
9.9 Summary 428
Chapter 10. Relational Mapping 432
10.1 Implementing a Conceptual Schema 433
10.2 Relational Schemas 434
10.3 Relational Mapping 441
10.4 Advanced Mapping Aspects 470
10.5 Summary 484
Chapter 11. Relational Languages 486
11.1 Relational Algebra 487
11.2 Relational Database Systems 513
11.3 SQL: Historical and Structural Overview 514
11.4 SQL: Identifiers and Data Types 516
11.5 SQL: Choosing Columns, Rows, and Order 522
11.6 SQL: Joins 531
11.7 SQL: In, Between, Like, and Null Operators 543
11.8 SQL: Union and Simple Subqueries 551
11.9 SQL: Scalar Operators and Bag Functions 561
11.10 SQL: Grouping 569
11.11 SQL: Correlated and Existential Subqueries 576
11.12 SQL: Data Definition 583
11.13 SQL: Updating Table Populations 595
11.14 SQL: Security and Meta-Data 597
11.15 Summary 599
Chapter 12. Schema Transformations 604
12.1 Schema Equivalence and Optimization 605
12.2 Predicate Specialization and Generalization 609
12.3 Nesting, Co-referencing and Flattening 620
12.4 Other Conceptual Transformations 638
12.5 Conceptual Schema Optimization 642
12.6 Normalization 656
12.7 Denormalization and Low Level Optimization 671
12.8 Re-engineering 676
12.9 Summary 684
Chapter 13. Other Modeling Aspects and Trends 688
13.1 Introduction 689
13.2 Data Warehousing and OLAP 689
13.3 Conceptual Query Languages 696
13.4 Schema Abstraction Mechanisms 705
13.5 Process Modeling and External Design 710
13.6 Post-relational Databases and Other Trends 721
13.7 Meta-Modeling 731
13.8 Summary 737
ORM Glossary 742
ER Glossary 750
UML Glossary 754
Bibliography 758
Index 768
About the Author 792