What is normalization? Explain 3NF with example.
Database Management System
Computer Science Engineering
654
Angelina
Third Normal Form (3NF) is a database normalization technique used to further eliminate data redundancy and improve data integrity beyond 2NF. In 3NF, a table is said to be in 3NF if it is already in 2NF and every non-key attribute is not transitively dependent on the primary key.
A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key, then it is in Third Normal Form (3NF).
To explain 3NF with an example, consider a table called "Employees" with the following columns:
This table is not in 3NF because the non-key attribute "Department Head" is transitively dependent on the primary key through the "Department Name" column. The Department Head can be determined from the Department Name, and the Department Name can be determined from the Department ID, which is part of the primary key.
To normalize this table to 3NF, we need to split it into three tables: "Employees," "Departments," and "Department Heads."
The "Employees" table will have the following columns:
The "Department Heads" table will have the following columns:
By doing this, we have eliminated the transitive dependency between the Employee ID and the Department Head, and we have ensured that each table contains data that is relevant and not transitively dependent on the primary key.
To explain 3NF with an example, consider a table called "EMPLOYEE_DETAIL " with the following columns:
EMPLOYEE_DETAIL table:
EMP_ID | EMP_NAME | EMP_ZIP | EMP_STATE | EMP_CITY |
---|---|---|---|---|
222 | Harry | 201010 | UP | Noida |
333 | Stephan | 02228 | US | Boston |
444 | Lan | 60007 | US | Chicago |
555 | Katharine | 06389 | UK | Norwich |
666 | John | 462007 | MP | Bhopal |
Super key in the table above: {EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}....so on
Candidate key: {EMP_ID}
Non-prime attributes: In the given table, 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). It violates the rule of third normal form.
That's why we need to move the EMP_CITY and EMP_STATE to the new <EMPLOYEE_ZIP> table, with EMP_ZIP as a Primary key.
EMPLOYEE table:
EMP_ID | EMP_NAME | EMP_ZIP |
---|---|---|
222 | Harry | 201010 |
333 | Stephan | 02228 |
444 | Lan | 60007 |
555 | Katharine | 06389 |
666 | John | 462007 |
EMPLOYEE_ZIP table:
EMP_ZIP | EMP_STATE | EMP_CITY |
---|---|---|
201010 | UP | Noida |
02228 | US | Boston |
60007 | US | Chicago |
06389 | UK | Norwich |
462007 | MP | Bhopal |