3NF Third Normal Form

A relation can be in Third normal form if it is in the second normal form and does not have any transitive partial dependency. This form is used to reduce the amount of data duplication. It also increases data integrity. In case there is no transitive dependency for non-prime attributes, it is said to be in the third normal form.

A relation can be said to be in the third normal form if at least one of the following is true for X → Y.

  1. Y is a prime attribute
  2. X is a superkey



11 Jai 33456 HR Gurgaon
12 Navdeep 12312 HR Ambala
13 Shivanshu 67868 US Chicago
14 Mahesh 34535 UK Norwich
15 Vishal 67868 UP Noida

The super key in the above table is


Candidate key: {EMP_ID}

Non-prime attributes: Here all attributes except {EMP_ID} are non-prime.

Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP dependent on EMP_ID. The non-prime attributes (EMP_STATE, EMP_CITY) transitively dependent on super key(EMP_ID). This violates the rules of the third normal form. We need to move the EMP_CITY and EMP_STATE to the new <EMPLOYEE_ZIP> table, with EMP_ZIP as a Primary key.

12 Jai 33456
13 Navdeep 12312
14 Shivanshu 67868
15 Mahesh 34535
16 Vishal 67868


33456 HR Gurgaon
12312 HR Ambala
67868 US Chicago
34535 UK Norwich
67868 UP Noida


Please follow and like us:
Content Protection by DMCA.com