Normalization Forms in DBMS with example

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 coverts E-R model into Tables or Relations.
Normalization Forms
Benefits of Normalization Forms in DBMS :

(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.

Types of Normalization Forms in DBMS:

First Normal Form (1NF) :

When a table has no repeating groups, then it said to be First Normal Form (1NF). The repeating columns or fields present in an unauthorized table are removed from the table and put into a separate table. These tables are dependent on the parent table from which is derived. 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 is said to be 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 on. 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:

2NF

Third Normal Form (3NF) :

If a table, all non-key fields are fully independent of all other non-key fields then it is said to be Third Normal Form (3NF).

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 proposed as a simpler form of Third Normal Form (3NF). But it is much more strict than 3NF that means every relation in BCNF is also in 3NF.

Example:

BCNF

Here Candidate Key is : {Emp_id, Emp_Dept}
BCNF 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 introduced 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:
Emp table is given below:

4NF

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

5NF table

Fifth Normal Form (5NF) :

Multivalued dependencies help us understand and tackle some forms of repetition of information that can’t be understood in terms of functional dependencies. These type of constraints called join dependencies that generalized the multivalued dependencies and lead to another normal form that is called Fifth Normal Form (5NF).

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. It is considered to be the highest form of Normalization.