SQL Database Normalization: 1NF, 2NF, 3NF, 4NF

Normalization: Removing Duplicates is called Normalization.

Benefits:

• Store data efficiently
• Reduce duplicates
• Protect data integrity

Example: The above table is not normalized.

EName	Gender	Salary	DName	DHead	DLocation
Chris	Male	4500	Finance	Lu	Michigan
Mary	Female	2000	HR	Pete	Florida
Den	Male	1500	Finance	Lu	Michigan
Kate	Female	3000	HR	Pete	Florida
Mathew	Male	6500	Finance	Lu	Michigan

Problems with Duplicate data:

• Wastage of disk space.
• Inconsistent data.
• DML(Insert/Update/Delete) queries can become slow.

To solve this, we normalize tables. We have broken the above table into 2 tables, repeating columns we have moved into a separate table and we don’t have to repeat DName, DHead, DLocation in the Employees Table all we do here refer Did. And we have given the primary key to Did to uniquely identify each department in the Department table.

Read More: Different Types of SQL Keys

Department Table

DId	DName	DHead	DLocation
1	Finance	LU	Michigan
2	HR	Pete	Florida

 Employee Table

Read: What is SQL Server? Microsoft SQL Server Tutorial Guide for Beginners

EID	EName	Gender	Salary	DId
1	Chris	Male	4500	1
2	Mary	Female	2000	2
3	Den	Male	1500	1
4	Kate	Female	3000	2
5	Mathew	Male	6500	1

There are 6 normal forms: 1NF,2NF,3NF,4NF,5NF,6NF. Most databases are in 3NF. There are certain rules that each normal form follows

Read More: Different Type of SQL Joins

First Normal Form(1NF) A table is said to me 1NF, if

• No Multiple values separated by comma.

Example:

DName	EName
Finance	Chris, Mary, Den
HR	Mathew

Because of multiple values in the above example Insert, Update, Delete is not possible for one Employee.

• No repeating columns.

 

Example:

DName	EName1	EName2	EName3
Finance	Chris	Den	Kate
HR	Mary

Suppose HR department has only one employee, EName2 and EName3 columns have null values in that case, wastage of the disk space.

Read: Different Types of SQL Server & SQL Database Functions

• By using Primary key, each record should identify Uniquely.

Example: Table1:

DId	DName
1	Finance
2	HR

Table2:

DId	EName
1	Chris
1	Den
1	Kate
2	Mary

Here, DId is the Primary key in the Table1 and DId is the Foreign key in the Table2, which satisfies 1NF.

Read More: Different Types of SQL Database Functions

Second Normal Form(2NF)

• Should meet all the requirements of 1NF.
• Move unwanted data into separate table.
• Create relationship between these tables by using Foreign key.

Department Table

DId	DName	DHead	DLocation
1	Finance	LU	Michigan
2	HR	Pete	Florida

Employee Table

EID	EName	Gender	Salary	DId
1	Chris	Male	4500	1
2	Mary	Female	2000	2
3	Den	Male	1500	1
4	Kate	Female	3000	2
5	Mathew	Male	6500	1

DId is Primary key in Department table and DId in Employee table is Foreign key (Added additional column to make sure the two tables have relationship).

Read More: Different Types of SQL Injection

Read: What Does SQL Stand For? The Structured Query Language Explained

Third Normal Form(3NF)

• Should meet all the requirements of 1NF and 2NF.
• Remove columns which are not fully dependent on Primary key.

 Example: Employee Table

EID	EName	Gender	Salary	Annual Salary	DId
1	Chris	Male	4500	54000	1
2	Mary	Female	2000	24000	2
3	Den	Male	1500	18000	1
4	Kate	Female	3000	36000	2
5	Mathew	Male	6500	78000	1

Annual Salary is not fully dependent on Primary key because Annual Salary Column depends on Salary column.

Fourth Normal Form(4NF)

• Should meet all the requirements of 1NF, 2NF, 3NF
• NO Multivalued dependencies, other than Candidate key

Example: Course, Lecture, Books

Difference between Normalization and Denormalization

Normalization	Denormalization
Divides data into multiple tables so, data retrieval is slow	Data from multiple tables are divided into single table so, data retrieval is fast
Reduce duplicate data	Create duplicate data
Because of more tables, we use more joins	Less tables less joins