Normalization in Databases
Hi guys, Normalization is the solution for many problems in DBMS, but before directly jumping into normalization let's take an example. Suppose you are an admin of the database of a school and your task is to design a database and you have created a table named student as shown in the figure.
The student table contains many columns as show notice last 3 columns of the table data is repeated many times this increases data redundancy. This redundant data causes there anomalies(problem):
1: Update Anomalies: In the above table if room no. for the CSE department changes from 102 to 104 then we have to update every row where the branch is CSE and this will be a lot of work. This problem is known as update anomalies.
2: Deletion Anomalies: Suppose we want to delete the information about Astha and Manjunath and if we do so the information about the EEE branch is lost since we don’t have any student enrolled in this program. This problem is known as deletion anomalies.
3: Insertion Anomalies: Let’s there is a new ISE branch in college. Since it is a new branch you have no student enrolled in it so you can’t insert any information related to the branch. This problem is insertion anomalies.
Normalization: Normation is the solution to all the problems mention above the reduces the redundancy in the database. We actually divide the above student table into two parts as shown in the figure:
The first part is the student table where we have removed the other information about the branch and remained only one row which contains the branch information.
The second table is a brach table where detailed information of the branch is stored.
Conclusion: This normalization solves all the there problem mentioned with the non-normalized table. Normation can’t remove 100% redundancy but it will surely reduce the redundancy.