Third Normal Form (3NF)

Process of transforming table from 2NF to 3NF is:

  • Identifying determinants that are not part of the table primary key.
  • Removing partial dependencies to a new table.
  • Determining the primary key of the new table.

Identifying Determinants That Are Not Part of Primary Key

For process of transforming tables from 2NF to 3NF, we look only for determinants that are not part of the primary key and this is the only differences compare to previous sub-chapter. Also, tables with zero or only one non-key attributes should be ignored - these tables are already in 3NF.

Process of identifying determinants that are not part of PK is:

  • Take one non-key attribute of table and see if it is dependent on another non-key attribute?
  • If answer to previous question is yes then we got one determinant.
  • If answer to previous question is no then check against other non-key attribute.
  • Repeat this process for every non-key table attribute.

Taking this procedure, we can identify determinants in Project and Project_Employee tables and show this as in next figure. Determinants of Project and Project_Employee Table

Project Manager is dependent upon Project Manager No attribute in table Project and Department Name is dependent upon Department No attribute in table Project_Employee.

Removing partial dependencies to a new table

In previous figure, partial dependencies are: Project Manager and Department Name. We remove this attributes in new tables that we call Manager and Department. In order to have a cross-reference between old and new tables we must also copy determinant to the new table. Manager table also contain Project Manager No attribute from Project table and Department table also contain Department No attribute from Project_Employee table.

Determining the Primary Key of the New Table

The determinant from previous step becomes a primary key of new table. Next figure shows tables in Third Normal Form. Third Normal Form