Skip to content
Home » Structured Query Language » Normalization In SQL

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

 

Join Telegram : Click Here

 

All Full Stack Java Study Material

 

Structured Query Language

 

Job’s For Fresher

 

Share This Information To Your Friends and Your College Group’s, To Help Them !