ER Studio Data Architect - Embarcadero Technologies Product

678 Pages·2015·5.98 MB·English

Product Documentation

ER Studio Data Architect
User Guide

Version 10.0.2
© 2015 Embarcadero Technologies, Inc. Embarcadero, the Embarcadero Technologies
logos, and all other Embarcadero Technologies product or service names are
trademarks or registered trademarks of Embarcadero Technologies, Inc. All other
trademarks are property of their respective owners.
Embarcadero Technologies, Inc. is a leading provider of award-winning tools for
application developers and database professionals so they can design systems right,
build them faster and run them better, regardless of their platform or programming
language. Ninety of the Fortune 100 and an active community of more than three million
users worldwide rely on Embarcadero products to increase productivity, reduce costs,
simplify change management and compliance and accelerate innovation. The
company's flagship tools include: Embarcadero® Change Manager™, CodeGear™ RAD
Studio, DBArtisan®, Delphi®, ER/Studio®, JBuilder® and Rapid SQL®. Founded in
1993, Embarcadero is headquartered in San Francisco, with offices located around the
world. Embarcadero is online at www.embarcadero.com.
March, 2015

Embarcadero Technologies 2
C
ONTENTS
Before you Start ..................................................................................................................... 24
Introducing ER/Studio Data Architect ............................................................................. 24
Notice for Developer Edition Users ................................................................................. 24
Important Name Change Notice ..................................................................................... 25
Product Benefits by Audience .......................................................................................... 25
Overview ................................................................................................................................ 26
ER/Studio Family Products ............................................................................................ 26
Application Design ........................................................................................................ 27
Application Interface ............................................................................................................. 28
Data Model Explorer ............................................................................................................. 31
Data Model..................................................................................................................... 31
Data Dictionary .............................................................................................................. 32
Data Lineage .................................................................................................................. 33
Macros ............................................................................................................................ 34
Data Model Window ............................................................................................................. 35
Data Lineage Window ........................................................................................................... 36
Pop-up Windows ................................................................................................................... 37
Zoom Window ....................................................................................................................... 38
Overview Window ................................................................................................................. 39
Menus ..................................................................................................................................... 40
Application Menus ......................................................................................................... 40
Accessing Shortcut Menus ............................................................................................ 40
Toolbars ................................................................................................................................. 41
Moving Toolbars ............................................................................................................ 41
Undock a Toolbar .......................................................................................................... 41
Dock a Toolbar ............................................................................................................... 41
Displaying Toolbars ....................................................................................................... 41
Keyboard Commands ........................................................................................................... 42
Basic Functions .............................................................................................................. 42
Function Keys ................................................................................................................. 42
Shortcuts ......................................................................................................................... 42
Hot Keys ......................................................................................................................... 43
Full Undo Redo .............................................................................................................. 43
Status Bar ............................................................................................................................... 44
Configuring and Customizing .............................................................................................. 45
Customizing the Display of Diagrams and Objects............................................................ 46
Diagram tab ................................................................................................................... 47
Entity/Table tab ............................................................................................................. 47
Relationship tab ............................................................................................................. 48
View tab .......................................................................................................................... 48
Schema Objects tab ...................................................................................................... 49
Transformation tab ........................................................................................................ 49
Drawing Shapes tab ...................................................................................................... 49

