Data Modeling And Normalization

Data Modeling:

This activity is performed once we have understood the requirements for the data warehouse/mart. Data modeling refers to creating the structure and relationship of the data for the business rules defined in the requirements. We create the conceptual model followed by logical model followed by physical model. Here we see a progression in the level of detail as we proceed toward the physical model.

Conceptual Model:

Conceptual Model identifies at a high level the subject areas involved. The objective of creating the conceptual model is to identify the scope of the project in terms of the subject areas involved.

Logical Model:

Logical Model identifies the details of the subject areas identified in the conceptual model, and the relationships between the subject areas. Logical model identifies the entities within the subject area and documents in detail the definition of the entity, attributes, candidate keys, data types and the relationship with other entities.

Physical Model:

Physical Model identifies the table structure, Columns within the tables, Primary Keys, data types and size of the columns, constraints on the table and the privileges on the tables. Using the physical model we develop the database objects through the DDL's developed in the physical model.

 

Normalization

This is a technique used in data modeling that emphasizes on avoiding storing the same data element at multiple places. We follow the 3 rules of normalization called the First Normal Form, Second Normal Form, and Third Normal Form to achieve a normalized data model. A normalized data model may result in many tables/entities having multiple levels of relationships, example table1 related to table2, table2 further related to table3, table3 related to table 4 and so on.

First Normal Form – The attributes of the entity must be atomic and must depend on the Key.
Second Normal Form – This rule demands that every aspect of each and every attribute depends on Key.
Third Normal Form (3NF) – This rule demands that every aspect of each and every attributes depends on nothing but the key.

Theoretically We have further rules called the Boyce-Codd Normal Form, Fourth Normal Form and the Fifth Normal form. In practice we don’t use the rules beyond 3NF.