Third normal form

Whereas in second normal form we are eliminating partial dependencies, in third normal form (3NF) we are eliminating transitive dependencies. A transitive dependency exists if one non-key attribute is functionally dependent on another non-key attribute. In the consultant example, you will notice that there are two non-key attributes called grade and rate in the CONSULTANT table. If we assume that the consultant's grade determines their daily rate, then we have an instance of a transitive dependency:

The term transitive indicates a mathematical concept in which if entity A is related to entity B and entity B is related in the same way to entity C, then by implication entity A is related to entity C. A real-world example is the relationship is the sibling of: if John is the sibling of Jack, and Jack is the sibling of Jenny, then John is the sibling of Jenny.

In the consultants example, cons_id determines grade, and grade determines rate. Therefore, cons_id also determines rate and this is the transitive dependency shown as a dashed arrow in the diagram. Because the other two dependencies exist, however, we do not need to represent the transitive dependency explicitly.

To remove the transitive dependency, extract the non-key determinant (grade) and its dependents (rate) into a new relation, and make the determinant the primary key. A 'copy' of the primary key remains in the original relation where it plays the role of foreign key:

The final set of relations for the consultants database in 3NF can be written:

consultant(cons_id, cons_name, grade)

grade(grade, daily_rate)

project(proj_code, proj_name, start_date, end_date)

cons_proj(proj_code, cons_id, days)