Embarcadero Technologies 3
Security Objects tab ...................................................................................................... 49
Apply To tab ................................................................................................................... 49
Defining Model Options for the Selected Model ............................................................... 50
General Options tab ...................................................................................................... 50
Name Handling Options tab ........................................................................................ 51
Customizing Standard Model Features for New Models .................................................. 53
Application tab .............................................................................................................. 53
Logical tab ...................................................................................................................... 54
Physical tab ..................................................................................................................... 54
Name Handling tab ....................................................................................................... 55
Display tab ...................................................................................................................... 56
Directories tab................................................................................................................ 57
Tools tab ......................................................................................................................... 57
Diagram tab ................................................................................................................... 57
View tab .......................................................................................................................... 58
Schema Objects tab ...................................................................................................... 58
Object Types tab ........................................................................................................... 59
Object Names tab ......................................................................................................... 59
Automation Options tab ............................................................................................... 59
Data Dictionary tab ........................................................................................................ 59
Comments tab ............................................................................................................... 60
Where Used tab ............................................................................................................. 60
ERX File Import tab ........................................................................................................ 60
Undo tab ......................................................................................................................... 60
Repository Options tab ................................................................................................. 60
Specifying the Location of Shared Data .............................................................................. 63
Changing Cursor Popup Help Options ............................................................................... 64
Data Modeling Fundamentals .............................................................................................. 65
Data Modeling Concepts ..................................................................................................... 66
The Logical Model ......................................................................................................... 66
The Physical Design ....................................................................................................... 66
Data Model Design Principals ...................................................................................... 66
The Purpose of a Data Model ....................................................................................... 66
The Relational Model .................................................................................................... 67
The Entity-Relationship Model ..................................................................................... 67
The Dimensional Model ................................................................................................ 68
Developing a Data Model .................................................................................................... 69
Starting With a Basic Data Model ................................................................................ 69
Creating a Basic Data Model ........................................................................................ 69
Reverse Engineering a Database ................................................................................. 69
Importing a File to Create a New Database ................................................................ 69
Ensuring Data Integrity .................................................................................................. 69
Automating Data Model Creation and Maintenance ................................................. 70
Fine Tuning the Data Model ......................................................................................... 70
Securing the Data Model .............................................................................................. 70

Embarcadero Technologies 4
Preparing for Review and Implementation .................................................................. 70
Maintaining the Data Model ......................................................................................... 71
Using ER/Studio Data Architect ........................................................................................... 72
Creating and Working With Data Models ........................................................................... 73
Creating a New, Blank Data Model ..................................................................................... 74
Reverse Engineering an Existing Database ........................................................................ 75
Page 1 ............................................................................................................................. 75
Page 2 ............................................................................................................................. 77
Page 3 ............................................................................................................................. 77
Page 4 ............................................................................................................................. 78
Page 5 ............................................................................................................................. 79
Notes .............................................................................................................................. 79
Specifying Application Options for Reverse Engineering .......................................... 80
MongoDB Advanced Login Options ................................................................................... 81
Authentication ................................................................................................................... 81
Connection Options .......................................................................................................... 82
Importing a Model ................................................................................................................ 83
Importing a Model from External Metadata ....................................................................... 84
Importing a Model from a SQL File ..................................................................................... 87
Importing a Model from an ERX File ................................................................................... 89
Generating a Physical Data Model ...................................................................................... 90
Creating a New Physical Model.................................................................................... 91
Using the Compare and Merge Utility ................................................................................. 92
What Can the Compare/Merge Utility Do? ..................................................................... 92
Comparing Models and Submodels ................................................................................ 94
Page 1 ............................................................................................................................. 95
Page 2 ............................................................................................................................. 95
Page 3 ............................................................................................................................. 96
Page 4 - Select Specific Model Objects ...................................................................... 97
Page 5 - Results .............................................................................................................. 97
Forcing a Match ................................................................................................................. 98
Create a new diagram with one entity called Entity1 with a primary key of A. ........ 98
Updating an Offline DBMS ............................................................................................. 102
Updating a Model or Submodel .................................................................................... 102
Resolving and Reporting on Model Differences ........................................................... 103
Changing the Database Platform of a Model ................................................................... 105
Common Tasks .................................................................................................................... 106
Using Toolbar Tools ............................................................................................................ 107
Creating a Data Model Object ........................................................................................... 108
Moving Objects ................................................................................................................... 109
Moving Objects in the Data Model Window ............................................................. 109
Moving Data Model Objects ...................................................................................... 109
Moving Relationship Docking Points ......................................................................... 110
Moving Data Model Objects in the Data Model Explorer ....................................... 110
Moving Objects to Different Nodes .......................................................................... 110

