# DB concepts

## Normalization

Normalization is a systematic approach of organizing data in a database to eliminate data redundancy, inconsistent database behavior and other undesirable characteristics. Data dependency created for normalization should make sense.

A table before normalization

*Data redundancy*: Here office_tel is same for every student of the same branch.
*Insertion anomaly*: A new student entry cannot be made until the branch is allocated. Again, same branch name may be added many times. This is insertion anomaly.
*Deletion anomaly*: Here student information (roll, branch) and branch information (branch name, hod, office_tel) are kept together. Therefore, if student records are deleted, branch records are deleted too.
*Updation anomaly*: If Hod of office_tel for branch CSE changes, all student records will need updating. If one record is missed, data will become incosistent.

To eliminate these redundancy and anomalies normalization is done and the following tables are generated

Table: Student Information
| Roll | Name | BranchId|
| ------- | -------- | -------|
| 401 | Akon | 01 |
| 402 | Bkon | 01 |
| 403 | Ckon | 01 |
| 404 | Dkon | 01 |

Table: Branch Information
| BranchId | Name | Hod | Office_tel |
| -------- | -------- | -------- |-------|
| 01 | CSE | Mr. X | 53337 |

## 3NF Normalization

Normalization rules are divided into different normal forms. 3NF stands for 3rd normal form.
By defintion, a table is in 3NF when it is in the 2nd normal form and it does not have any Transitive dependency.

**Ques:** When designinga database tables how to achieve 3NF? Note we dont have the data, we know how they are expected to be (type of data).
**Rule of thumb:** `Will this data be repeated? if 'Yes' then we make a separate table for that data and its related information's entry to achieve 3NF. Otherwise no separate table is required to achieve 3NF.`

**Example**
Table: Student Information
| Roll | Name | Age | Branch |
| ------- | -------- | --- | ------ |
| 401 | Akon | 20 | CSE |
| 402 | Bkon | 19 | CSE |
| 403 | Ckon | 20 | CSE |

*After 3NF Normalization*
Table: Student Information
| Roll | Name | Age | BranchId |
| ------- | -------- | --- | ------ |
| 401 | Akon | 20 | 01 |
| 402 | Bkon | 19 | 01 |
| 403 | Ckon | 20 | 01 |

Table: Branch Info
| Id | Branch |
|----| ------ |
| 01 | CSE |

*Note: This is a simplified 3NF example. Please check out the links in this regard.*

## Denormalization

Denormalization is a database optimization technique where redundant data is added to normalized database. It is applied only after doing normalization.

Advantages:
1. Data retrieval is faster since there are fewer joints
2. Queries can be simpler as fewer tables need to be queried

Disadvantages:
1. Update and insert operations are more expensive
2. Coding for update and insetion may be more complex
3. Data inconsistency
4. Data redundancy calls for larger storage space

### Links
1. [DB Normalization](https://www.studytonight.com/dbms/database-normalization.php)
2. [Dtabase Normalization](https://www.tutorialspoint.com/dbms/database_normalization.htm)
3. [Normal forms in DBMS](https://www.geeksforgeeks.org/normal-forms-in-dbms/)
4. [Denormalization in database](https://www.geeksforgeeks.org/denormalization-in-databases/)
5. [Database denormalization with examples](https://rubygarage.org/blog/database-denormalization-with-examples)
6. [Another Database Normalization article](https://www.guru99.com/database-normalization.html)

### Video Tutorial Link
1. [link](https://www.lynda.com/Software-Development-tutorials/Welcome/412845/438411-4.html)