### In this 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.

## 0 comments:

## Post a Comment

Note: only a member of this blog may post a comment.