In this article we will understand What Is DataBase Normalization ? Different Types of Normalization forms By Sagar Jaybhay.
Database normalization is a process of organizing data and minimizing data redundancy which in turn ensures data consistency.
The problem of data redundancies:
- Data is duplicated
- Disk space required more which is wastage
- Data inconsistency
- DML queries become slow(Insert, update, delete)
create table emps(empname nvarchar(20),gender nvarchar(20),salary float,deptname nvarchar(20),deptheaad nvarchar(20),deptlocation nvarchar(20)) insert into emps values('sagar','male',1000,'Hr','Raju','London'), ('A','female',2000,'IT','X','UK'), ('B','male',3000,'Account','Y','USA'), ('C','female',4000,'Support','Z','India');
Database normalization is a step by step process. There is 6 normal forms that start from 1st normal form to the 6th normal form.
But most of the databases support up to 3rd normal form.
In general, normalization means broken down the table into multiple tables where we can avoid data redundancies in which repeating columns or rows move to another table.
Below is unnormalize table and we want to normalize this table.
In the above case, you can see we are repeating the depthead and department name column. Suppose this table has millions of records and in the future, our department head will change then Raju to xyz then we need to update millions of records and this is time-consuming and performance will degrade.
So if we remove this repeating rows into another table which decreases space requirement and time required for this is minimum.
1st Normal Form
- It means data in the column should be atomic and no column contains multiple data by comma-separated.
|Hr||Sagar, Suresh, Ramesh|
|IT||X, y, Z|
This not good.
- The table does not contain any repeating column groups.
- You can identify each record by the primary key.
2nd Normal Form(2NF)
- The Table needs to meet the requirement of 1st normal form.
- Need to move redundant data to separate table
- Create a relationship between these tables using primary key and foreign key.
Now we are splitting the above table into 2 different tables which look like below here deptid is a foreign key by which relationship is achieved.
3rd Normal Form
- The table needs to meet all the conditions in the first normal form and second normal form.
- The table does not contain any column that not fully depend on the primary key of that table.
In the above table, the Annual salary table does not fully depend on empid. So there is no need for the annual salary you can compute this query so you can remove this column.
GitHub Profile:- https://github.com/Sagar-Jaybhay