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.
- Y is a prime attribute
- 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 |
