Normalization - SQL Server

Normalization:

Normalization is the process of reduce the redundancy.

Removing the repeating rows from the table and maintain them in a separate table.

There are 6 normalization steps defined in RDBMS.

Mostly database designers will follow till 3^rd normalization.

EmpID	EmpName	EmpSalary	DeptID	DeptName
1	E1	12000	1	MIS
2	E2	13000	2	Accounts
3	E3	11000	1	MIS
4	E4	9500	2	Accounts
5	E5	12580	2	Accounts
6	E6	18000	1	MIS

The above table containing the information of employees.

Here is containing DeptID, Deptname columns and data in those columns are repeating.

 Let us suppose there are 50k Employees information in that table.

For each employees, those columns will get repeat.

That leads to waste of disk space and memory.

If you want to change any data in those columns, for example if you want to change the deptname MIS to some other, you need to change data in columns using update query.

To avoid this, we will split that table into two steps.

                                                                                                                                Foreign key

EmpID	EmpName	EmpSalary	DeptID
1	E1	12000	1
2	E2	13000	2
3	E3	11000	1
4	E4	9500	2
5	E5	12580	2
6	E6	18000	1

Primary Key

DeptID	DeptName
1	MIS
2	Accounts

By using joins we can combine the columns and get the data as we want.

Rules for 1^st normalization form.

Data in each column should be atomic. No multiple values in the same cell as below

DeptName	EmpName
MIS	E1,E2,E3…

Data should not separate by commas as it is not possible to make any querying on it.

No repeating column groups.

DeptName	EmpName1	EmpName2	EmpName3
MIS	E1	E2	E3
Accounts	E4

If any new employee added, entire table structure need to be changed.

Rules for 2^nd normalization form.

Must satisfy the conditions of 1^st normal form.

An entity must be in First Normal Form

Entity must have a Primary Key or Composite Primary Key           

Every attribute must be fully and functionally dependent upon Primary Key

Move the redundant data into a separate table.

Identify each record uniquely using primary key and create relation between those two tables.

Foreign key

EmpID	EmpName	EmpSalary	DeptID
1	E1	12000	1
2	E2	13000	2
3	E3	11000	1
4	E4	9500	2
5	E5	12580	2
6	E6	18000	1

Primary Key

DeptID	DeptName
1	MIS
2	Accounts

3^rd Normalization

Must satisfy the conditions of 1^st  and 2^nd normal form.

No dependent columns should exist in the table.

EmpID	EmpName	EmpSalary	Annual salary	DeptID
1	E1	12000	144000	1
2	E2	13000	156000	2
3	E3	11000	132000	1
4	E4	9500	114000	2
5	E5	12580	150960	2
6	E6	18000	216000	1

Here Annual salary column not required as it is the dependent on Empsalary and we can get annual salary from “Empsalary”.

So remove that column.