Database Normalization In DBMS
Tags: database, datascience, python, programming
Published: 2023-11-14 10:35:16

## What is Normalization?

Normalization is the process of organizing the data and the attributes of a database.

- It is performed to reduce the data redundancy in a database and ensure that data is stored logically.

- Normalization is systematic approach of decomposing table to eliminate data redundancy and undesirable characteristics like insertion, update and delete.

- Normalization is multi-step process that puts data in tabular form and remove duplicate data from relation tables.

**Example Employees Table**

| Id              | Name      | Address    | Profession    |
|-----------------|-----------|:------------:|------------:|
| 101             | Mary      | 1245       | Developer     |           |-----------------|-----------|------------|---------------|
| 102             | David     | 5234       | Accountant    |
| 103             | Juliet    | 1444       | Salesperson   |
| 104             | Elizabeth | 8745       | Manager       |
| 105             | Haskell   | 3251       | Operation     |

In this table, we have data of office employees.

---


**1. Insertion Anomaly**
An insertion anomaly occurs in the relation database when some attributes or data items are inserted into database without existence of other attributes.

**2. Updation Anomaly**
Updation Anomaly occurs when the same data item is repeated with the same values are not linked to each other.

**3. Deletion Anomalies**

Deletion Anomalies occurs when deleting one part of the data deletes the other necessary information from the database.

---

## Types of Normalization

- 1NF

- 2NF

- 3NF

- BCNF

- 4NF

- 5NF

**Diagram:**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m33j9bawmz0elwer4wa0.png)

**1. 1NF (First Normal Form)**
In 1NF relation, each table cell should contain a single value. Each record looks like unique.

 **Example**

| CouserId        |Course Name| Framework          |
|-----------------|-----------|:------------------:|
| JAV101          | Java      | NetBeans           | 
| SQL102          | SQL       | MySQL, PostgreSQL  | 
| PY214           | Python    | Flask              | 

Here in the Framework row, we stored two frameworks of course Name MySQL, PostgreSQL so it is **multi-valued attribute. ** it is not 1NF relation. We need to convert it into 1NF.  

**Convert it into 1NF**

| CourseId        | Course Name       | Framework   |
|-----------------|------------------ |:-----------:|
|JAV101           | Java              | NetBeans    |
|SQL102           | SQL               | MySQL       |
|SQL102           | SQL               | PostgreSQL  |
|PY214            | Python            | Flask       |


It's a simple method to store Framework separately in 1NF. Now this is **First Normal Form**. 1NF wants to store unique information in table without data repetition.

**2. 2NF (Second Normal Form)**

In **2NF**, relation must in **1NF**. In the Second Normal Form, all non-key attributes are fully functionally dependent on the primary key.


|StudentID        | Specialization      | Student Age   |
|-----------------|---------------------|:-------------:|
|501              | Data Analyst        | 22            |
|501              | Data Engineer       | 22            |
|502              | Full Stack Developer| 24            |
|503              | Web Developer       | 23            |