Normalization In SQL

Normalization

It is database design technique that reduces data redundancy and ensure that data is stored logically.

Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of normalization is to add delete or modify field that can be made in a single table.

What Is Data Redundancy?

Data redundancy is the repetitive storage of the same data in data storage or Database Management System (DBMS).

It divides larger tables into smaller tables logically and links them by establishing association among them.

Database Normal Forms

Here is a list of Normal Forms in SQL:

1NF (First Normal Form)
2NF (Second Normal Form)
3NF (Third Normal Form)
BCNF (Boyce-Codd Normal Form)

1NF (First Normal Form) Rules

Each table cell should contain a single value.
Each record needs to be unique.

If in our table we have multiple values in single row

Stud ID	Course ID	Course Name	Student Name
1	101	C1	Bob
2	102, 103	C2, C3	Vishwa
3	101	C1	Swathi

In 1NF every row should contain a single value

Stud ID	Course ID	Course Name	Student Name
1	101	C1	Bob
2	102	C2	Vishwa
3	101	C1	Swathi
2	103	C3	Vishwa

2NF (Second Normal Form) Rules

Rule 1- Be in 1NF.
Rule 2- Single Column Primary Key that does not functionally dependent on any subset of candidate key relation.

In Above example our Sub ID Column should be our primary key. and we can convert that single column into two columns

1st Column

Stud ID (PK)	Student Name	Course ID (FK)
1	Bob	101
2	Vishwa	102
3	Swathi	103

2nd Column

Course ID (PK)	Course Name
101	C1
102	C2
103	C3

3NF (Third Normal Form) Rules

Rule 1- Be in 2NF
Rule 2- Has no transitive dependencies

What are transitive dependencies?

A transitive dependency is when changing a non-key column, might cause any of the other non-key columns to change

BCNF (Boyce-Codd Normal Form)

Rule 1- Be in 3NF
Rule 2- BCNF (Boyce Codd Normal Form) is the advanced version of 3NF. A table is in BCNF if every functional dependency X->Y, X is the super key of the table. For BCNF, the table should be in 3NF, and for every FD. LHS is super key.

Even when a database is in 3^rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key.