Normalizing the Data › Normal Forms of Data › Third Normal Form

Third Normal Form

A data table is in third normal form if it is in second normal form and no non-key attribute determines the value of another non-key attribute; a table that is in third normal form contains no transitive dependencies among non-key attributes.

Table not in third normal form

The EMPLOYEE table shown below is not in third normal form:

               EMPLOYEE

EMP ID  EMP NAME    DEPT ID    DEPT NAME
1216    SMITH       1000       OPERATIONS
1041    JONES       3500       MARKETING
1633    DAVIS       3400       DOCUMENTATION
1063    EVANS       2000       SUPPORT

Let's assume that EMP ID is the primary key of the EMPLOYEE table shown above. In this case, the table is not in third normal form because a non-key attribute has a transitive dependency on another non-key attribute. The DEPT NAME attribute is dependent on the DEPT ID attribute; a DEPT NAME value can be determined by the value of a particular DEPT ID.

Normalizing the table

To normalize the EMPLOYEE table shown above, you could break down this table into two separate tables:

   EMPLOYEE         DEPARTMENT

EMP ID  EMP NAME          DEPT ID    DEPT NAME
1216    SMITH             1000       OPERATIONS
1041    JONES             3500       MARKETING
1633    DAVIS             3400       DOCUMENTATION
1063    EVANS             2000       SUPPORT

Since the EMP NAME attribute is not dependent on any other non-key attribute, the EMPLOYEE table shown above is in third normal form. In addition, since the DEPT NAME attribute is not dependent on any other non-key attribute, the DEPARTMENT table is also in third normal form.

Rules of first, second, and third normal forms

The following table summarizes the rules of each normal form of data organization.