---
title: "What is normalization?"  
description: "What is normalization?"  
author: "Abhishek Srivasatava"  
published: 2016-09-27  
updated: 2020-09-19  
canonical: https://www.mindstick.com/interview/23075/what-is-normalization  
category: "database"  
tags: ["database", "sql server"]  
reading_time: 7 minutes  

---

# What is normalization?

## Normalization :\

Normalization is a process of effectively organizing the column and table of a database to reduce data redundancy and improve data integrity.

**Normal form of a table**

| **Prsn_empl_id** | **Prsn_dpnd_id** | **Plan opted** |
| --- | --- | --- |
| 669911 | 123456 | Health , Dental |
| 669901 | 987415 | Vision |

**1NF**

First Normal Form (1NF)

All attributes in a relation must have atomic domains.

In this scheme there should be no repeated element in single block.

**For example:**

| **Prsn_empl_id** | **Prsn_dpnd_id** | **Plan opted** |
| --- | --- | --- |
| 669911 | 123456 | Health |
| 669911 | 123456 | Dental |
| 669901 | 987415 | Vision |

**2NF**

Second Normal Form (2NF)

for implementing this scheme it must satisfied 1st scheme.

Before moving further one should know about the prime attribute and non-prime attribute.

**Prime attribute:** the entire element which is a part of the prime key.

**Non-prime attribute:** the entire element which is not part of the prime key.

For this scheme, non prime attribute should be functionally dependent on prime key.

**1NF table**

| **Prsn_empl_id** | **Prsn_dpnd_id** | **Plan opted** |
| --- | --- | --- |
| 669911 | 123456 | Health |
| 669911 | 123456 | Dental |
| 669901 | 987415 | Vision |

**Modified to 2NF**

| **Prsn_empl_id** | **Prsn_dpnd_id** |
| --- | --- |
| 669911 | 123456 |
| 669901 | 987415 |

| **Prsn_empl_id** | **Plan opted** |
| --- | --- |
| 669911 | Health |
| 669911 | Dental |
| 669901 | Vision |

**3NF**

It must satisfy 2NF criteria and there should no relationship between primary key and non primary element data.

For example we have table like this

| **Name** | **Addr_id** | **Address type** | **Address** |
| --- | --- | --- | --- |
| A | 10 | Permanent | Abc |
| A | 5 | Temp | Pqr |
| B | 10 | Permanent | Xyz |
| B | 5 | temp | Efg |

We can divide above table in two

| **Name** | **Addr_id** | **Address** |
| --- | --- | --- |
| A | 10 | Abc |
| A | 5 | Pqr |
| B | 10 | Xyz |
| B | 5 | Efg |

| **Addr_id** | **Address type** |
| --- | --- |
| 5 | Temp |
| 10 | Permanent |

**Boyce-Codd Normal Form**

It is developed by the Raymond F Boyce.

X → Y is a trivial functional dependency (Y ⊆ X)

X is a super key for schema R

**Normal table**

| **Employee_id** | **Name** | **DOB** | **Car** | **Car_id** | **price** |
| --- | --- | --- | --- | --- | --- |
| 1001 | A | 12/01/1993 | U | 997 | 11M |
| 1002 | B | 15/01/1993 | X | 997 | 12M |
| 1003 | C | 17/01/1993 | Y | 997 | 13M |
| 1004 | D | 20/01/1993 | Z | 997 | 14M |

**Converting to Boyce-Codd Normal Form**

| **Employee_id** | **Name** | **DOB** |
| --- | --- | --- |
| 1001 | A | 12/01/1993 |
| 1002 | B | 15/01/1993 |
| 1003 | C | 17/01/1993 |
| 1004 | D | 20/01/1993 |

| **Car** | **Car_id** | **price** |
| --- | --- | --- |
| U | 997 | 11M |
| X | 996 | 12M |
| Y | 995 | 13M |
| Z | 994 | 14M |

