'Coding has over 700 languages', '67% of programming jobs aren’t in the technology industry', 'Coding is behind almost everything that is powered by electricity', 'Knowing how to code is a major requirement for astronomers', 'The first computer didn’t use any electricity', 'Do you know there is a coding language named “Go“', 'Computer programming is one of the fastest-growing careers', 'Fortran (FORmula TRANslation) was the name of the first programming language', 'The first programmer was the daughter of a mad poet', 'Many programming languages share the same structure', 'Coding will soon be as important as reading', 'How many programmers does it take to change a light bulb? None, that’s a hardware problem', 'Why do Java developers wear glasses? Because they can’t C', 'Software and temples are much the same — first we build them, then we pray', 'An engineer will not call it a bug — it’s an undocumented feature', 'In a room full of top software designers, if two agree on the same thing, that’s a majority', 'C programmers never die. They are just cast into void', 'Knock, knock … Who’s there? … *very long pause* … Java', 'The best thing about a boolean is even if you are wrong, you are only off by a bit', 'Linux is only free if your time has no value', 'The computer was born to solve problems that did not exist before', 'Coding has over 700 languages', '67% of programming jobs aren’t in the technology industry', 'Coding is behind almost everything that is powered by electricity', 'Knowing how to code is a major requirement for astronomers', 'The first computer didn’t use any electricity', 'Do you know there is a coding language named “Go“', 'Computer programming is one of the fastest-growing careers', 'Fortran (FORmula TRANslation) was the name of the first programming language', 'The first programmer was the daughter of a mad poet', 'Many programming languages share the same structure', 'Coding will soon be as important as reading', 'How many programmers does it take to change a light bulb? None, that’s a hardware problem', 'Why do Java developers wear glasses? Because they can’t C', 'Software and temples are much the same — first we build them, then we pray', 'An engineer will not call it a bug — it’s an undocumented feature', 'In a room full of top software designers, if two agree on the same thing, that’s a majority', 'C programmers never die. They are just cast into void', 'Knock, knock … Who’s there? … *very long pause* … Java', 'The best thing about a boolean is even if you are wrong, you are only off by a bit', 'Linux is only free if your time has no value', 'The computer was born to solve problems that did not exist before',
Update appNew update is available. Click here to update.
Last Updated: Jan 19, 2024
Easy

Normalization in DBMS

gp-icon
Dbms - Database management systems
Free guided path
12 chapters
93+ problems
gp-badge
Earn badges and level up

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 FormDescription
1NFIf a table has no repeated groups, it is in 1NF.
2NFIf a table is in 1NF and every non-key attribute is fully dependent on the primary key, then it is in 2NF.
3NFIf a table is in 2NF and has no transitive dependencies, it is in 3NF.
BCNFIf a table is in 3NF and every non-prime attribute fully dependent on the candidate keys, then it is in BCNF.
4NFIf 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
Guided path
Free
gridgp-icon
Dbms - Database management systems
12 chapters
93+ Problems
gp-badge
Earn badges and level up
Live masterclass