The 4NF also known as BCNF NF
================================================================================
Normalization:-Normalization is a mechanism of decomposing tables to remove redundancy of data and removal of other characteristics like insertion, deletion and update anomalies.
To understand Insertion, deletion and update anomalies let’s take an example of “EmpDetail” table.
Eid
|
Name
|
Visa Type
|
Address
|
101
|
Vikas
|
H1
|
Faridabad
|
101
|
Vikas
|
B1
|
Faridabad
|
102
|
Ramesh
|
Null
|
Amritsar
|
103
|
Prakash
|
L1
|
Raipur
|
In the above table you can see that we have employee details along with their Visa details. Employee Ramesh has no Visa detail as Visa Type column has null value in it.
Insertion Anomalies: – If we want to insert an employee details which has no Visa that leads insertion anomalies as Visa Type value would be null.
Deletion Anomalies: - If employee Prakash’s Visa got expired and we want to delete that Visa details for Prakash, whole data will be deleted that leads deletion anomalies.
Update Anomalies:- If we want to update data for Eid =101 , we need to make sure we are updating both rows otherwise data would be inconsistent.
1st Normal Form: – First normal form says that there should not be any repeating group in a row and First Normal form also advocates about the uniqueness of each row in a table i.e. primary key should be there in each table.
Lets take an example to understand 1st Normal form.
Eid
|
Name
|
Address1
|
Address2
|
101
|
Vikas
|
Faridabad
|
Gurgaon
|
102
|
Ramesh
|
Jaipur
|
Null
|
103
|
Prakash
|
Noida
|
Null
|
In above Emloyee table, we have repeating group i.e. Address1 and Address2 which is violating first normal form. To be in first normal form we can split employee table in two tables as mentioned below.
Employee
| |
AddressDetail
|
Eid
|
Name
| |
Eid
|
Address
|
101
|
Vikas
| |
101
|
Faridabad
|
102
|
Ramesh
| |
101
|
Gurgaon
|
103
|
Prakash
| |
102
|
Jaipur
|
| | | | |
We have decomposed employee table in two tables, Employee and AddressDetail, in employee table Eid is primary key and in AddressDetail table Eid and Address both can be the part of primary key . If we want to get the employee detail and address detail we have to join the both tables using joins.
2nd Normal Form:- For a table to be in 2nd Normal form it should follow the 1st Normal form and each Non Key columns should be fully dependent on the primary key or in other words there should not be any partial dependency. To understand this, let’s take an example.
Eid
|
ProjectID
|
ProjectName
|
EmpName
|
101
|
P01
|
Syntal
|
Vikas
|
102
|
P02
|
Cris
|
Priya
|
101
|
P03
|
Vintex
|
Vikas
|
103
|
P04
|
Alex
|
Rahul
|
104
|
P05
|
Dwan
|
Shivam
|
The above table has mapping of Project and employee and combination of projectID and Eid is the primary key of the table. Projectname is dependent on Project ID not the complete primary key similarly, EmpName is directly dependent on Eid not the whole primary key hence this is the violation of 2nd Normal form. To be in 2nd Normal form we need to split this table in two tables as shown below.
EmpDetail
| |
ProjectDetail
|
Eid
|
EmpName
| |
ProjectID
|
ProjectName
|
101
|
Vikas
| |
P01
|
Syntal
|
102
|
Priya
| |
P02
|
Cris
|
101
|
Vikas
| |
P03
|
Vintex
|
103
|
Rahul
| |
P04
|
Alex
|
104
|
Shivam
| |
P05
|
Dwan
|
I have split the table in two tables, one is EmpDetail and other one is ProjectDetail where in Eid is the primary key of Empdetail and ProjectID is the primary key for ProjectDetail table. And non key attributes are fully dependent in each table, now we can say that tables are in 2ndNormal form.
3rd Normal Form:- To be in 3rd Normal form table should follow first two normal forms apart from that there should not be any transitive relationship in columns. Or in other words each column should directly dependent on primary key. E.g. if there are three columns in a table (A,B,C) and A is the primary key of the table and B is dependent on A and C is dependent on B.
AßBßC
Lets take an example to understand this fact.
EmpDetail
|
Eid
|
Basic_Sal
|
HRA(50% of Basic)
|
101
|
4000
|
2000
|
102
|
6000
|
3000
|
103
|
5000
|
2500
|
104
|
9000
|
4500
|
105
|
4600
|
2300
|
In the above table Eid is the primary key and Basic Salary is non key column which is dependent on Primary key and HRA is also a non key column which is not directly dependent on Primary key(Eid) rather it is dependent on other non key column Basic_Sal ,as HRA value is 50% of Basic_Sal. In this way this table violating 3rd normal form. To be in 3rd Normal form we will spilt above table in two tables as shown below.
BasicSalaryDetail
| |
HRADetail
|
Eid
|
Basic_Sal
| |
Eid
|
HRA
|
101
|
4000
| |
101
|
4000
|
102
|
6000
| |
102
|
6000
|
103
|
5000
| |
103
|
5000
|
104
|
9000
| |
104
|
9000
|
105
|
4600
| |
105
|
4600
|
We have split the above table in two tables, BasicSalaryDetail and HRADetail, now these two tables are following all three normal forms. Lets see how.
- Both tables do not contain any repeating group and each row in both table can be uniquely identified by primary key(Eid) , in that way 1st normal form’s condition is satisfied.
- In both tables non key columns (Basic_Sal and HRA) fully dependent on Primary key in that way 2nd normal form’s condition is satisfied.
- We have already removed the transitive relationship, hence both tables are in 3rd normal form.