2NF and 3NF

The 2NF and 3NF are very similar--the 2NF deals with composite primary keys and the 3NF deals with single primary keys. In general, if you do an ER diagram, convert many-to-many relationships to entities, and then convert all entities to tables, then your tables will already be in 3NF form.

Second normal form is violated when a non-key field is a fact about a subset of a key. It is only relevant when the key is composite, i.e., consists of several fields. Consider the following inventory record:

---------------------------------------------------

| PART | WAREHOUSE | QUANTITY | WAREHOUSE-ADDRESS |

====================-------------------------------

The key here consists of the PART and WAREHOUSE fields together, but WAREHOUSE-ADDRESS is a fact about the WAREHOUSE alone. The basic problems with this design are:

• The warehouse address is repeated in every record that refers to a part stored in that warehouse.
• If the address of the warehouse changes, every record referring to a part stored in that warehouse must be updated. Because of the redundancy, the data might become inconsistent, with different records showing different addresses for the same warehouse.
• If at some point in time there are no parts stored in the warehouse, there may be no record in which to keep the warehouse's address.

To satisfy second normal form, the record shown above should be decomposed into (replaced by) the two records:

------------------------------- --------------------------------- 

| PART | WAREHOUSE | QUANTITY | | WAREHOUSE | WAREHOUSE-ADDRESS |

====================----------- =============--------------------

The 3NF differs from the 2NF in that all non-key attributes in 3NF are required to be directly dependent on the primary key of the relation. The 3NF therefore insists that all facts in the relation are about the key (or the thing that the key identifies), the whole key and nothing but the key.

Third normal form is violated when a non-key field is a fact about another non-key field, as in

------------------------------------

| EMPLOYEE | DEPARTMENT | LOCATION |

============------------------------

The EMPLOYEE field is the key. If each department is located in one place, then the LOCATION field is a fact about the DEPARTMENT -- in addition to being a fact about the EMPLOYEE. The problems with this design are the same as those caused by violations of second normal form:

• The department's location is repeated in the record of every employee assigned to that department.
• If the location of the department changes, every such record must be updated.
• Because of the redundancy, the data might become inconsistent, with different records showing different locations for the same department.
• If a department has no employees, there may be no record in which to keep the department's location.

To satisfy third normal form, the record shown above should be decomposed into the two records:

------------------------- -------------------------

| EMPLOYEE | DEPARTMENT | | DEPARTMENT | LOCATION |

============-------------  ==============-----------

To summarize, a record is in second and third normal forms if every field is either part of the key or provides a (single-valued) fact about exactly the whole key and nothing else.