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 3rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key.