Embarcadero Technologies 5
Moving Submodels ...................................................................................................... 110
Copying Objects.................................................................................................................. 112
Copying an Object in the Data Model Window ....................................................... 112
Copying Multiple Objects ........................................................................................... 113
Copying an Attribute ................................................................................................... 113
Copying an Object from the Data Model Explorer to the Data Model Window ... 113
Copying Objects Between Models and Submodels ................................................ 114
Resizing Objects .................................................................................................................. 116
Manually Resize Data Model Objects ........................................................................ 116
Automatically Resize Data Model Objects to Fit Text .............................................. 116
Changing Model Alignment, Distribution, and Layout .................................................... 117
Finding an Entity, Table or View ........................................................................................ 118
Locating an Object in the Data Model Window ............................................................... 119
Editing an Object ................................................................................................................ 120
Adding and Editing Object Comments ............................................................................ 121
Editing the Name of an Object .......................................................................................... 123
Deleting an Object .............................................................................................................. 124
Renaming an Object ........................................................................................................... 125
Searching for and Changing Object or Object Attribute Names ................................... 126
Creating and Editing Database Views ............................................................................... 128
Notes ................................................................................................................................ 129
Entity/Table tab ............................................................................................................... 130
Attribute/Column tab ...................................................................................................... 130
Column Alias dialog ........................................................................................................ 131
Where tab ......................................................................................................................... 131
Group By tab .................................................................................................................... 131
Having tab ........................................................................................................................ 131
DDL tab ............................................................................................................................ 131
SQL Validation dialog ..................................................................................................... 132
Options tab ...................................................................................................................... 132
Definition tab ................................................................................................................... 132
Notes tab .......................................................................................................................... 132
Where Used tab ............................................................................................................... 132
Dependencies tab ........................................................................................................... 132
PreSQL & PostSQL tab .................................................................................................... 132
Permissions tab ................................................................................................................ 132
Security Information tab .................................................................................................. 133
Attachment Bindings tab ................................................................................................ 133
Defining the Default View Settings for New Models ........................................................ 134
Customize the View Settings for a Specific Model ........................................................... 135
Creating and Editing Submodels ....................................................................................... 136
Edit Submodel tab........................................................................................................... 136
Attributes tab ................................................................................................................... 137
Definition tab ................................................................................................................... 137
Attachment Bindings tab ................................................................................................ 137

Embarcadero Technologies 6
Security Information tab .................................................................................................. 137
Notes ................................................................................................................................ 137
Synchronizing Submodels .................................................................................................. 139
Example 1 ..................................................................................................................... 139
Example 2 ..................................................................................................................... 139
Example 3 ..................................................................................................................... 139
Using the Submodel Synchronization Utility ..................................................................... 141
Page 1 ........................................................................................................................... 141
Page 2 ........................................................................................................................... 141
Page 3 ........................................................................................................................... 141
Page 4 ........................................................................................................................... 141
Notes ............................................................................................................................ 142
Copying a Database Object to a Submodel (Apply to Submodel)................................. 143
Establishing Database Security .......................................................................................... 144
Creating and Editing Database Users ............................................................................... 145
General page/tab ............................................................................................................ 145
System Privileges page/tab ............................................................................................ 146
Object Privileges page/tab ............................................................................................. 146
Dependencies page/tab ................................................................................................. 146
Definition page/tab ......................................................................................................... 146
DDL tab ............................................................................................................................ 146
Attachment Bindings tab ................................................................................................ 147
Notes ................................................................................................................................ 147
Creating and Editing Database Roles ............................................................................... 148
General page/tab ............................................................................................................ 148
System Privileges page/tab ............................................................................................ 149
Object Privileges page/tab ............................................................................................. 149
Dependencies page/tab ................................................................................................. 149
Definition page/tab ......................................................................................................... 149
DDL tab ............................................................................................................................ 150
Attachment Bindings tab ................................................................................................ 150
Notes ................................................................................................................................ 150
Associating Database Roles with New Database Users ................................................... 151
Associating Database Users with New Database Roles ................................................... 152
Changing Database Roles Associated with Existing Database Users ............................ 153
Changing Database Users Associated with Existing Database Roles ............................ 154
Granting and Revoking Permissions to Modify Database Objects ................................. 155
Comparing Models ............................................................................................................. 156
Customizing the Data Model ............................................................................................. 157
Setting Diagram Properties ................................................................................................ 158
Attachment Bindings tab ............................................................................................ 158
Setting Data Model Properties .......................................................................................... 159
Security Information tab .............................................................................................. 159
Attachment Bindings tab ............................................................................................ 159
PreSQL & PostSQL tab ................................................................................................ 160

