Second Normal Form (2NF):

FIrst, the table must be in 1NF, plus, we want to make sure that every Non-Primary-Key attribute (field) is fully functionally dependent upon the ENTIRE Primary-Key for its existence.  This rule ONLY applies when you have a multi-part (concantonated) Primary Key (PK).

Take each non-key field, and ask this question: If I knew part of the PK, could I tell what the non-key field would be.

In this inventory table, Description combined with Supplier is our PK.  This is because we have two of the same hammers that come from different suppliers.

There are two non-key fields.  So, here are the questions:

If I know just Description, can I find out Cost?  No, because we have more than one supplier for the same product.
If I know just Supplier, and I find out Cost?  No, because I need to know what the Item is as well.

Therefore, Cost is fully, functionally dependent upon the ENTIRE PK (Description-Supplier) for its existence.

If I know just Description, can I find out Supplier Address?  No, because we have more than one supplier for the same product.
If I know just Supplier, amd I find out Supplier Address?  Yes.  The Address does not depend upon the Description of the item.

Therefore, Supplier Address is NOT functionally dependent upon the ENTIRE PK (Description-Supplier) for its existence.

We must get rid of Supplier Address from this table.

We still need a field for Supplier Address that will tell us what supplier it is connected with, So I also brought Supplier from the other table with me.  At this point, since it is the "Supplier" table, I can also rename the "Supplier" filed to "Name."  Name is the PK for this new table.

Third Normal Form

© Ken Gibson