Open In App

Normalization Process in DBMS

Improve
Improve
Like Article
Like
Save
Share
Report

Pre-Requisite: Introduction to Database Normalization

Database Normalization is a stepwise formal process that allows us to decompose database tables in such a way that both data dependency and update anomalies are minimized. It makes use of functional dependency that exists in the table and the primary key or candidate key in analyzing the tables. Normal forms were initially proposed called

Subsequently, R, Boyce, and E. F. Codd introduced a stronger definition of 3NF called Boyce-Codd Normal Form. With the exception of 1NF, all these normal forms are based on functional dependency among the attributes of a table. Higher normal forms that go beyond BCNF were introduced later such as Fourth Normal Form (4NF) and Fifth Normal Form (5NF). However, these later normal forms deal with situations that are very rare.

Normalization Process in DBMS

Normalization Process in DBMS

Now, we are going to describe the process of Normalization by considering an Example. Let us look into that.

Normalization Process Example

In this example, we will look into that how we can normalize the database with the help of different Normal Forms. We will look into each of the Normal Forms separately in this example.

Consider the table 1 shown below:

Full Name Institute Courses Result
Naveen Kumar IIT Delhi DBMS, OS Pass
Utkarsh Tiwari IIT Bombay CN. COA Fail
Utkarsh Tiwari IIT Kanpur OS Fail

                                                                                                           

Now, we are re-structuring the table according to the 1st Normal Form.

Rules of 1st Normal Form

  • Each table should contain a single value.
  • Each record needs to be unique.

Table 1 after applying 1st Normal Form:

Full Name Institute Subject Result
Naveen Kumar IIT Delhi DBMS Pass
Naveen Kumar IIT Delhi OS Pass
Utkarsh Tiwari IIT Bombay CN Fail
Utkarsh Tiwari IIT Bombay COA Fail
Utkarsh Tiwari IIT Kanpur OS Fail

Consider the table 2 shown below,

Full Name Institute Subject Result
Utkarsh Tiwari IIT Bombay COA Fail
Utkarsh Tiwari IIT Kanpur OS Fail

Here, People having the same name are from different institutes. So, we require Full Name and Institute to Identify a Row of the database. For this, we have to remove Partial Dependency.

Let’s look at the 2nd Normal Form.

Rules of 2nd Normal Form

  • The table should be in 1NF.
  • Primary Key does not functionally dependent on any subset of Candidate Key.

    Table 1

ID Full Name Institute Result
1 Naveen Kumar IIT Delhi Pass
2 Utkarsh Tiwari IIT Bombay Fail
3 Utkarsh Tiwari IIT Kanpur Fail

    Table 2

ID Subject
1 DBMS
1 OS
2 CN
2 COA
3 OS

                                                                                                              

Here, the Id in Table 2 is Foreign Key to the Id in Table 1. Now, we have to remove Transitive Functional Dependency from our Table to Normalize our Database. A Transitive Functional Dependency basically tells us that there is an indirect relationship between functional dependency.

Now, let us look at the 3rd Normal Form.

Rules of 3rd Normal Form

  • The tables should be in 2NF.
  • There will be no Transitive Dependency.

  Table 1

ID Full Name Institute Result_ID
1 Naveen Kumar IIT Delhi 1
2 Utkarsh Tiwari IIT Bombay 2
3 Utkarsh Tiwari IIT Kanpur 2

Table 2

                                                                                                                 

ID Subject
1 DBMS
1 OS
2 CN
2 COA
3 OS

Table 3

Result_ID Result
1 Pass
2 Fail
3 On Hold

 

Finally, Our Database is Normalized. From the above-mentioned example, we have reached our level of Normalization. In fact, there are also some higher forms or next levels of Normalization. Now, we are going to discuss them one by one. 

Other Normal Forms

Boyce-Codd Normal Form (BCNF)

Sometimes, when the database is in the 3rd Normal Form, there exist some anomalies in DBMS, like when more than one Candidate Keys is present in the Database. This has to be removed under BCNF. BCNF is also called 3.5NF.

4th Normal Form

Whenever a Database contains multivalued and independent data in two or more tables, then the database is to be considered in the 4th Normal Form.

5th Normal Form

Whenever a Database Table is not in 4NF, and we cannot divide it into smaller tables keeping our data safe with us, then our Database is in 5th Normal Form.

Summary of Normalization in a Nutshell

Normal Form    Test Remedy (Normalization)
1NF The relation should have no non-atomic attributes or nested relations. Form a name relation for each non-atomic attribute or nested relation.
2NF

For relations where the primary key contains multiple attributes, no non-key 

attributes should be functionally dependent on a part of the primary key.

Decompose and set up a new relation for each partial key with its dependent attributes. 

Make sure to keep a relationship with the original primary key and any attributes that are fully functionally dependent on it.

3NF

The relation should not have a non-key attribute functionally determined by another

non-key attribute (or by a set of non-key attributes) i.e., there should be no 

transitive dependency of a non-key attribute of the primary key.

Decompose and set up a relation that includes the non-key attribute(s) that functionally determine(s) another non-key attribute (s).
BCNF

The relation should not have any attribute in Functional Dependency which is 

non-prime, the attribute that doesn’t occur in any candidate key.

Make sure that the left side of every functional dependency is a candidate key.
4NF

The relation should not have a multi-value dependency means it occurs when 

two attributes of a table are independent of each other but both depend on a 

third attribute.

Decompose the table into two subtables.
5NF The relation should not have join dependency means if a table can be recreated by joining multiple tables and each of the tables has a subset of the attributes of the table, then the table is in Join Dependency. Decompose all the tables into as many as possible numbers in order to avoid dependency.


Last Updated : 06 May, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads