What Is DataBase Normalization By Sagar Jaybhay

In this article we will understand What Is DataBase Normalization ? Different Types of Normalization forms By Sagar Jaybhay.

DataBase Normalization

Database normalization is a process of organizing data and minimizing data redundancy which in turn ensures data consistency.

The problem of data redundancies:

  1. Data is duplicated
  2. Disk space required more which is wastage
  3. Data inconsistency
  4. 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.
DeptName EmpName
Hr Sagar, Suresh, Ramesh
IT X, y, Z

This not good.

  • The table does not contain any repeating column groups.
DeptName EmpName1 Empname2 Empname3
Hr Sagar Suresh Ramesh
IT X Y Z
  • You can identify each record by the primary key.
Deptid DeptName
1 HR
2 IT

Deptid Empname
1 Sagar
1 Suresh
1 Ramesh
2 X
2 Y
2 Z

2nd Normal Form(2NF)

  1. The Table needs to meet the requirement of 1st normal form.
  2. Need to move redundant data to separate table
  3. Create a relationship between these tables using primary key and foreign key.
EmpID EmpName Gender Salary DeptName DeptHead DeptLocation
1 Sagar Male 10000 HR X India
2 Seeta Female 20000 IT Y USA
3 Suresh male 30000 Sales Z UK
4 Raju Male 40000 Account K London

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.

DeptID DeptName DeptHead DeptLocation
1 HR X India
2 IT Y USA
3 Sales Z UK
4 Account K London

EmpID EmpName Gender Salary DeptID
1 Sagar Male 10000 1
2 Seeta Female 20000 2
3 Suresh male 30000 3
4 Raju Male 40000 4

3rd Normal Form

  1. The table needs to meet all the conditions in the first normal form and second normal form.
  2. The table does not contain any column that not fully depend on the primary key of that table.
EmpID EmpName Gender Salary Annual Salary DeptID
1 Sagar Male 10000 120000 1
2 Seeta Female 20000 240000 2
3 Suresh male 30000 360000 3
4 Raju Male 40000 480000 4

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

Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

Related posts