Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction 
2.
What is DBMS Normalisation? 
3.
Types of DBMS Normal Form
3.1.
First Normal Form (1NF)
3.2.
Second Normal Form (2NF)
3.3.
Third Normal Form (3NF)
3.4.
Boyce-Codd Normal Form(BCNF)
3.5.
Fourth Normal Form (4NF)
4.
Advantages of Normalization
5.
Disadvantages of Normalization
6.
Why do we need normalization in databases?
6.1.
Insertion Anomaly
6.2.
Deletion Anomaly 
6.3.
Updation / Modified Anomaly
7.
Frequently Asked Questions
7.1.
What is 1NF 2NF and 3NF?
7.2.
What are the four 4 types of database normalization?
7.3.
What are the 3 rules in normalizing database?
8.
Conclusion
Last Updated: Mar 27, 2024
Easy

Normalization in DBMS

Introduction 

Normalization in DBMS is an essential and widely used concept as it helps us reduce the redundancy in data present in relations. Through this article, we will try to understand how redundancy impacts databases and how we can minimize it using the concept of normalization in databases.

Normalization in DBMS

Let’s first start with the basics of normalization in dbms,i.e., functional dependency.

What is DBMS Normalisation? 

Normalization in a database is the process in which we organize the given data by minimizing the redundancy present in a relation. In this, we eliminate the anomalies present, namely - update, insertion and deletion. Normalization divides the single table into smaller tables and links them using relationships. The different normal forms help us minimize redundancy in the database table. 

To perform normalization in the database, we decompose the table into multiple tables. This process keeps repeating until we achieve SRP (Single Responsibility Principle). The SRP states that one table should have one role only.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Types of DBMS Normal Form

Normalization in a database is done through a series of normal forms. 

Normal Form Description
1NF If a table has no repeated groups, it is in 1NF.
2NF If a table is in 1NF and every non-key attribute is fully dependent on the primary key, then it is in 2NF.
3NF If a table is in 2NF and has no transitive dependencies, it is in 3NF.
BCNF If a table is in 3NF and every non-prime attribute fully dependent on the candidate keys, then it is in BCNF.
4NF If a table is in BCNF and has no multi-valued dependencies, it is in 4NF.

 

 

These normal forms help to normalize the relations step by step:

First Normal Form (1NF)

In 1NF, every database cell or relation contains an atomic value that can’t be further divided, i.e., the relation shouldn’t have multivalued attributes.

Example:

 

 The following table contains two phone number values for a single attribute.

First Normal Form Example

So to convert it into 1NF, we decompose the table as the following - 

First Normal Form Example

Here, we can notice data repetition, but 1NF doesn’t care about it.

Second Normal Form (2NF)

In 2NF, the relation present should be 1NF, and no partial dependency should exist. Partial dependency is when the non-prime attributes depend entirely on the candidate or primary key, even if the primary key is composite.

 

Example 1: (depicting partial dependency issues) 

If given with a relation R(A, B, C, D) where we have {A, B} as the primary key where A and B can’t be NULL simultaneously, but both can be NULL independently and C, D are non-prime attributes. If B is NULL and we are given the functional dependency, say, B  C. So can this ever hold?

As B contains NULL, it can never determine the value of C. So, as B → C is a partial dependency, it creates a problem. Therefore, the non-prime attributes cannot be determined by a part of the primary key. We can remove the partial dependency present by creating two relations ( the 2NF conversion)- 

Relation 1 = R1(ABD), where {A, B} is the primary key. AB determines D.

Relation 2 = R1(BC), where B is the primary key. And from this, B determines C.

 

Example 2:

Consider the following table. Its primary key is {StudentId, ProjectId}.

The Functional dependencies given are - 

StudentId → StudentName

ProjectId  ProjectName

Second Normal Form Example

As it represents partial dependency, we decompose the table as follows - 

Second Normal Form Example

Here projectId is mentioned in both tables to set up a relationship between them.

Third Normal Form (3NF)

In 3NF, the given relation should be 2NF, and no transitivity dependency should exist, i.e., non-prime attributes should not determine non-prime attributes.

Example:

Consider the following scenario where the functional dependencies are -  

B and B → C, where A is the primary key. 

