Logical Data Modeling — The How To

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 Logical Data Modeling capabilities.

Overview

As its name suggests, Logical Data Modeling enables you to capture a logical view of your data. You depict the logical entities, the data attributes describing those entities, and the relationships between the entities.

The Logical Data Model is considered a denormalized view of the data. Once you have an understanding of the logical map of your data, you may wish to normalize it when designing how it is implemented as a physical database — in some cases breaking up ‘entities’ into one or more discrete tables for faster lookup access to the data.

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.

For Logical Data Modeling, System Architect supports:

• Entity Relation (ER) diagramming using “Crow’s Feet” notation.

• IDEF1X diagramming — the logical data modeling standard invented by the US Air Force in the 1980’s. The Integrated DEFinition standard consisted of 16 views of the enterprise: IDEF0 thru IDEF14 — with there being an IDEF1 (for information design structure) and an IDEF1X (for a data modeling language that addresses database design issues).

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 Entity, Attribute, relationship, and Table definitions. You may turn on zero to many of these target databases.

Re-Open Encyclopedia for Changes to Take Effect

You can at any point open this dialog and toggle on “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.

Models and Subject Areas

When you perform Logical data modeling in System Architect, you must specify the Model that you are working in. The Model provides a namespace for the Entities and their relationships. This allows you to, for example, have multiple entity definitions named Customer in an encyclopedia, each one distinct from each other because they are in different Models.

When creating a Logical data model diagram (either ER or IDEF1X type), you must specify the Model that the diagram will be in, the Database Management System (DBMS) type that will be used, and the type of diagram — either a Model diagram or a Subject Area diagram.

A Model diagram always automatically has all of the Entities of the model on it. As soon as you create an Entity in the Model, System Architect places that Entity on the Model diagram. A Subject Area diagram allows you to model sections of the Model.

For logical data modeling, the American National Standards Institute (ANSI) set of generic types is usually chosen. You can choose the types for a particular DBMS — the DBMS’s provided in the list are based on the selections you made in the System Architect Properties dialog (above).

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.

Types of Entities

There are 3 types of entities that make up a logical data model: Strong (Independent) Entities, Weak (Dependent) Entities, and Associative Entities.

• Strong (Independent) Entity: Exists and always exists even if other Entities are removed from the model. In the example below, the Unmanned Aerial Vehicle (UAV) is a Strong, Independent Entity.
• Weak (Dependent) Entity: Depends on another Entity in the model to exist. If the Entity it depends on is removed from the model, it too is removed. In the example below, the UAV Pod is a Weak, Dependent Entity — if the UAV is removed from the model, the UAV Pod goes away too.
• Associative Entity: Joins two Entities providing information on how they are related; like a Marriage between two people that contains the anniversary date.

The ER “Crow’s Feet” notation and the IDEF1X notation differ in how they notate Entity types as shown in the figure below.

Types of Relationships

When modeling in System Architect, you simply model Entities — they are automatically changed from Strong to Weak to Associative based on the relationship that you draw between them.

You may also specify Cardinality (or Multiplicity) of each relationship. Crow’s Feet notation is shown in the figure below. IDEF1X uses a bullet notation.

Utilizing a Data Dictionary

System Architect allows use of an underlying Data Dictionary of Data Elements and Data Structures as you model. A Data Element is the most elemental piece of data — such as a Customer ID or Order Number. A Data Structure is a grouping of Data Elements — such as an Address, which consists of Street Address, City, State, and Zip Code.

Attributes, Data Elements, & Data Structures

As you type in Attributes to an Entity — for example, a Customer’s ID, First Name, Last Name, Email, etc — you are automatically creating Data Elements in the underlying data dictionary. These Data Elements can be used in other Entities throughout different Models in the Encyclopedia. You may also create Data Structures.

You can click Choices while in the Attribute tab of an Entity to view the underlying Data Dictionary, and select and drag a Data Element or Data Structure into the Entity definition — at which point it becomes an Attribute to the Entity.

Attribute is a local instance of a Data Element or Structure in an Entity. You can rename the attribute to a different name than the underlying Data Element or Structure — for example, rename ID to Customer Number in the Customer entity, and also use ID in the Employee entity, but rename it there to Employee ID.

Data Domains

You may also create Data Domains and assign them to Data Elements, Structures, or Attributes. Once you do, their property values become read only, as they inherit all property values from the Data Domain.

Assigning Primary Keys