Embarcadero Technologies 7
Name Handling tab ..................................................................................................... 160
Creating Entity Relational or Dimensional Models .......................................................... 161
What is a Dimensional Model? ................................................................................... 161
Dimensional Model Table Type Determination ........................................................ 161
Dimensional Model Table Types ................................................................................ 162
Dimensional Table Types Indepth ..................................................................................... 164
Fact ............................................................................................................................... 164
Dimension .................................................................................................................... 165
Snowflake ..................................................................................................................... 167
Bridge ........................................................................................................................... 168
Hierarchy Navigation ................................................................................................... 168
Undefined ..................................................................................................................... 168
Changing the Model Notation ........................................................................................... 169
Setting the Relationship Notation ..................................................................................... 170
IDEF1X versus IE Relationship Notation .................................................................... 170
IDEF1X Notation .......................................................................................................... 170
IE (James Martin) Notation ......................................................................................... 171
IE (Crow's Feet) Notation ............................................................................................ 172
Filtered ID (Hide Foreign Keys) Notation .................................................................. 173
Changing the Relationship Notation for all Diagram Models ................................. 174
Changing the Relationship Notation for a Selected Model .................................... 174
Manipulating Relationship Lines ........................................................................................ 175
Changing the Style of Relationship Lines .................................................................. 175
Create an N Bend ........................................................................................................ 176
Straightening an N Bend ............................................................................................ 177
Change the Disposition of Relationship Lines .......................................................... 177
Changing the Model Background ..................................................................................... 179
Change the Background and Outline Colors of Existing Models and Views ................. 180
Setting Default Diagram Colors and Fonts ....................................................................... 181
Overriding Color and Font Settings for a Specific Object ............................................... 182
Change Color and Font Settings for an Entity, Table, Attribute, Column or View 182
Change Entity/Table Background and Outline Colors............................................. 183
Change the Background and Outline Colors of Views in an Open Data Model ... 183
Changing the Color of Relationships ......................................................................... 184
Change Data Model Display Preferences ......................................................................... 185
Changing Entity/Table Display Preferences ..................................................................... 186
Customizing the Layout ...................................................................................................... 187
All tabs .......................................................................................................................... 187
Disconnected tab ......................................................................................................... 188
Circular tab ................................................................................................................... 188
Hierarchical tab ............................................................................................................ 188
Orthogonal tab ............................................................................................................ 189
Symmetric tab .............................................................................................................. 189
Tree tab ........................................................................................................................ 189
Creating and Editing Shapes ............................................................................................. 190

Embarcadero Technologies 8
Shape Text tab ............................................................................................................. 191
Colors and Fonts tab ................................................................................................... 191
Attachment Bindings tab ............................................................................................ 191
Locking Data Model Objects to a Drawing Shape ........................................................... 192
Adding and Editing Lines ................................................................................................... 193
Properties tab ............................................................................................................... 193
Attachment Bindings tab ............................................................................................ 193
Notes ............................................................................................................................ 193
Adding and Editing Title Blocks......................................................................................... 195
Adding Text Blocks ............................................................................................................. 196
Changing Data Model Layout ............................................................................................ 197
Redraw a Data Model Using Auto Layout ................................................................. 197
Notes ............................................................................................................................ 197
Enforcing Data Integrity Using Check Constraints ........................................................... 199
Checking Out a Concurrent License for Offline Usage .................................................... 200
Developing the Logical Model ........................................................................................... 201
Logical Design Concepts .................................................................................................... 202
Logical Design Documentation .......................................................................................... 203
Object Name Rules ............................................................................................................. 204
Normalization....................................................................................................................... 205
Creating and Editing Entities and Tables.......................................................................... 207
Creating and Editing Entities ............................................................................................. 208
All tabs .............................................................................................................................. 208
Attributes/Columns tab .................................................................................................. 209
Keys tab ............................................................................................................................ 209
Relationships tab ............................................................................................................. 209
Definition tab ................................................................................................................... 209
Note tab ........................................................................................................................... 209
Where Used tab ............................................................................................................... 209
User-Defined Mappings tab ........................................................................................... 210
Constraints tab ................................................................................................................. 210
Dependencies tab ........................................................................................................... 210
Permissions tab ................................................................................................................ 210
Naming Standards tab .................................................................................................... 210
Data Lineage tab ............................................................................................................. 211
Security Information tab .................................................................................................. 211
Attachment Bindings tab ................................................................................................ 211
Notes ................................................................................................................................ 211
Creating and Editing Tables ............................................................................................... 213
Columns tab ..................................................................................................................... 213
Dimensional tab ............................................................................................................... 213
DDL tab ............................................................................................................................ 214
Indexes tab ....................................................................................................................... 214
Foreign Keys tab .............................................................................................................. 214
Definition tab ................................................................................................................... 214

Embarcadero Technologies 9
Note tab ........................................................................................................................... 214
Where Used tab ............................................................................................................... 214
User-Defined Mappings tab ........................................................................................... 214
Storage tab ...................................................................................................................... 215
Dimensions tab ................................................................................................................ 215
Properties tab .................................................................................................................. 215
Partition Columns tab ...................................................................................................... 215
Distribute Columns tab ................................................................................................... 216
Distribution tab ................................................................................................................ 216
Organization tab .............................................................................................................. 216
Partitions tab .................................................................................................................... 216
Overflow tab..................................................................................................................... 216
Constraints tab ................................................................................................................. 216
Dependencies tab ........................................................................................................... 216
Capacity Planning tab ..................................................................................................... 217
Permissions tab ................................................................................................................ 217
PreSQL & Post SQL tab ................................................................................................... 217
Naming Standards tab .................................................................................................... 217
Compare Options tab ..................................................................................................... 217
Data Lineage tab ............................................................................................................. 217
Security Information tab .................................................................................................. 218
Attachment Bindings tab ................................................................................................ 218
Notes ................................................................................................................................ 218
Creating and Editing Attributes and Columns ................................................................. 220
Creating and Editing Attributes ......................................................................................... 221
Main page ........................................................................................................................ 221
Datatype tab .................................................................................................................... 222
Default tab........................................................................................................................ 223
Rule/Constraint tab ......................................................................................................... 223
Definition tab ................................................................................................................... 223
Notes tab .......................................................................................................................... 223
Where Used tab ............................................................................................................... 223
User-Defined Mappings tab ........................................................................................... 223
Reference Values tab ....................................................................................................... 224
Naming Standards tab .................................................................................................... 224
Compare Options tab ..................................................................................................... 224
Data Lineage tab ............................................................................................................. 224
Security Information tab .................................................................................................. 224
Attachment Bindings tab ................................................................................................ 224
Data Movement Rules tab .............................................................................................. 225
Notes ................................................................................................................................ 225
Creating and Editing Columns ........................................................................................... 227
Main page ........................................................................................................................ 227
Datatype tab .................................................................................................................... 228
Default tab........................................................................................................................ 232

Embarcadero Technologies 10