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.

Normalization

Benefits of Normalization :

(i) Normalization reduces Data 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 simplifying 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).

Example:
A Student table is given below:

student_idstudent_namestudent_pincodestudent_statestudent_citystudent_district
70001Ram700105UPLucknowMeerut
70002Shyam700095MPIndoreDhar
70003Jodhu700126UPLucknowLucknow
70004Modhu700425WBKolkataKolkata
70005Rakhi711411WBKolkataHowrah

Here Super key is: {student_id}, {student_id, student_name}, {student_id, student_name, student_pincode}
Candidate Key is : {student_id}
3NF Student table, We break into two tables is given below:
1. student table:
student_idstudent_namestudent_pincode
70001Ram700105
70002Shyam700095
70003Jodhu700126
70004Modhu700425
70005Rakhi711411

2. student_pincode table:
student_pincodestudent_statestudent_citystudent_district
700105UPLucknowMeerut
700095MPIndoreDhar
700126UPLucknowLucknow
700425WBKolkataKolkata
711411WBKolkataHowrah

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.

Example:

Emp_idEmp_CountryEmp_DeptDept_TypeEmp_Dept_No
E045EnglandFinancialD01555
E045EnglandAccountingD01525
E060CanadaProductionD02575
E075SwitzerlandMarketingD03590

Here Candidate Key is : {Emp_id, Emp_Dept}
BCNF Emp Details table, We break into three tables is given below:
1.Emp_Country table:
Emp_idEmp_Country
E045England
E060Canada
E075Switzerland

2.Emp_Dept table:

Emp_DeptDept_TypeEmp_Dept_No
FinancialD01555
AccountingD01525
ProductionD02575
MarketingD03590

3.Emp_id_Dept table:

Emp_idEmp_Dept
E045Financial
E045Accounting
E060Production
E075Marketing