Second Normal Form (2NF)

Before going through the second normal form, first let’s understand the concept of functional dependency. Also read following article if you have not already gone through them for better clarity:

Table of Contents

Functional Dependency

Functional dependency defines dependency between columns of a table. Let’s say we have a Table have two columns A and B then “Column B is said to be functionally dependent on column A if, given A we can precisely determine B.” For example, we have table Student in which Student_Id is primary key.

Table: Student

Observe the two statements below:

Given a student id, we can certainly figure out the student name.
Given a student name, we can certainly figure out the student id.

Statement 1 is true but statement 2 seems to be false as there can be more than one student with same name in a class. Thus, we can determine with confidence a student’s name, given her student Id. However, the reverse is not true. Therefore, Student_Name is functionally dependent on Student_Id.

Second Normal Form (2NF)

A table is in second normal form (2NF), if it satisfies the following conditions:

The table is in first normal form (1NF).
All the non-key columns are dependent upon the primary key of table.

Let’s say we have table Professor, primary key of table is Professor_Id. The table tell us professors and departments info.

Table: Professor

Non-key attribute Professor_Name can be determined by Professor_Id but other non-key attributes like Department_Id, Department_Title cannot be determined by primary key Professor_Id. If we want to add new department in the table then we cannot do it, as Professor_Id is primary key and primary key cannot be null. Thus it created an insertion anomaly for us because we cannot add new department without adding a new professor. Similarly we cannot delete any department as it will delete professor information also. The solution is to create three tables: Professor, Department, ProfessorDepartment.

Table: Professor