Data Modeling

What is data modelling?

A date model is a conceptual representation of business requirement (logical data model) or database objects (physical) required for a database and are very powerful in expression and communicating the business requirements and database objects. The approach by which data models are created in called as data modelling.

What does data model contain?

Logical Data Model: Entity, Attributes, Super Type, Sub Type, Primary Key, Alternative Key, Inversion Key Entry, Rule, Relationship, Definition, business rule etc.

Physical Data Model: Table, Column, Primary Key Constraint, Unique Constraint or Unique Index, Non Unique Index, Check Constraint, Default Value, Foreign Key, Comment etc.

There are three levels of data modelling. They are conceptual, logical and physical. 

The difference among the three is 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 modeller attempts to identify the highest-level relationship among the different entities.

Logical Data Model:

Features of the 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 modeller 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 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.
• Demoralization 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 modeller will specify how the logical data model will be realized in the database schema.

The steps for physical data model design are as follows:

1. Convert entities into tables.
2. Covert relationships into foreign keys.
3. Convert attributes into columns.

7. Modelling is an efficient and effective way to represent the organization's needs, It provides information in a graphical way to the members of an organization to understand and communicate the business rules and processes. Business Modelling and Data Modelling are the two important types of modelling.

The different between a logical and physical data model:

Logical Data Model	Physical Data  Model
Represents business information and defines business rules	Represents the physical implementation of the model in a database
Entity	Table
Attribute	Column
Primary Key	Primary Key Constraint
Alternate Key	Unique Constraint or Unique Index
Inversion Key Entry	Non Unique Index
Rule	Check Constraint, Default Value
Relationship	Foreign Key
Definition	Comment