Models

Conceptual, Logical, and Physical Data Models

There are three levels of data modeling. They are conceptual, logical, and physical. This section will explain the difference among the three, the order with which each one is created, and how to go from one level to the other.

Conceptual Data Model

Features of conceptual data model include:

• Includes the important entities and the relationships among them.

• No attribute is specified.

• No primary key is specified.

At this level, the data modeler attempts to identify the highest-level relationships among the different entities.

Logical Data Model

Features of logical data model include:

• Includes all entities and relationships among them.

• All attributes for each entity are specified.

• The primary key for each entity specified.

• Foreign keys (keys identifying the relationship between different entities) are specified.

• Normalization occurs at this level.

At this level, the data modeler attempts to describe the data in as much detail as possible, without regard to how they will be physically implemented in the database.

In data warehousing, it is common for the conceptual data model and the logical data model to be combined into a single step (deliverable).

The steps for designing the logical data model are as follows:

1. Identify all entities.

2. Specify primary keys for all entities.

3. Find the relationships between different entities.

4. Find all attributes for each entity.

5. Resolve many-to-many relationships.

6. Normalization.

Physical Data Model

Features of physical data model include:

• Specification all tables and columns.

• Foreign keys are used to identify relationships between tables.

• Denormalization may occur based on user requirements.

• Physical considerations may cause the physical data model to be quite different from the logical data model.

At this level, the data modeler will specify how the logical data model will be realized in the...