Home ยป Data Warehouse

What is Data Modelling ? Conceptual, Logical, & Physical Data Models

What is Data Modelling ?

Data Modelling is the process of creating database schema and defining the relationships between tables. A data model is an abstract model that organizes elements of data and standardizes how they relate to one another and to the properties of real-world entities

In this tutorial, you will learn:

Basic components of a Data Model

While creating a data model there are 3 basic tenants that are repetitively used, they are:

  • Entity (or Table): Eg. Customer, Product, Location
  • Attribute: Characteristics or properties of an entity
  • Relationship: Dependency or association between two entities

What are the different stages in Data Modelling Process ?

Data modelling process involves three levels:

  • Conceptual Data Model
  • Logical Data Model
  • Physical Data Model

Conceptual Data Model

A conceptual data modelling involves identifying the entities involved and highest-level relationships between them. Features of conceptual data model include:

  • Identify the important entities and the establish the relationship among them
  • No attributes are specified
  • No primary keys are specified
Conceptual Data Model

Characteristics of a Logical data model:

  • This type of Data Models are designed and developed for a business audience
  • Offers Organisation-wide coverage of the business concepts

Logical Data Model

Logical data models add further information to the conceptual model elements. It defines the structure of the data elements and set the relationships between them

  • Includes all entities and relationships among them
  • All attributes for each entity are specified
  • The primary key for each entity is specified
  • Foreign keys (keys identifying the relationship between different entities) are specified.
  • Normalization occurs at this level.
Logical Data Model

Characteristics of a Logical data model:

  • In a logical data model, primary keys are present, whereas in a conceptual data model, no primary key is present.
  • In a logical data model, all attributes are specified within an entity. No attributes are specified in a conceptual data model.
  • Relationships between entities are specified using primary keys and foreign keys in a logical data model. In a conceptual data model, the relationships are simply stated, not specified, so we simply know that two entities are related, but we do not specify what attributes are used for this relationship.

Physical Data Model

A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. The steps for physical data model design are as follows:

  1. Convert entities into tables.
  2. Convert relationships into foreign keys.
  3. Convert attributes into columns.
  4. Modify the physical data model based on physical constraints / requirements.
Physical Data Model

Characteristics of a Physical data model:

  • Entity names are now table names.
  • Attributes are now column names.
  • Data type for each column is specified. Data types can be different depending on the actual database being used.

Differences between: Conceptual vs Logical vs Physcial Data Models

FeatureConceptualLogicalPhysical
Entity Names
Yes
Yes
  
Entity Relationships
Yes
Yes
  
Attributes  
Yes
  
Primary Keys  
Yes
Yes
Foreign Keys  
Yes
Yes
Table Names    
Yes
Column Names    
Yes
Column Data Types    
Yes


Next Section: OLAP Cubes


SQL.info