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

• 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 

Subject Table