An entity is in second normal form if each attribute that
is not in the primary key provides a fact that depends on the entire
key.
A violation of the second normal form occurs when a nonprimary
key attribute is a fact about a subset of a composite key.
Example: An inventory entity records
quantities of specific parts that are stored at particular warehouses.
The following figure shows the attributes of the inventory entity.Figure 1. A primary key that
violates second normal form
Here, the primary key consists of the PART and the WAREHOUSE
attributes together. Because the attribute WAREHOUSE_ADDRESS depends
only on the value of WAREHOUSE, the entity violates the rule for second
normal form. This design causes several problems:
Each instance for a part that this warehouse stores repeats the
address of the warehouse.
If the address of the warehouse changes, every instance referring
to a part that is stored in that warehouse must be updated.
Because of the redundancy, the data might become inconsistent.
Different instances could show different addresses for the same warehouse.
If at any time the warehouse has no stored parts, the address
of the warehouse might not exist in any instances in the entity.
To satisfy second normal form, the information in the figure
above would be in two entities, as the following figure shows.
Figure 2. Two entities that
satisfy second normal form
