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:

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
IT_Officer_003_05

Observe the two statements below:

  1. Given a student id, we can certainly figure out the student name.
  2. 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.

Advertisement

Second Normal Form (2NF)

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

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

Table: Professor
IT_Officer_007_01

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
IT_Officer_007_02

Table: Department
 IT_Officer_007_03

Table: ProfessorDepartment
IT_Officer_007_04

Now we add/delete professors and departments without losing any information.

Download as PDF

Read next:  Third Normal Form (3NF) ››

« Back to Course page

Advertisement by Google

Comments

Subscribe Us

Join us to get latest article in your inbox

Advertisement

SUBSCRIBE US ON YOUTUBE

Article of the Week

Follow Us On Facebook