2-NF form in DBMS

2-NF form in DBMS

 

In this article, we will learn about the 2-NF form in DBMS.

Normalization is a process where a table is broken into further tables  to ensure the best that they are free from problems due to insertion updation and deletion

Even if the table is in First Normal Form there are still the anomalies  of insertion updation and deletion hence we go for second normal form.

Learn more about Normalization here on this page.

2-NF form in DBMS

Definition

A table is said to be in Second Normal Form if it satisfies the following two properties

1. It must be in First Normal Form
2. It should be free from all kinds of partial dependencies

Problem Table: Score table

student_idsubject_idmarksteacher
19113A70Java Teacher
19115D75C++ Teacher
19113A80Java Teacher

 

  • The above table is in First Normal Form as it is free from multivalued attributes
  • The combination of student_id and subject_id columns will become the primary key for the table.
  • But the column teacher entirely depends on subject_id column and independent of student_id.
  • So even if the table is in First Normal Form it suffers from problems due to insertion updation and deletion hence we need to go for second normal form.

 

Procedure to convert a table into 2-NF

  • Identify the partial and full dependencies and apply decomposition rule
  • In the above table column teacher is dependent on subject_id
  • The simple solution is to create a separate table for subject_id and teacher and removing it from Score table
  • Resulting tables which are in 2 normal form are

Score Table 

student_idsubject_idmarks
19113A70
19115D75
19113A80

Subject Table

subject_idteacher
13AJava Teacher
15DC++ Teacher