Database Normalization

Data Modeling Procedure #

1. Perform normalization accurately during data modeling.
2. Estimate database capacity.
3. Identify the types of transactions occurring in the database.
4. Perform denormalization based on capacity and transaction types.
5. Adjust history models, PK/FK, supertypes, and subtypes.
6. Validate the data model from a performance perspective.

What is Normalization? #

The primary goal of normalization is to prevent redundant data between tables.

By preventing redundant data, integrity can be maintained, and the storage capacity of the DB can also be reduced.

Normalization stages for decomposing tables are defined, and the normalization stage varies depending on how the tables are decomposed.

Purpose of Normalization #

• Saves space by eliminating redundancy.
• Ensures data integrity, maintaining accuracy and consistency.
• Reduces the possibility of insertion, deletion, and update anomalies.

Characteristics of Normalization #

• Increases the number of joins during data retrieval.
• Improves model flexibility even when business requirements change.
• Clarifies the interpretation of entity meanings.
• Increases the number of tables.
• Enhances model independence.

Types of Normalization #

From 1st Normal Form to 5th Normal Form, but in practice, only up to 3rd Normal Form is typically applied.

1st Normal Form #

• All attributes contain only atomic values.
• Converts M:N relationships to 1:M relationships.

2nd Normal Form #

• All attributes must be fully functionally dependent on the primary key.
• Eliminates partial functional dependencies.
• However, if the 1st Normal Form result has only one primary key, this step is skipped.

3rd Normal Form #

• Eliminates transitive functional dependencies.
• Transitive dependency?: Means that if A -> B and B -> C, then A -> C holds.

After undergoing 3rd Normal Form, it becomes as follows.

BCNF Boyce - Codd Normal Form #

• A normal form where every determinant is a candidate key.
• For a table that has undergone 3rd Normal Form, it involves decomposing the table so that all determinants become candidate keys.

In this table, the primary key is Student ID, Special Lecture Name.

• The primary key determines the Professor.
• The Professor determines the Special Lecture Name.

The problem is that the Professor is a determinant for the Special Lecture Name, but it is not a candidate key.

Therefore, BCNF can be satisfied by separating it into a Special Lecture Application table (Student ID, Professor) and a Special Lecture Professor table (Special Lecture Name, Professor).

4th Normal Form #

• Eliminates multi-valued dependencies.

5th Normal Form #

• Eliminates join dependencies.