As you specify Attributes to Entities, you also determine which are Primary Keys — Attributes that uniquely identify the Entity — which would enable you to quickly look it up. Examples are a Customer ID, Employee ID, or Order Number.

Propagating Foreign Keys

One of the fun terms in the vernacular of Data Architects is the notion of a Foreign Key and Propagation of Foreign Keys to other entities. A Foreign Key is a Primary Key of a related Strong (Independent) Entity that also becomes an Attribute of the Entity — because the Entity is dependent on it (Weak). The Attribute migrates over to the Weak entity as if it is on a trip, and resides there as a Foreign Key as if it is on vacation.

• If the relationship between the Strong and Weak entities is identifying — then the migrating Attribute (Primary Key in the Strong entity) becomes a Primary Key in the Weak entity as well.
• If the relationship is non-identifying, then the Attribute migrates over as simply an Attribute in the Weak entity.

System Architect provides a function to propagate foreign keys — select Data, Update FK’s. An ini session option allows you to set this to automatically happen as you model. By default, the function is set so that you run it by the menu command, as SA is a multi-user tool and propagating keys examines the entire data model — and may cause other users modeling at the same time a slight delay as they model.

In the example below, UAV_ID propagates to UAV_Pod as a Foreign Key and a Primary Key in the UAV_Pod — meaning if you want to look up the UAV_Pod that can fit on the UAV, you can look it up by the UAV_ID in addition to the UAV_Pod_Type.

Normalizing the Data Model

Data Architects perform normalization of the database design by separating entities out — organizing data into entities to reduce redundancy, then denormalize the data by combining tables — typically in the physical model — to improve query performance.

Data Architects have come up with rules of Normalization of logical database designs — called the Normal Forms:

1st Normal Form

• All entities have a key that uniquely identify an instance of that entity (Primary Key (PK)).
• For each entity there must be no repeating groups (each attribute must have a unique value).
• Example: For an entity ITEM, Order Number can’t be an attribute.

2nd Normal Form

• Each non-key attribute must depend on the entire key.
• Example: Passport Number and SSN for an entity PERSON because both uniquely identify a PERSON.

3rd Normal Form

• A non-key attribute must not depend on any other non-key attributes, i.e. if a non key attribute’s value can be determined by the value of another attribute third normal form is violated.

System Architect provides functions to do rules checks against the logical data model for the 1st thru 3rd Normal Forms. You may select Reports, 1st Normal Form Check, and Reports, 2nd and 3rd Normal Form Checks.

When the rules checks run, violations are notated on the model through ghostbuster symbols and an error code, that is spelled out in a report that is produced.

You can select Reports, Clear Errors to remove the ghostbuster symbols.

Using Roles to Control Foreign Keys

You can use Roles to address multiple relationships between two Entities. You assign a Role to a Primary Key to indicate what Role the Entity is playing in the relationship.

In the example below of two relationships between Airport and Flight, one Role provides the Departure Airport Code, another Role provides the Arrival Airport Code.

Modeling Recursive Relationships

Roles come in handy to model Recursive Relationships, wherein an Entity has a relationship to itself. In the example below, a College Course has a requires relationship to itself — the Role of prerequisite is specified on the Primary Key of Course_Number.

Specifying Access Paths

An Access Path (also called an Alternate Key) is a separate storage structure used to enforce uniqueness of data and provide a faster access path to the data – something like a card catalog in a library.

You create Access Paths in the definition of an Entity — specifying the attributes that can be used to locate an individual record that is represented by the Entity.

Using Display Options

System Architect provides numerous ways to display data on entities on logical data models. Right-mouse click on any Entity symbol and choose Display Options to access the choices.

Make sure to make selections on the middle tab — selections made on that tab affect all Entities of a certain type (Strong, Weak, Associative) on the current diagram. Those selections are overridden by selections made in the tab on the far right — which are for a particular symbol chosen.

Mapping Between ER Diagram (Crows Feet) & IDEF1X

You can automatically map IDEF1X data models to ER (Crows Feet notation) data models, and vice versa, via a selection in the Dictionary menu.

Mapping Logical Model to/from Physical Model

You can automatically map ER Diagrams to Physical Data Models, and vice versa, via a command in the Dictionary menu.

Logical Data Model of SA’s Logical Data Modeling

As a summary, we offer you the overall logical data model of System Architect’s Data Modeling support:

Thanks for reading this article. If you have any questions or feedback, please post it below.