Contents

Read me first!              1

Conventions Used in this Book              3

How to get the Most out of this Book              4

SECTION I: Overview              7

CHAPTER 1: Data Model Overview              9

Finding Your Way              10

Representing an Information Landscape              10

Communicating During the Modeling Process              11

Communicating After the Modeling Process              11

Leveraging the Data Model              11

Embarking on Our Publishing Adventure              12

EXERCISE 1.1: Educating Your Neighbor              16

CHAPTER 2: ER/Studio Functionality              19

EXERCISE 2.1: Learning More About the ER/Studio XE3 Family              20

New Features              21

My “Top 10” Favorite Features of ER/Studio              21

EXERCISE 2.2: Installing and Starting ER/Studio              23

CHAPTER 3: ER/Studio Landscape              25

Using the Windows              26

Data Model Explorer              26

Data Model Window              26

Overview Window              26

Zoom Window              27

Using the Menus              27

Application Menus              27

Shortcut Menus              28

Using the Toolbars              28

Using Keyboard Commands              32

Using the Status Bar              33

EXERCISE 3.1: Creating a New Data Model              34

Creating Title Blocks              35

Editing Title Blocks              35

Deleting Title Blocks              36

EXERCISE 3.2: Saving Your Data Model              36

EXERCISE 3.3: Closing and Opening Existing Data Models              37

EXERCISE 3.4: Getting Comfortable with ER/Studio              38

SECTION II: Data Model Objects              41

CHAPTER 4: Entities              43

Entity Explanation              43

Entity Types              44

Entities in ER/Studio              45

Creating Entities              45

Creating Text Blocks              46

Editing Entities              47

Changing the Name of an Entity              49

Moving Entities              49

Resizing Entities              51

Changing the Appearance of Entities              51

Copying Entities              53

Finding Entities              54

Find Entity/View              54

Universal Naming Utility              54

Deleting Entities              56

EXERCISE 4.1: Creating Entities in ER/Studio              56

CHAPTER 5: Submodels              59

Submodel Explanation              59

Submodels in ER/Studio              60

Creating Submodels              60

Editing Submodels              63

Moving Submodels              63

Deleting Submodels              63

EXERCISE 5.1: Changing Settings in Submodels              64

EXERCISE 5.2: Creating Three More Submodels              64

EXERCISE 5.3: Creating Title Blocks for each Submodel              65

CHAPTER 6: Attributes and Domains              67

Attribute Explanation              67

Attribute Types              67

Attributes in ER/Studio              68

Creating Attributes              68

Editing Attributes              72

Moving Attributes              72

Defining Attributes              72

Changing the Appearance of Attributes              73

Copying Attributes              74

Finding Attributes              75

Deleting Attributes              75

EXERCISE 6.1: Creating Attributes in ER/Studio              75

Key Explanation              76

Candidate Key              77

Primary and Alternate Keys              79

Surrogate Key              80

Foreign Key              81

Inversion Entry              82

EXERCISE 6.2: Clarifying Customer ID              82

Keys in ER/Studio              82

Creating a Key              83

Editing a Key              84

EXERCISE 6.3: Creating Keys in ER/Studio              84

Domain Explanation              85

Domains in ER/Studio              87

CHAPTER 7: Relationships              89

Relationship Explanation              89

Relationship Types              89

Cardinality              90

Independent vs. Dependent Entities              93

Recursion              94

Subtyping              96

EXERCISE 7.1: Reading a Model              97

Data Modeling Notations              97

Relationships in ER/Studio              98

Creating Relationships              98

Creating Subtypes              101

Creating Recursive Relationships              102

Duplicate Relationships              102

Duplicate Attributes              103

Editing Relationships              104

Moving Relationships              106

Changing the Appearance of Relationships              107

Finding Relationships              108

Deleting Relationships              109

EXERCISE 7.2: Creating Relationships in ER/Studio              109

SECTION III: Conceptual, Logical, and Physical Data Models              111

CHAPTER 8: Conceptual Data Models              113

Conceptual Data Model Explanation              113

Relational and Dimensional Conceptual Data Models              117

Relational CDM Example              117

Dimensional CDM Example              118

Creating a Conceptual Data Model              121

EXERCISE 8.1: Creating a Conceptual Data Models in ER/Studio              123

EXERCISE 8.2: Adding Definitions to a Data Model              123

