13.1 Third Normal Form

A table is in third normal form if:

		It is in second normal form and
		The table does not have a non-key attribute that is transitively dependent on the primary key.

Thus, 3NF eliminates a non-key attribute that is functionally dependent (relies on) another non-key attribute that is functionally dependent on the table’s primary key.

Consider the Book table shown in Figure 13.1 that has the Book_Id attribute as its primary key.

		The table is in 1NF and 2NF but violates 3NF as its Affiliation attribute is transitively dependent on the primary key Book_Id.
		This is because affiliation is a fact about the author and thus functionally dependent on the Author_Id attribute.
		As the latter is functionally dependent on Book_Id, the transitive dependency exists between Affiliation and the primary key.
		The problems with this table design are the same as those caused by violating 2NF (which are discussed in Lecture 12).

13.1 Third Normal Form

To normalize the Book table into 3NF, the Affiliation attribute has to be moved to a new table, thus decomposing the Book table into two tables as shown in Figure 13.2.

13.1 Third Normal Form

Example

Consider the Employees table shown in Figure 13.3. The Employee_Id attribute is the table’s primary key. The table is not in 3NF. Explain why and normalize the table into 3NF.

13.1 Third Normal Form

Solution:

	The table is in 1NF as it does not include any multi-valued attributes.
	As there are no attributes that are partially dependent on the primary key and the table is in 1NF, then the table is in 2NF.
	Although the table is in 2NF, it is not 3NF as the Zip Code attribute is transitively functionally dependent on the primary key Employee_Id. Zip Code is functionally dependent on Address and the latter is functionally dependent on Employee_Id.
	To normalize the Employees table into 3NF, transitive functionally dependency has to be eliminated by moving Zip Code into a new table.
	The Employees table is split into two tables as shown in Figure 13.4.

13.1 Third Normal Form