Database Modeling with Microsoft(R) Visio for Enterprise Architects (eBook)

Front Cover 1
Database Modeling with Microsoft® Visio for Enterprise Architects 4
Copyright Page 5
Contents 6
Part 1: Overview of Database Modeling and the Database Modeling Tool 16
Chapter 1. Introduction 18
1.1 Why Read This Book? 19
1.2 What Can You Do with Visio for Enterprise Architects? 20
1.3 What Can You Do with the Database Modeling Solution? 21
1.4 How Can You Best Use This Book? 29
1.5 Format Conventions 31
Chapter 2. Database Modeling 34
2.1 Four Information Levels 35
2.2 Designing Databases at the Logical level 36
2.3 Designing Databases at the Conceptual Level 40
2.4 The Database Life Cycle 42
Chapter 3. Getting Started 46
3.1 Product Editions and Versions 47
3.2 Installation 47
3.3 The Visio Interface 50
3.4 Using Help 58
3.5 Pagination and Layers 59
3.6 Simple Examples of Forward Engineering 63
Part 2: The Conceptual Modeling Solution (ORM) 78
Chapter 4. Object Types, Predicates, and Basic Constraints 80
4.1 Object Types 81
4.2 Fact Types 89
4.3 Adding Basic Constraints in the Fact Editor 94
4.4 Populating Fact Types with Examples 97
4.5 Saving a Model 98
4.6 Verbalization and Hyphenation 99
4.7 Objectifying an Association (Nesting) 100
4.8 Model Error Checks 102
4.9 Derived Fact Types 103
4.10 Data Types 104
Chapter 5. ORM Constraints 112
5.1 Value Constraints 113
5.2 Internal and External Uniqueness Constraints 114
5.3 Simple and Disjunctive Mandatory Constraints 117
5.4 Constraint Editing and Deletion 119
5.5 Set-Comparison Constraints 120
5.6 Subtyping 124
5.7 Frequency Constraints 127
5.8 Ring Constraints 129
5.9 Indexes 133
5.10 Constraint Layers 134
Chapter 6. Configuring, Manipulating, and Reusing ORM Models 136
6.1 Configuring ORM Preferences 137
6.2 Showing Relationships for Object Types 138
6.3 Redisplaying Model Elements 140
6.4 Cloning Model Elements 141
6.5 Cutting and Pasting Model Elements 143
6.6 Referencing Model Elements 144
Chapter 7. Mapping ORM Models to Logical Database Models 148
7.1 Forward Engineering ORM Source Models 149
7.2 Refining the Logical Model 153
7.3 Migrating Changes back to ORM Source Models 154
7.4 Controlling Logical Name Generation 157
7.5 Constraint Code 166
7.6 Subtype Mapping 171
Chapter 8. Reverse Engineering and Importing to ORM 180
8.1 Reverse Engineering to ORM 181
8.2 Displaying the ORM Model Graphically 187
8.3 Refining the ORM Schema 189
8.4 Importing VisioModeler and ERX Files 190
Chapter 9. Conceptual Model Reports 192
9.1 Conceptual Model Reports—Overview 193
9.2 Object Type Reports 194
9.3 Constraint Type Reports 205
9.4 Fact Type Reports 207
9.5 Supertype Reports 210
9.6 Copying Diagrams and Text 212
Part 3: The Logical Modeling Solution (ER and Relational) 216
Chapter 10. Creating a Basic Logical Database Model 218
10.1 The Database Model Diagram Solution 219
10.2 Notation Options 219
10.3 Creating a Database Model Diagram 223
10.4 Adding Attributes to an Entity 227
10.5 Adding Basic Constraints 231
10.6 Basic Model Housekeeping 237
10.7 Projects and ER Source Models 238
Chapter 11. Generating a Physical Database Schema 246
11.1 Physical Schemas and Database Drivers 247
11.2 Creating a Basic DDL Script 247
11.3 Database Driver Options 253
11.4 Generating Schemas via an ODBC Connection 259
11.5 The ODBC Driver Setup Pane 266
Chapter 12. Editing Logical Models: Intermediate Aspects 270
12.1 The Database Properties Dialog 271
12.2 Table Properties 271
12.3 Column Properties 272
12.4 Foreign Key Relationships 276
12.5 Category Relationships 279
12.6 Show Related Tables 280
12.7 Views 281
12.8 Pagination 285
12.9 Verbalization 286
12.10 Model Error Check 286
12.11 Driver, Document, and Modeling Options 287
Chapter 13. Editing Logical Models: Advanced Aspects 296
13.1 Copying Model Elements 297
13.2 Cloning Model Elements 299
13.3 Database Properties: Definition and Notes 300
13.4 Check Clauses 300
13.5 Indexes 301
13.6 Triggers 302
13.7 Stored Procedures 304
13.8 Raw DDL 304
13.9 User Defined Functions 305
13.10 User Defined Types 306
13.11 Extended Properties 307
13.12 The Object-Relational Stencil 309
Chapter 14. Reverse Engineering Physical Schemas to Logical Models 312
14.1 Using the Reverse Engineer Wizard 313
14.2 Reviewing the Result 316
14.3 Handling Errors and Warnings 320
14.4 Other Reverse Engineering Options 320
Chapter 15. Logical Database Model Reports 322
15.1 New Report Wizard 323
15.2 Statistical Report 324
15.3 Table Report 325
15.4 Data Types Report 337
Part 4: Managing Database Projects 342
Chapter 16. Change Propagation and Round-Trip Engineering 344
16.1 Overview of Change Propagation 345
16.2 Mapping ORM Changes to Logical Models 345
16.3 Migrating Logical Model Changes to ORM Models 349
16.4 Updating Physical Schemas with Logical Model Changes 351
16.5 Refreshing Logical Models with Physical Schema Changes 355
16.6 Three-Way Synchronization 358
16.7 Code Synchronization and Transfer 364
16.8 Data Migration 369
16.9 DDL Script vs. Automated Update 372
Chapter 17. Other Features and Best Practices 376
17.1 Model Reviews and Presentations 377
17.2 Librarianship 395
17.3 Distributing Your Database Models 399
Glossary and ORM Notation 404
Further Resources 416
Index 426