• Normalization in DBMS is a systematic approach used to reduce the data redundancy in DBMS.
• In Normalization approach given table is decomposed in to two or more table to reduce the data redundancy.

Database normalization in DBMS is also an essential topic for GATE(CS/IT) and UGC NET exam. Students preparing for the GATE exam are kindly requested to prepare this topic very well to score good marks.

Questions on database normalization are asked every year in the GATE exam.

Questions on Normalization in DBMS are generally asked in a technical interview for a software developer or database administrator.

We will discuss database normalization based questions for the GATE and UGC NET Exam in a separate tutorial.

This “Normalization in DBMS” tutorial is written for computer science students. In this tutorial, we will study functional Dependency in DBMS, the introduction of Normalization in DBMS.

Advantages of Database Normalization and separate database standard forms are also explained in this Normalization in DBMS based tutorial.

Frequently Asked Questions

Frequently Asked Questions

By the end of this tutorial, students will be able to answer the following database normalization interview questions.

• What is database normalization?
• What are the benefits of database normalization?
• Database normal forms with example.
• Difference between 3 NF and BCNF.
• Redundancy and anomaly in database.
• What are the different types of anomalies in the database?
• Example for anomalies.
• What is multi-value Dependency?

Normalization in DBMS

• Normalization makes database design simpler.
• Normalization in DBMS is an important part of database design.
• It helps in the speed, accuracy of the database.
• Normalization in DBMS is a systematic approach used to design a relational database.
• Normalization occurs in two processes, which removes the repeating groups from the data in the form of a table.
• Normalization also removes duplicate entries of the same from the relational table.

The work of Database Normalization is to reduce redundancy. Reducing redundancy means to store information at once and to remove data duplicacy. Storing the same information more than once can increase the storage. This is the primary need of Normalization in DBMS.

Relative Normalized means that the information should not be lost whenever the Relation is altered in the database.

By normalizing the database, we can arrange the data into tables and columns it means in the form of a relational model. You make sure that each table has only related data. If the data is not directly related, then you create a new table for that data.

In Normalization, we decompose the given Relation to more than one relationship in order to remove the redundancy. This decomposition should be so that the functional Dependency given in the original table should persist after decomposition.

Decomposition is of two types lossless decomposition and lossy decomposition.

Database Normalization Example

If we have a “Customer” table, you can usually create a separate table for those products they can order, and we can give the name “product” to this table.

You will prepare another table for customer orders, and we can give the name “order” to this table. If there are many items in each order, you usually create another table (perhaps called “order_list”) to store each order item.

All these tables will be linked to their primary key, which allows you to find related data in all these tables (such as all orders by a given customer).

So in this normalization example, we decomposed the Customer relation or table into three sub-tables named product, order and order_list.

Notes Point: In a relation, the Primary key is used to uniquely identify a record in a table or Relation. The primary key is chosen by the database designer, and it used to denote the candidate key.

Benefits of Normalization in DBMS

These are the following benefits of Normalization in DBMS. 

• Redundancy Minimization
• Performance Improvement.
• Query Optimization
• Minimizing insertion, deletion and updation anomalies in DBMS

Database Normal Forms

Database normal forms or normal forms in dbms are used to reduce redundancy from the database table. We decompose the given relation in to more than one relation.

After decomposition the resultant relations are said to be in a specific normal form.

Let’s see different normal forms in dbms with example one by one.

1.First Normal Form (1NF)

First Normal Form also represented as 1NF in a relational database. A relation or table is said to be in first normal form when all the values in the column are atomic.

All attributes of the relation should have atomic values. That means it does not contain repeating values.

We can understand it in such a way that a table is in 1 NF if- There should be no duplicate rows in the table.

Every single cell should have single values. Entries should be of the same type in the column.

Here first normal form in dbms with example is explained.

Consider the following table.

Here in this table issue is that here we are storing two courses against Dr. L.S.Maurya. This is not the optimal way.This table is not in 1 NF. Better way is that we have to store each subject separately as shown in following table.

This table also store the unique information there is no repetition so this table is in 1 NF.

2.Second Normal Form (2NF)

A table or relation is then in 2nd normal form when it meets all the needs of 1st normal form and all non key attributes will depend entirely on the primary key.

Here 2nd normal form in dbms with example is explained.

Consider the following PROFESSOR table

Since a teacher can teach more than one subject so In this table candidate key is the combination of teacher id and subject code. But teacher age is the non prime attribute which is dependent on the professor Id.

Which is a part of candidate key so here is partial Dependency which is not allow in 2 NF. So we need to decompose this table in order to convert it in to 2 NF. This decomposition is as follows:

PROFESSOR_SUBJECT

PROFESSOR_AGE

Now this resultant tables in second normal forms(2nf) .

3. Third Normal Form (3NF)

A table or Relation is said to be in 3rd normal form when it meets all the requirements of 2nd normal form, and there should not be any transitive functional dependency.

If there is transitive functional Dependency, we decompose the given Relation and remove the transition Dependency.

Once the transitive Dependency has been removed, then we will check two conditions

Consider a function Dependency X –>Y then for a given relation to be in 3 NF, any one of the following conditions must be satisfied.

(i) Ethier X is a Super Key

OR

(ii) Y is a prime attribute.

If any above the mentioned condition is satisfied, then the given Relation will in 3rd Normal Form.

Consider the following EMP table to understand the concept of 3nf in DBMS with an example.

Candidate key of this table is Emp_ID. All other attribute are non prime attributes.

Since in this table there is a transitive dependency. Because Non prime attribute EMP_CITY, EMP_STATE can be determined by other non prime attribute zip code. This is not allowed in 3nf .

So in order to convert this table in 3NF we have to decompose it

The decomposition in 3 NF is given below

EMP_ZIPCODE

EMP_CITY_ZIP

This decomposition is now in 3 NF.

4.Boyce-Codd Normal Form (BCNF)

A table or relation is said to be in in 3rd normal form and all tables in the database should have only one primary key.

Consider the following table

Here in this table candidate key is { EMP ID ,EMP DEPT}. This table is not in BCNF because neither EMP DEPT nor EMP ID is alone the key.

So to convert this table we need to decompose it into three tables.

EMP COUNTRY TABLE.

EMP DEPT Table.

EMP Dept Mapping table.

Now this decomposition is in BCNF.

A relation or table is said to be in 4NF when it satisfies the following condition “A relation or table is in 4NF if it is in 3 normal forms (3NF) and does not have any multivalued dependencies.”

In other words, we can say that the normal form, which is in BCNF and does not have multivalued functional Dependency, is 4NF.

What is Multivalued Dependency?

“Multivalued dependency occurs when one table contains multiple independent (multiple) multivalued attributes.”

6.Fifth Normal Form (5NF)

A relation or table is in 5NF when it satisfies the following condition “A table or Relation is then in 5NF when it is in 4NF, and there is no non-loss decomposition in the table.

Note Point: Candidate key is a super key that has no redundant attribute. It means any subset of candidate key should not be a super key. A candidate key is an attribute or group of attributes that uniquely identify tuples in relations.

Conclusion and Summary

In this database normalization tutorial, we have discussed essential concepts which are helpful in database design. These concepts are functional Dependence, database anomalies, inference rules, Normalization, and various forms of normal form.

I hope this tutorial will be beneficial for students.

Please provide your feedback or leave a comment to improve and provide you with a good quality tutorial.

Don’t stop learning and practice.

If you find this Normalization in the DBMS tutorial helpful, then please Like and Share the post on Facebook, Twitter, Linkedin through their icons as given below.

All the Best !!

Previous Tutorial – Relational Database Management System

Next Tutorial – Views in SQL