A table is in third normal form if it is second normal form and no non-primary key attribute is transitively dependant on the primary key.

We know that partial dependency is where a attribute is only dependant on a subset of the primary key. We know that fully dependant means that it is dependant on the whole of the primary key. Transitive dependency is a little more complicated. It is defined as follows, where A,B and C are attributes of a relation.

If A is dependant on B and B is dependant on C then A is transitively dependant on C.

So let us consider an example. Product is dependant on productID. Supplier is dependant on the product. As such the supplier is transitively dependant on the productID.

3-NF is responsible for removing this dependency. It effectively does the same job as 2-NF, however it is responsible for tables which only have a single attribute for their primary key.

Product

Supplier

We need to add the supplier ID back to the product table just because otherwise we would have no way of accessing the supplier. This step might be seen as one too far. There could be a update problem if a supplier change their name. However it may be that this is rare enough for it not to be a huge problem. This is a good example of how 3-NF can help or may be a hindrance. It is a good idea to always attempt 3-NF but look very seriously at the results which are produced.

Customer( CustomerID, Name)

Order( CustomerID, OrderNumber, ProductID)

Product( ProductID, Product, SupplierID)

Supplier( SupplierID, Supplier)