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 1^st
normal form to the 6^th normal form.

But most
of the databases support up to 3^rd 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

2^nd Normal Form(2NF)

1. The Table needs to meet the requirement of 1^st 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

3^rd 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