Logical Models

This section contains the following topics:

How to Construct a Logical Model

Entity Relationship Diagram

Logical Model Design Validation

Data Model Example

How to Construct a Logical Model

The first step in constructing a logical model is developing the Entity Relationship Diagram (ERD), a high-level data model of a wide business area. An ERD is made up of three main building blocks: entities, attributes, and relationships. If you view a diagram as a graphical language for expressing statements about your business, entities are the nouns, attributes are the adjectives or modifiers, and relationships are the verbs. Building a data model is simply a matter of putting together the right collection of nouns, verbs, and adjectives.

The objective of the ERD is to provide a broad view of business information requirements sufficient to plan for development of the business information system. These models are not very detailed (only major entities are included) and there is not much detail, if any, about attributes. Many-to-many (non-specific) relationships are allowed and keys are generally not included. This is primarily a presentation or discussion model.

An ERD may be divided into subject areas, which are used to define business views or specific areas of interest to individual business functions. Subject areas help reduce larger models into smaller, more manageable subsets of entities that can be more easily defined and maintained.

There are many methods available for developing the ERD. These range from formal modeling sessions to individual interviews with business managers who have responsibility for wide areas.

Entity Relationship Diagram

If you are familiar with a relational database structure, you know that the most fundamental component of a relational database is the table. Tables are used to organize and store information. A table is organized in columns and rows of data. Each row contains a set of facts called an instance of the table.

In a relational database, all data values must also be atomic, which means that each cell in the table can contain only a single fact. There is also a relationship between the tables in the database. Each relationship is represented in an RDBMS by sharing one or more columns in two tables.

Like the tables and columns that make up a physical model of a relational database, an ERD (and all other logical data models) includes equivalent components that let you model the data structures of the business, rather than the database management system. The logical equivalent to a table is an entity, and the logical equivalent to a column is an attribute.

In an ERD, an entity is represented by a box that contains the name of the entity. Entity names are always singular: CUSTOMER not CUSTOMERS, MOVIE not MOVIES, COUNTRY not COUNTRIES. By always using singular nouns, you gain the benefit of a consistent naming standard and facilitate reading the diagram as a set of declarative statements about entity instances.

The figure that follows is one created by a hypothetical video store that needs to track its customers, movies that can be rented or purchased, and rental copies of movies that are in stock in the store.

In an ERD, a relationship is represented by a line drawn between the entities in the model. A relationship between two entities also implies that facts in one entity refer to, or are associated with, facts in another entity. In the previous example, the video store needs to track information about CUSTOMERs and MOVIE RENTAL COPYs. The information in these two entities is related, and this relationship can be expressed in a statement: A CUSTOMER rents one or more MOVIE RENTAL COPYs.

Entities and Attributes Defined

An entity is any person, place, thing, event, or concept about which information is kept. More precisely, an entity is a set or collection of like individual objects called instances. An instance (row) is a single occurrence of a given entity. Each instance must have an identity distinct from all other instances.

In the previous figure, the CUSTOMER entity represents the set of all of the possible customers of a business. Each instance of the CUSTOMER entity is a customer. You can list information for an entity in a sample instance table, as shown in the following table:






Ed Green

Princeton, NJ


Margaret Henley

New Brunswick, NJ


Tomas Perez

Berkeley, CA


Jonathon Walters

New York, NY


Greg Smith

Princeton, NJ

Each instance represents a set of facts about the related entity. In the previous table, each instance of the CUSTOMER entity includes information about the "customer-id," "customer-name," and "customer-address." In a logical model, these properties are called the attributes of an entity. Each attribute captures a single piece of information about the entity.

You can include attributes in an ERD to describe the entities in the model more fully, as shown in the following figure:

Logical Relationships

Relationships represent connections, links, or associations between entities. They are the verbs of a diagram that show how entities relate to each other. Easy-to-understand rules help business professionals validate data constraints and ultimately identify relationship cardinality.

Examples of one-to-many relationships:

In all of these cases, the relationships are chosen so that the connection between the two entities is what is known as one-to-many. This means that one (and only one instance) of the first entity is related or connected to many instances of the second entity. The entity on the one-end is called the parent entity. The entity on the many-end is called the child entity.

Relationships are displayed as a line connecting two entities, with a dot on one end, and a verb phrase written along the line. In the previous examples, the verb phrases are the words inside the brackets, such as <sells>. The following figure shows the relationship between PLANE-FLIGHTs and PASSENGERs on that flight:

Many-to-Many Relationships

A many-to-many relationship, also called a non-specific relationship, represents a situation where an instance in one entity relates to one or more instances in a second entity and an instance in the second entity also relates to one or more instances in the first entity. In the video store example, a many-to-many relationship occurs between a CUSTOMER and a MOVIE COPY. From a conceptual point of view, this many-to-many relationship indicates that:

You typically use many-to-many relationships in a preliminary stage of diagram development, such as in an ERD, and are represented in IDEF1X as a solid line with dots on both ends.

Since a many-to-many relationship can hide other business rules or constraints, they should be fully explored at a later point in the modeling process. For example, sometimes a many-to-many relationship identified in early modeling stages is mislabeled and is actually two one-to-many relationships between related entities. Or, the business must keep additional facts about the many-to-many relationship, such as dates or comments, and the result is that the many-to-many relationship must be replaced by an additional entity to keep these facts. You need to fully discuss all many-to-many relationships during later modeling stages to ensure that the relationship is correctly modeled.

Logical Model Design Validation

Since a data model exposes many of the business rules that describe the area being modeled, reading the relationships helps you validate that the design of the logical model is correct. Verb phrases provide a brief summary of the business rules embodied by relationships. Although they do not precisely describe the rules, verb phrases do provide an initial sense of how the entities are connected.

If you choose your verb phrases correctly, you should be able to read a relationship from the parent to the child using an active verb phrase.


A PLANE FLIGHT <transports> many PASSENGERs.

Verb phrases can also be read from the perspective of the child entity. You can often read from the child entity perspective using passive verb phrases.


Many PASSENGERs <are transported by> a PLANE FLIGHT.

It is a good practice to make sure that each verb phrase in the model results in valid statements. Reading your model back to the business analysts and subject matter experts is one of the primary methods of verifying that it correctly captures the business rules.

Data Model Example

The following model of a database was constructed for a hypothetical video store and appears in the following figure:

The data model of the video store, along with definitions of the objects presented on it, makes the following assertions:

This is a relatively small model, but it says a lot about the video rental store. From it, you get an idea of what a database for the business should look like, and you get a good picture of the business. There are several different types of graphical objects in this diagram. The entities, attributes, and relationships, along with the other symbols, describe our business rules. In the following chapters, you will learn more about what the different graphical objects mean and how to use CA ERwin DM to create your own logical and physical data models.

Copyright © 2009 CA. All rights reserved. Email CA about this topic