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

 

Example

EMP_ID EMP_NAME EMP_ZIP EMP_STATE EMP_CITY
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

{EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}….so on

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.

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

 

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

 

Please Share