Third Normal Form (3NF)
A table is said to be in Third Normal Form (3NF) when:
It is in 2NF and It contains no Transitive Dependencies.
In the following Figure, it shows a transitive dependency, which can generate anomalies.
For example, if the charge per hour changes for a job classification held by many employees, that change must be made for each of those employees.
If you forget to update some of the employee records that are affected by the charge per hour change, different employees with the same job description will generate different hourly charges.
You May Also Like:
Normalization and its advantages
1st Normal Form
2nd Normal Form
Conversion to Third Normal Form:
The data anomalies created by the database organization shown in the above Figure are easily eliminated by completing the following two steps:
Step 1: Make New Tables to Eliminate Transitive Dependencies
For every transitive dependency, write a copy of its determinant as a primary key for a new table. A determinant is any attribute whose value determines other values within a row. If you have three different transitive dependencies, you will have three different determinants. As with the conversion to 2NF, it is important that the determinant remain in the original table to serve as a foreign key.
Therefore, write the determinant for this transitive dependency as:
JOB_CLASS
Step 2: Reassign Corresponding Dependent Attributes
identify the attributes that are dependent on each determinant identified in Step 1. Place the dependent attributes in the new tables with their determinants and remove them from their original tables. In this example, eliminate CHG_HOUR from the EMPLOYEE table shown in the above Figure to leave the EMPLOYEE table dependency definition as:
EMP_NUM ----> EMP_NAME, JOB_CLASS
Draw a new dependency diagram to show all of the tables you have defined in Steps 1 and 2. Name the table to reflect its contents and function. In this case, JOB seems appropriate. Check all of the tables to make sure that each table has a determinant and that no table contains inappropriate dependencies. When you have completed these steps, you will see the results as in the following figure.
In other words, after the 3NF conversion has been completed, your database will contain four tables:
PROJECT (PROJ_NUM, PROJ_NAME)
EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS)
JOB (JOB_CLASS, CHG_HOUR)
ASSIGNMENT (PROJ_NUM, EMP_NUM, ASSIGN_HOURS)
Note that this conversion has eliminated the original EMPLOYEE table’s transitive dependency. So the tables are now said to be in third normal form (3NF).
You May Also Like:
