What is Normalization in SQL?

Normalization is the process of reducing the redundancy of data. Did you get it? I don’t think so. Before moving into the academic definitions of Normalization, let’s understand Normalization in DBMS by using a practical example.

Figure 1

Figure 2

Figure 3

What are the problems with this large table? Can you see that there are lots of repeated data in this table? See, how many times department names like CSE, IT has repeated again and again, also the Building number and Lecture Hall Number have repeated many times. (Please kindly consider this is just an example to explain Normalization). Assume that if there are 100 students in the IT department, we have to insert Department Name IT 100 times in this table. Actually, it is data redundancy.

Having data redundancy not only eats extra space, but it is difficult to handle and update the database. Other than redundancy, there are 3 major problems has identified for introducing this Normalization concept. They are,

1. Insert Anomaly
2. Update Anomaly
3. Delete Anomaly

01. Insert Anomaly

Suppose you want to add the Business studies department to the above table but, no student belongs to that department. Therefore you have to keep Student ID, Student Name, and GPA as NULL values. Here Student ID is the primary key. So you cannot keep it as NULL. This is called “Insertion Anomaly”.

02. Update Anomaly

Assume that the IT department has moved to a new building which number is B005. How many tuples you will have to update here if there are 100 students in the IT faculty? Now you got it, I think. It is also a hugely time-consuming process if you are going to update them one by one. This is how “Update Anomaly” behaves. Therefore, it is easy to update data, if you have a separate table for the Building Entity.

03. Delete Anomaly

Assume that you have to remove a student from this table for a particular reason (Assume that you are going to remove Huego in the ME. He is the only guy in the ME department). And also that student is the only guy who represents that department. Is that possible to remove him/her from the table? Yes, it is. But all the information about the department will also be deleted. This is called “Delete Anomaly”.

Now you can understand what happens when all the attributes are in one table and how hard to manage tables when it has redundant data. This is why Normalization comes into the picture. Now is the time to give you a quick definition of Normalization.

“Normalization is creating the database table without Insert, Update and Delete Anomalies and save the space by reducing the redundant data.”

As per the conclusion, I thought to write down some benefits of Normalization.

1. It reduces the redundancy of Data
2. Reduce Insertion, Delete and Update anomalies
3. Minimize NULL values
4. Simplify queries
5. Simplify the database structure

What are the Normalization Forms in DBMS? I will write about those in my upcoming Articles. Stay tune with us.