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 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.
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:
Let’s say we have table Professor, primary key of table is Professor_Id. The table tell us professors and departments info.
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.
Now we add/delete professors and departments without losing any information.
Download as PDF
Read next: Third Normal Form (3NF) ››
« Back to Course page