1st Normal Form
The updated table below is now in first normal form. But it is not in 2nd normal form.
2nd Normal Form
Definition
To be in 2nd normal form a table must meet the following conditions:
The table is in 1st normal form.
All non-key fields depend on the entire key, not a subset.
Example
Let us look at an example with parts held in warehouses. We wish to track the inventory by part and warehouse. A sample table might be...
INVENTORY(sku,location,quantity, part_description, price)
But this is not correct as part_description depands only on sku, not the entire primary key. To normalize this we would use two tables. Sku in the inventory table becomes also a foreign key to sku in a parts table. The parts table contains the parts_description field which is dependent on sku.
INVENTORY(sku,location,quantity)
PARTS(sku, part_description, price)
2nd normal form violation
Chemical Plants Example
Back to our chemical plants we can see that the plant manager is dependent only on the plant name. The full primary key is both plant name and equipment name. So this is a partial key dependency.
Correcting the 2nd normal form violation
As shown above we will have to use two tables.
Continue
to page 3 - 3rd normal form |