What is Normalization and Types of Normalization?

What is Normalization?

Normalization is a process of simplifying the relationship among data elements in a record. It replaces a collection of data in a record structure by another record design which is simpler and more predictable. It converts E-R model into Tables or Relations. There are mainly six types of normalization in Database.

Types of Normalization

Benefits of Normalization:
(i) Normalization reduces Data Redundancy, it is the unnecessary repetition of a field.

(ii) During the process of normalization, we can identify dependencies that causing the problems when deleting or updating.

(iii) Normalization helps in simplifying the structure of the tables.

(iv) Normalization helps to convert the large table into a smaller table that leads to data and table compaction.

Types of Normalization:

In DBMS, There are mainly six types of normalization:

First Normal Form (1NF):

When a table has no repeating groups, then it says First Normal Form (1NF). The repeating columns or fields present in an unauthorized table are removing from the table and put into a separate table. These tables are dependent on the parent table. The key of these tables must also be a part of the parent table so that the parent table and the derived tables can be related to each other.

Example: An Employee table is given below:

1NF table

Second Normal Form (2NF):

If a table, all non-key fields are fully dependent on the whole key then it says Second Normal Form (2NF). It means that each field in a table must depend upon the entire key, they don’t depend upon the combination key, they are a move to another table on whose key they depend. The structure which doesn’t contain combination keys is automatically in the Second Normal Form.

Example: A Student table is given below:

Normalization in DBMS
Here Candidate key is : { student_id, subject}

The non-key attribute is: age
2NF Student table, We break into two tables is given below:
1. student_details table:

Types of Normalization

Third Normal Form (3NF):

If a table, all non-key fields are fully independent of all other non-key fields then it says Third Normal Form (3NF). A relation is in third normal form (3NF) if it holds at least one of the following conditions for every non-trivial function dependency X → Y.

1. X is a super key.
2. Y is a prime attribute, each element of Y is part of some candidate key.

Example: Student table is given below:

3NF

Candidate Key is: {student_id}
3NF Student table, We break into two tables is given below:
1. student table:

student table

Boyce Codd Normal Form (BCNF):

Boyce Codd Normal Form (BCNF) was introduce as an extension of the Third Normal Form (3NF). But it is much more strict than 3NF that means every relation in BCNF is also in 3NF. A table is in BCNF if every functional dependency X → Y, X is the super key of the table.

Example: An Emp Details table is given below:

BCNF

Here Candidate Key is: {Emp_id, Emp_Dept}
Emp Details table, We break into three tables is given below:
1. Emp_Country table:

Boyce Codd table

Fourth Normal Form (4NF):

Fourth Normal Form was introduce as an extension of Boyce Codd Normal Form. It eliminates in which the composite key of a record type contains two or more data items that are independent, multivalued facts of an entity.

Example: An Emp table is given below:

4NF

The given Emp table is in 3NF, but Emp_Dept and HOBBY are two independent entities. Hence, there is no relationship between Emp_Dept and HOBBY.
So, we make the above table into 4NF, We break it into two tables as given below:
1. Emp_id_Dept table:

5NF table

Fifth Normal Form (5NF):

In DBMS, Join dependency is a generalization upon the concept of multivalued dependency. A relation is in Fifth Normal Form, if it is in 4NF, it won’t have lossless decomposition into smaller tables.

Domain-Key Normal Form (DKNF):

The idea behind Domain-Key Normal Form is to specify the ultimate normal form that takes into account all possible types of dependencies and constraints. It doesn’t exhibit insertion and deletion anomalies. DKNF is “If every table has a single theme, then all functional dependencies will be logical consequences of keys. All data value constraints can express as domain constraints.