As here, a non-prime attribute can be determined by a prime attribute, which implies transitivity dependency exists. To remove this, we decompose this and convert it into 3NF. So, we create two relations -

R1(A, B), where A is the primary key and R2(B, C), where B is the primary key.

Boyce-Codd Normal Form(BCNF)

In BCNF, the relation should be in 3NF.If given a relation, say A → B, A should be a super key in this. This implies that no prime attribute should be determined or derived from any other prime or non-prime attribute.

Example:

Given the following table. Its candidate keys are {Student, Teacher} and {Student, Subject}.

The Functional dependencies given are - 

{Student, Teacher} → Subject

{Student, Subject}  Teacher

Teacher → Subject

Boyce-Codd Normal Form Example

As this table is not in BCNF form, so we decompose it into the following tables:

Boyce-Codd Normal Form(BCNF) Example

Here Teacher is mentioned in both tables to set up a relationship between them.

Fourth Normal Form (4NF)

For any relation to be in 4NF, it should have no multi-valued dependencies and is in Boyce Codd Normal Form. It simplifies the database by eliminating the non-trivial multi-valued dependencies besides those including the candidate key.

Example:

Consider the following table:

Fourth Normal Form Example

In the above table, subject and student phone number are two independent entities, showing no relation between subject and phone number. So to convert it in 4NF, we decompose the table as - 

Fourth Normal Form Example

Here Student is mentioned in both tables to set up a relationship between them.

Advantages of Normalization

The following are the advantages of normalization in a database:

  1. The redundancy in data is minimized, leading to a smaller size of the database.
     
  2. It removes the data inconsistency.
     
  3. The database becomes easy to maintain when we organize it using normal forms.
     
  4. It becomes comparatively easier to write queries as the size of the database decreases.
     
  5. Decreased database size further reduces the complexity of sorting and finding any value in the database.

Disadvantages of Normalization

The following are the disadvantages of normalization in database:

  1. Decomposing the table in Normalization can lead to a poorer database design and severe problems.
     
  2. The process of normalization in the database is very time-consuming as we decompose the table repeatedly into different normal forms until we reach the SRP situation.
     
  3. It becomes tough to normalize relations that are of a higher degree.

Why do we need normalization in databases?

Redundancy in data occurs when the same piece of information exists in a single database. Database redundancy can lead to many drawbacks and introduces three anomalies (or abnormalities). These anomalies are-

Insertion Anomaly

This anomaly occurs when specific data cannot be inserted into the table or database due to the absence of some other data where both of these are independent of each other.

Deletion Anomaly 

While deleting some data, when some critical information is lost that was necessary to maintain the integrity of data, it is known as a deletion anomaly. 

Updation / Modified Anomaly

This type of anomaly occurs when a single data has to be updated, but it demands multiple rows of data to be updated. This further leads to data inconsistency if one forgets to update the data in some places.

Due to these anomalies, the storage costs increase as the size of the database increases(because of redundant data), further increasing the database’s complexity and making it more challenging to maintain.

To rectify and address these issues, we need to optimize the given database by using the normalization technique so that no redundant values are present in the database. 

Frequently Asked Questions

What is 1NF 2NF and 3NF?

First Normal Form, or 1NF, removes repeated groups from a table to guarantee atomicity. The Second Normal Form, or 2NF, lessens redundancy by eliminating partial dependencies. In a relational database, the Third Normal Form, or 3NF, reduces data duplication by removing transitive dependencies.

What are the four 4 types of database normalization?

First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF) are the four methods of database normalisation. They enhance data integrity in relational databases by gradually removing redundant data.

What are the 3 rules in normalizing database?

Normalization rules in database design include: 1) Eliminate data redundancy by organizing data into separate tables, 2) Ensure each table has a primary key for unique identification, and 3) Establish relationships between tables using foreign keys for data integrity.

Conclusion

Normalization in the database is a crucial concept that helps minimize data redundancy and further helps organize the database system. In this article, we learned how by decomposing the table into smaller ones using different normal forms, we can remove the anomalies. Further, we learned to remove data inconsistency and improve data integrity.

To learn more about normalization in dbms, we recommend reading the following articles:

You can also consider our DBMS Course to give your career an edge over others!

Happy Learning!

Next article
Need for Normalization
Live masterclass