Open In App
Related Articles

Normalization Process in DBMS

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
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 NameInstituteCoursesResult
Naveen KumarIIT DelhiDBMS, OSPass
Utkarsh TiwariIIT BombayCN. COAFail
Utkarsh TiwariIIT KanpurOSFail

                                                                                                           

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 NameInstituteSubjectResult
Naveen KumarIIT DelhiDBMSPass
Naveen KumarIIT DelhiOSPass
Utkarsh TiwariIIT BombayCNFail
Utkarsh TiwariIIT BombayCOAFail
Utkarsh TiwariIIT KanpurOSFail

Consider the table 2 shown below,

Full NameInstituteSubjectResult
Utkarsh TiwariIIT BombayCOAFail
Utkarsh TiwariIIT KanpurOSFail

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

IDFull NameInstituteResult
1Naveen KumarIIT DelhiPass
2Utkarsh TiwariIIT BombayFail
3Utkarsh TiwariIIT KanpurFail

    Table 2

IDSubject
1DBMS
1OS
2CN
2COA
3OS

                                                                                                              

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

IDFull NameInstituteResult_ID
1Naveen KumarIIT Delhi1
2Utkarsh TiwariIIT Bombay2
3Utkarsh TiwariIIT Kanpur2

Table 2

                                                                                                                 

IDSubject
1DBMS
1OS
2CN
2COA
3OS

Table 3

Result_IDResult
1Pass
2Fail
3On 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   TestRemedy (Normalization)
1NFThe 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.
5NFThe 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