A table is said to be in 3NF when it is in 2NF and every non-key attributes is functionally dependent only on the primary key.
Consider the table EMPLOYEE
The problems with dependencies of this kind are:
- Insertion : The department head of a new department that does not have any employees at present cannot be entered in the DEPTHEAD column. This is because the primary key is unknown.
- Updation : For a given department, the code for a particular department head (DEPTHEAD) is repeated several times. Hence, if a department head moves to another department, the change will have to be made consistently across the table.
- Deletion : If the record of an employee is deleted, the information regarding the head of the department will also be deleted. Hence, there will be a loss of information.
You must check if the table is in 3NF.Since each cell in the table has a single value, the table is in 1NF.
The primary key in the EMPLOYEE table is ECODE. For each value of ECODE, there is exactly one value of DEPT. Hence, the attribute DEPT is functionally dependent on the primary key, ECODE. Similarly, for each value of ECODE, there is exactly one value of DEPTHEAD. Therefore, DEPTHEAD is functionally dependent on the primary key ECODE. Hence, all the attributes are functionally dependent on the whole key, ECODE. Hence, the table is in 2NF.
However, the attribute DEPTHEAD is dependent on the attribute DEPT also. As per 3NF, all non-key attributes, have to be functionally dependent on DEPT, which is not a primary key.
