Normalization in DBMS: 1NF, 2NF, 3NF and BCNF

Normalization :

Normalization is a process of simplifying the relationship among data elements in a record. Normalization replaces a collection of data in a record structure by another record design which is simpler and more predictable. Normalization covert E-R model into Tables or Relations.

Benefits of Normalization :

(i) Normalization reduces redundancy, it is the unnecessary repetition of a field.

(ii) During the process of normalization, we can identify dependencies that causing the problems when deleting or updating.

(iii) Normalization helps in simplify the structure of the tables.

First Normal Form (1NF) :

When a table has no repeating groups, then it said to be First Normal Form (1NF). The repeating columns or fields present in an unauthorized table are removed from the table and put into a separate table. These tables are dependent on the parent table from which is derived. The key of these tables must also be a part of the parent table so that the parent table and the derived tables can be related to each other.
Example:
An Employee table is given below:

employeedepartmentage
RamMarketing25
ShyamProduction, Purchasing30
MadhuAccounting24

1NF Employee table is given below:
employeedepartmentage
RamMarketing25
ShyamProduction30
ShyamPurchasing30
MadhuAccounting24

Second Normal Form (2NF) :

If a table, all non-key fields are fully dependent on the whole key then it is said to be Second Normal Form (2NF). It means that each field in a table must depend upon the entire key, they don’t depend upon the combination key, they are a move to another table on whose key they depend on. The structure which doesn’t contain combination keys is automatically in the Second Normal Form.
Example:
A Student table is given below:

student_idsubjectage
150Java19
155C20
155Python20
175DBMS21

Here Candidate key is : { student_id, subject}
The non-key attribute is: age
2NF Student table, We break into two tables is given below:
1. student_details table:
student_idage
15019
15520
17521

2. student_subject table:
student_idsubject
150Java
155C
155Python
175DBMS

Third Normal Form (3NF) :

If a table, all non-key fields are fully independent of all other non-key fields then it is said to be Third Normal Form (3NF).

Boyce Codd Normal Form (BCNF) :

Boyce Codd Normal Form (BCNF) was proposed as a simpler form of Third Normal Form (3NF). But it is much more strict than 3NF that means every relation in BCNF is also in 3NF.