| **Employee_id** | **Car_iD** |
| --- | --- |
| 1001 | 997 |
| 1002 | 996 |
| 1003 | 995 |
| 1004 | 994 |

## Answers

### Answer by Abhishek Srivasatava

## Normalization :\

Normalization is a process of effectively organizing the column and table of a database to reduce data redundancy and improve data integrity.

**Normal form of a table**

| **Prsn_empl_id** | **Prsn_dpnd_id** | **Plan opted** |
| --- | --- | --- |
| 669911 | 123456 | Health , Dental |
| 669901 | 987415 | Vision |

**1NF**

First Normal Form (1NF)

All attributes in a relation must have atomic domains.

In this scheme there should be no repeated element in single block.

**For example:**

| **Prsn_empl_id** | **Prsn_dpnd_id** | **Plan opted** |
| --- | --- | --- |
| 669911 | 123456 | Health |
| 669911 | 123456 | Dental |
| 669901 | 987415 | Vision |

**2NF**

Second Normal Form (2NF)

for implementing this scheme it must satisfied 1st scheme.

Before moving further one should know about the prime attribute and non-prime attribute.

**Prime attribute:** the entire element which is a part of the prime key.

**Non-prime attribute:** the entire element which is not part of the prime key.

For this scheme, non prime attribute should be functionally dependent on prime key.

**1NF table**

| **Prsn_empl_id** | **Prsn_dpnd_id** | **Plan opted** |
| --- | --- | --- |
| 669911 | 123456 | Health |
| 669911 | 123456 | Dental |
| 669901 | 987415 | Vision |

**Modified to 2NF**

| **Prsn_empl_id** | **Prsn_dpnd_id** |
| --- | --- |
| 669911 | 123456 |
| 669901 | 987415 |

| **Prsn_empl_id** | **Plan opted** |
| --- | --- |
| 669911 | Health |
| 669911 | Dental |
| 669901 | Vision |

**3NF**

It must satisfy 2NF criteria and there should no relationship between primary key and non primary element data.

For example we have table like this

| **Name** | **Addr_id** | **Address type** | **Address** |
| --- | --- | --- | --- |
| A | 10 | Permanent | Abc |
| A | 5 | Temp | Pqr |
| B | 10 | Permanent | Xyz |
| B | 5 | temp | Efg |

We can divide above table in two

| **Name** | **Addr_id** | **Address** |
| --- | --- | --- |
| A | 10 | Abc |
| A | 5 | Pqr |
| B | 10 | Xyz |
| B | 5 | Efg |

| **Addr_id** | **Address type** |
| --- | --- |
| 5 | Temp |
| 10 | Permanent |

**Boyce-Codd Normal Form**

It is developed by the Raymond F Boyce.

X → Y is a trivial functional dependency (Y ⊆ X)

X is a super key for schema R

**Normal table**

| **Employee_id** | **Name** | **DOB** | **Car** | **Car_id** | **price** |
| --- | --- | --- | --- | --- | --- |
| 1001 | A | 12/01/1993 | U | 997 | 11M |
| 1002 | B | 15/01/1993 | X | 997 | 12M |
| 1003 | C | 17/01/1993 | Y | 997 | 13M |
| 1004 | D | 20/01/1993 | Z | 997 | 14M |

**Converting to Boyce-Codd Normal Form**

| **Employee_id** | **Name** | **DOB** |
| --- | --- | --- |
| 1001 | A | 12/01/1993 |
| 1002 | B | 15/01/1993 |
| 1003 | C | 17/01/1993 |
| 1004 | D | 20/01/1993 |

| **Car** | **Car_id** | **price** |
| --- | --- | --- |
| U | 997 | 11M |
| X | 996 | 12M |
| Y | 995 | 13M |
| Z | 994 | 14M |

| **Employee_id** | **Car_iD** |
| --- | --- |
| 1001 | 997 |
| 1002 | 996 |
| 1003 | 995 |
| 1004 | 994 |


---

Original Source: https://www.mindstick.com/interview/23075/what-is-normalization

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.