What is Database normalization, why database normalization is required, types of normal forms and roles for 1NF, 2NF, 3NF.

“Database normalization is the process of organizing the columns (attributes) and tables (relations) of a relational database to reduce data redundancy and improve data integrity.”

Or

“Database normalization is the process of or organizing data to minimalize data redundancy (data duplication), which in tum ensures data consistency”

Example table without normalization:

EmpId	Employee	Gender	Salary	DeptName	DeptHead	Location
1	Rax	Male	10000	IT	Takewha	USA
2	Sam	Male	11000	HR	Cook	IND
3	Mark	Female	9000	HR	Cook	IND

Example for normalized database tables(with normalization)

Problems of data redundancy (data duplication) and without Normalization(in non-normalized tables)

·         Disk Space wastage

·         Data Inconsistency

·         DML(Select, Insert, Update and Delete) queries became slow  

Type of Database normalization forms

There are 6 normal forms, First Normal Form (1NF) through sixth normal Form (6NF). Most of the data bases are in Third normal form (3NF). there are certain rolls in each normal form should follow.

Following are the normalization role for (1NF, 2NF, 3NF) to be followed in each normal form

First Normal Formal (1NF):

1NF Roles

1.      Data in each column should be atomic, No multiple values separated by comma.

2.      The table does not contain repeating column groups.

3.      Identify each record uniquely using primary key.

a.       (1NF) First Role Example :

Non atomic employee columns (contains multiple all department employee values separated by comma)

DeptName	Employee
IT	Rax, Takewha, sam
ECE	Cook
EEE	Mark

Problems of non-atomic columns:

·         DML operations SELECT, INSERT, UPDATE and UPDATES are not for one employee

b.      (1NF) Second Role(The table does not contain repeating column groups) Example :

No repeating column groups

DeptName	Employee1	Employee2	Employee3
IT	Rax	Takewha	sam
ECE	Nava Kumar
EEE	Mark

Problem with repeating groups:

·         Table structure changes are required for every new employee, to add new employee need to alter the table structure.

·         Wasted disk space for other department employee columns 

c.       (1NF) Third Role(Identify each record uniquely using primary key) Example :

Identify each record uniquely using primary key

DeptID à Primary key

DeptID	DeptName
1	IT
2	ECE
3	EEE

DeptIDà Foreign key

DeptID	Employee
1	Rax
2	Cook
2	Mark
1	Takewha
3	Sam

Second Normal Formal(2NF):

2NF Roles

1.      Table meets all the conditions of the 1NF

2.      Move redundant data to separate table

3.      Create relationship between these tables using foreign keys

Non normalized table structure

EMPID	Employee	Gender	Salary	DeptName	DeptHead	Location
1	Rax	Male	10000	IT	Takewha	USA
2	Sam	Male	11000	HR	Cook	IND
3	Emma	Female	9000	HR	Cook	IND

Tables designed in second normal form with primary and foreign key  

Third Normal Formal(3NF):

3NF roles

1.      Tables Meets all conditions of 1NF and 2NF

2.      Dose not contain columns(attributes) that are not fully dependent upon the primary key

The below example contains non-dependent primary key attribute or columns:

Example #1: Here the column (AnnualSalary) is not fully dependent on primary key it is also dependent on column(Salary), in fact we don’t need to store the annual salary in the table, if need it we can get it on fly writing the select query.  

EMPID	Employee	Gender	Salary	AnnualSalary	DeptID
1	Rax	Male	10000	120000	1
2	Sam	Male	11000	132000	2
3	Emma	Female	9000	108000	2
4	Luke	Male	5000	60000	1

Example #2: Here the column (DeptHead) is not fully dependent on primary key it is also dependent on column (DeptName).

EMPID	Employee	Gender	Salary	DeptName	DeptHead
1	Rax	Male	10000	IT	Mike
2	Sam	Male	11000	IT	Mike
3	Emma	Female	9000	HR	Takewha
4	Luke	Male	5000	HR	Takewha

The below example contains fully-dependent primary key attribute or columns:

3NF normalized tables: in the below example table attribute are fully dependent on Primary Key

Tables designed in third normal(3NF)