System Architect is known for having the best data modeling capabilities of tools in the Enterprise Architecture space. It has rich support for:
- Conceptual Data Modeling,
- Logical Data Modeling, and
- Physical Data Modeling.
In this article we focus on its Physical Data Modeling capabilities.
Overview
As its name suggests, with Physical Data Modeling you are modeling at the physical layer of the architecture — designing the databases that carry your corporate data. Physical Data Models represent the tables of the database, columns in those tables, and constraints (or relationships) between the tables.
With Physical Data Modeling you model with a particular Database Management System (DBMS) in mind.
Turning On Data Modeling
Data Modeling in System Architect can be utilized by itself, or in conjunction with any Enterprise Architecture framework (such as TOGAF, Archimate, DoDAF 2, UAF, etc). You can turn it on — or off — at any point while modeling by selecting Tools, Customize Method Support, Encyclopedia Configuration.
Target Databases
System Architect provides a Property Set for a number of target databases (see upper right of picture above). Each selection provides tabs of properties particular for the database in the Table, Column, and Constraint (relationship) definition types. You may turn on zero to many of these target databases. For Physical Data Modeling, you would typically choose the DBMS(s) you have in mind.
Re-Open Encyclopedia for Changes to Take Effect
You can at any point open this dialog and toggle on/off “Entity Relation and Physical” and/or “IDEF1X and Physical”, and one or more Target Databases, then re-open the encyclopedia for the changes to take effect. Turning options on or off never deletes information from the encyclopedia — you are just choosing whether to see it or not.
Physical Diagram Name
The Physical Data Model diagram name varies depending on what Framework you have turned on, as follows:
- No Framework: Physical Data Model
- TOGAF: Physical Data Model
- DoDAF 2: DIV-03 Physical Data Model (DM2)
- UAF: Rs-If Resource Information – Physical Model
In all cases it is the same diagram with the same functionality, just different name.
Models = Namespace
When you perform Physical Data Modeling in System Architect, you must specify the Model that you are working in. The Model provides a namespace for the Tables and their relationships. This allows you to, for example, have multiple Table definitions named Customer in an encyclopedia, each one distinct from each other because they are in different Models.
When creating a Physical Data Model diagram, you must also specify the Database Management System (DBMS) type that will be used. You can optionally specify a Database Name — but it is not mandatory.
For a Physical Model, you will typically be selecting the target DBMS you have in mind, such as Oracle or Microsoft SQL Server. The DBMS’s provided in the list are based on the selections you made in the System Architect Properties dialog (above).
Although the American National Standards Institute (ANSI) set of generic types is provided, you would normally be using that for Logical Data Modeling — although the DB Synchronize function does allow you to map a Physical Data Model to another Physical Data Model.
Some definitions exist outside any Model — and are Shared across models. The underlying data dictionary of Data Elements, Data Structures, and Data Domains are definition types that are Shared across models, as are Trigger Templates, and other definitions particular to different DBMS’s.
The Explorer tree in System Architect provides a Data tab — that shows a Data Modeling view of Models and their contents (diagrams and definitions), and Shared definitions and diagrams.
Database Design
The Physical Data Model enables you to design the database that stores your information. This work is traditionally done by Database Architects and Database Administrators (DBA’s).
Tables and Columns
In the Physical Data Model you design the database Tables and their Columns.
Table contains:
- Columns
- Indexes
- Constraints
- Triggers and Synonyms
Column contains:
- Data Type
- Type Qualifiers (Length of the column, and Precision)
- Default Value
- User Data Type (Domain)
- Check Constraints
Constraints = Relationships
You also model Constraints, which create a pathway between Tables — and are equivalent to a relationship between Tables.
Indexes
You may specify Indexes — specific columns that can be used to locate individual records within a database table.
Triggers
Triggers enforce the relationship between a parent Table and its child tables. Triggers are used to enforce business rules during database modification. All triggers are related to specific Tables; a trigger cannot be “free-standing” in the database.
Denormalized Database Designs
Database Architects and Administrators will denormalize a database design to:
- Add ability to search for an item quickly. To do this, they might combine two separate fields of related data into one — for example, combining First and Last Name into Name.
- Ease of data entry. To do this they might design two separate tables to store the same information but at different states; for example, a table for checked out library books and a table for checked in library books.
- Ability to quickly report on or retrieve data. To do this they might combine two tables of unrelated data into one; for example, combine Order information and Customer information into one table.
Generating Schema
System Architect enables you to generate schema from a Physical Data Model for several different DBMS types:
- Oracle
- Microsoft SQL Server
To do so, select Tools, DB Schema Generation, and in the wizard that opens, select the DBMS you wish to generate to — the selection list reflects choices you make in the System Architect Property Configuration dialog (above).
In the Schema Generation wizard, select the Physical Data Model you wish to generate schema for, and the path for the DDL file to be generated to.
The Schema Generation wizard will provide you with numerous options for generation. Make selections and click Finish.
The SQL DDL file will be generated to the path you selected.
Reverse Data Engineering
System Architect enables you to reverse engineer the database design of several DBMS types:
- Oracle — either by reversing SQL files or via a direct connection to the database.
- Microsoft SQL Server — by direct connection to the database.
To reverse engineer an Oracle or Microsoft SQL Server database — first make sure the property set of the database is turned on in the System Architect Property Configuration dialog (above), then select Tools, DB Reverse Engineering.
The Reverse Data Engineering wizard will present numerous choices on what to reverse engineer.
DB Synchronization
System Architect offers a DB Synchronization wizard, that enables you to compare and selectively merge the following:
- Physical Data Model
- Live Database Connection (ADO connection)
- DDL or SQL File
So for example, you can compare and selectively merge:
- Physical Model to Physical Model
- Physical Model to SQL File
- Physical Model to DDL File
- DDL File to Database, etc
The DB Synch wizard is opened by selecting Tools, DB Synchronize.
In the example below, a Physical Data Model is compared to an Oracle SQL file and discrepancies are found — the DEPARTMENTS table in the SQL has Columns and Indexes that are not in the DEPARTMENTS table in the Physical Data Model. By right-mouse-clicking on the item on the right — they are chosen to be mapped into the Physical Data Model in System Architect. This is an example of a selective merge, but entire databases can be reversed in this way.
Mapping Physical Model to/from Logical Model
You can automatically map Physical Data Models to ER Diagrams, and vice versa, via a command in the Dictionary menu.
Reverse Engineering XML Schemas Into Physical Model
You can reverse engineer XML Schemas into a UML Class diagram, and then map the Class diagram to an ER diagram, then map that to a Physical Data Model — — thereby reverse engineering an XML Schema into a Physical Model. You can also go the other way — from a Physical Model to an ER diagram to a Class Diagram, and then generate XML Schema.
Details on doing this are in this related article.
Questions? Comments?
Thank you for reading this article. If you have questions or feedback, please comment below.
Be the first to comment