Second Normal Form and Third Normal Form - Part 53

Suggested sql server videos
Part 52 - Database Normalization & First Normal Form







In this video will learn about second normal form (2NF) and third normal form (3NF)
A table is said to be in 2NF, if
1. The table meets all the conditions of 1NF
2. Move redundant data to a separate table
3. Create relationship between these tables using foreign keys.

The table below violates second normal form. There is lot of redundant data in the table. Let's say, in my organization there are 100,000 employees and only 2 departments (IT & HR). Since we are storing DeptName, DeptHead and DeptLocation columns also in the same table, all these columns should also be repeated 100,000 times, which results in unnecessary duplication of data.


So this table is clearly violating the rules of the second normal form, and the redundant data can cause the following issues.
1. Disk space wastage
2. Data inconsistency
3. DML queries (Insert, Update, Delete) can become slow

Now, to put this table in the second normal form, we need to break the table into 2, and move the redundant department data (DeptName, DeptHead and DeptLocation) into it's own table. To link the tables with each other, we use the DeptId foreign key. The tables below are in 2NF.


Third Normal Form (3NF):
A table is said to be in 3NF, if the table
1. Meets all the conditions of 1NF and 2NF
2. Does not contain columns (attributes) that are not fully dependent upon the primary key

The table below, violates third normal form, because AnnualSalary column is not fully dependent on the primary key EmpId. The AnnualSalary is also dependent on the Salary column. In fact, to compute the AnnualSalary, we multiply the Salary by 12. Since AnnualSalary is not fully dependent on the primary key, and it can be computed, we can remove this column from the table, which then, will adhere to 3NF.


Let's look at another example of Third Normal Form violation. In the table below, DeptHead column is not fully dependent on EmpId column. DeptHead is also dependent on DeptName. So, this table is not in 3NF.


To put this table in 3NF, we break this down into 2, and then move all the columns that are not fully dependent on the primary key to a separate table as shown below. This design is now in 3NF.