EXERCISE 8.3: Segmenting the Publisher CDM into Submodels              124

EXERCISE 8.4: Creating a Conceptual Data Model for Your Organization              124

CHAPTER 9: Logical Data Models              125

Logical Data Model Explanation              125

Relational and Dimensional Logical Data Models              126

Relational LDM Example              126

Dimensional LDM Example              127

Creating a Relational Logical Data Model              128

Normalization              128

Initial Chaos              130

First Normal Form (1NF)              131

Second Normal Form (2NF)              133

Third Normal Form (3NF)              134

Abstraction              136

EXERCISE 9.1: Creating a Logical Relational Data Model in ER/Studio              137

Creating a Dimensional Logical Data Model              139

CHAPTER 10: Physical Data Models              143

Physical Data Model Explanation              143

Relational and Dimensional Physical Data Models              144

Relational PDM Example              144

Dimensional PDM Example              145

Creating a Physical Data Model in ER/Studio              146

Generating a Physical Data Model from a Logical Data Model              146

Generating a Physical Data Model from a Database (Reverse Engineering)              150

Generating a Physical Data Model from a SQL File              153

Editing Tables              155

Customizing Datatype Mapping              158

Denormalization              159

Rolldown Denormalization              160

Rollup Denormalization              160

Star Schema              161

Denormalizing in ER/Studio              162

EXERCISE 10.1: Denormalizing in ER/Studio              164

Views              164

Views in ER/Studio              165

EXERCISE 10.2: Creating Views in ER/Studio              168

Indexing              169

Indexing in ER/Studio              169

EXERCISE 10.3: Indexing in ER/Studio              170

Partitioning              170

Partitioning in ER/Studio              171

EXERCISE 10.4: Partitioning in ER/Studio              174

EXERCISE 10.5: Tracing from Physical back to Logical              174

SECTION IV: Additional ER/Studio Features              175

CHAPTER 11: Data Dictionary              177

Importing a Data Dictionary              177

Types of Objects Imported from a Data Dictionary              180

Domains              180

Domains in ER/Studio              180

EXERCISE 11.1: Creating Domains in ER/Studio              183

User-Defined Datatypes              183

User-Defined Datatypes in ER/Studio              184

EXERCISE 11.2: Creating User-Defined Datatypes in ER/Studio              185

Reference Values              185

Reference Values in ER/Studio              185

EXERCISE 11.3: Creating Reference Values in ER/Studio              187

Attachments              187

Attachments in ER/Studio              187

Exercise 11.4: Creating Attachments in ER/Studio              190

CHAPTER 12: Data Lineage              191

Using the Data Lineage Tab              192

Defining Source Systems in ER/Studio              194

Creating Data Movement Rules in ER/Studio              199

Creating a Data Flow in ER/Studio              200

Using the Table Editor to Further Document Lineage              204

Using the Column Editor to Further Document Lineage              204

Exercise 12.1: Creating a Data Lineage              204

CHAPTER 13: Macros              205

Running Macros              205

Top 5 Favorite Macros              206

Creating Macros              206

Editing Macros              207

Deleting Macros              208

Renaming Macros              208

Exercise 13.1: Creating and Running Macros              208

CHAPTER 14: Importing, Exporting, Printing, and Reporting              209

Importing into ER/Studio              209

Importing from External Metadata              209

Importing from an ERX File              212

Importing from a SQL File              212

Exporting out of ER/Studio              212

Exporting to External Metadata              213

Exporting to XML              215

Exporting to a SQL file              219

Exporting an Image              219

Printing in ER/Studio              220

Reporting in ER/Studio              221

Exercise 14.1: Importing, Exporting, Printing, and Reporting              224

CHAPTER 15: Naming Standards              225

Creating a Naming Standards Template              225

Applying the Naming Standards Utility              228

Assigning Naming Standards to Objects              228

Exercise 15.1: Creating a Naming Standard Template in ER/Studio              229

CHAPTER 16: Compare/Merge Utility              231

Comparing Models and Submodels in ER/Studio              231

Exercise 16.1: Running the Compare and Merge Utility in ER/Studio              235

APPENDIX A: References              237

APPENDIX B: Answers to Exercises              239

APPENDIX C: Glossary              245

APPENDIX D: ERStudio Commands Quick Reference              255

Index              265