---
# System prepended metadata

title: Database Normalization
tags: [DB, 訓]

---

# [Database Normalization](https://www.analyticsvidhya.com/blog/2022/08/database-normalization-a-step-by-step-guide-with-examples/)
[TOC]
## What do we need normaliztion?
Data inconsistency results from anything that affects data integrity. This can cause the data to be correct in one place and wrong elsewhere it is stored. This can lead to unreliable and meaningless information. It occurs between tables when similar data is stored in different formats in two different tables.

#### Examples:


| Student ID | Student Name | Course Name |
| --------   | -------- | -------- |
| 111        | John     | English     |
| 112        | Alice    | English     |
| 111        | John     | French      |

In the above table,we have stored some data twice,This is called data **redundancy**.  
We can eliminate data redundancy in the databases by the normalization of relations.
## Functional Dependency(功能相依)
An attribute is dependent on another attribute if another attribute uniquely identifies it.

It is denoted by  A –> B, meaning A determines B, and B depends upon A.
#### Example:We could find student's name using the student_ID
## What Is Normalization?
Database normalization is the process of organizing a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity.
## Normal Forms(NF)
Different stage of normalization
* 1 NF (First Normal Form)
* 2 NF (Second Normal Form)
* 3 NF (Third Normal Form)

## 1NF
### Multiple Values(多重值)
**Removing Multiple Values**
We have stored multuple values in `Outlet_Location`and`Contact_No`
![image](https://hackmd.io/_uploads/rJ-_eK-BT.png)
removing the non-atomic and multi-valued attributes

Data redundancy still exists even after 1st Normal form, so we need further normalization.

## 2NF
### Partial Dependency(部分相依)
:::info
Partial dependency occurs when a part of the primary key (Key attribute) determines the non-key attribute.
:::
**Removing partial dependency**
![image](https://hackmd.io/_uploads/BJ_UttWBp.png)
In the Retail Outlets table, the Item_Code and Retail_Outlet_ID are key attributes. The item description is partially dependent on Item_Code only. Outlet_Location depends on Retail_Outlet_ID. These are partial dependencies.

## 3NF
### Transitive Dependency(遞移相依)
:::info
A transitive dependency exists when another non-key attribute determines a non-key attribute. In other words, If A determines B and B determines C, then automatically, A determines C.
:::
![image](https://hackmd.io/_uploads/HkQc6tZrT.png)
**Removing Transitive Dependency**
![image](https://hackmd.io/_uploads/Sk4-J9ZH6.png)
The pincode of a city determines the City’s Name


