Database Reference
In-Depth Information
4.6 The Second Normal Form
By non-key attribute, we mean that the attribute is not part of the primary key.
Relation R0 (of the previous section), though in 1NF, is not in 2NF, due to FD2 and
FD3. Using Heath's theorem, we may decompose relation R0 as follows (note that the
abbreviation PK is used to denote the primary key):
R1
{Supl#, Sname, Location, SuplStatus} PK[Suppl#]
R2
{Item#, Itemname} PK[Item#]
R3
{Supl#, Item#, Qty} PK[Supl#, Item#]
We then check to ensure that the resulting relations are in 2NF (and they are).
So based on the definition of 2NF, and on the authority of Heath's theorem, we would
replace
R0
with
R1
,
R2
, and
R3
. Please note the consequences of our treatment of
R0
so far:
1.
The problems with relations in 1NF only have been addressed.
2.
By decomposing, we have introduced foreign keys in relation R3.
3.
JOINing is the opposite of PROJecting. We can rebuild relation
R0 by simply JOINing R3 with R1 and R3 with R2, on the
respective foreign keys.
4.
From the definition of 2NF, two observations should be
obvious: Firstly, if you have a relation with a single attribute
as the primary key, it is automatically in 2NF. Secondly, if you
have a relation with
n
attributes and
n-1
of them form the
primary key, the relation is also in 2NF.
Problems with Relations in 2NF Only
In this example, relations
R2
and
R3
are in 2NF (in fact they are in 3NF
),
but
w
e still
have potential problems with
R1
: What if we have a situation where there may be several
suppliers from a given location? Or what if we want to keep track of locations of interest?
In either case, we would have modification anomalies as described below:
•
Insertion anomaly: We cannot record information about a
location until we have at least one supplier from that location.
•
Deletion anomaly: We cannot delete a particular location without
also deleting supplier(s) from that location.
•
Update anomaly: If we wish to update information on a location,
we have to update all supplier records from that location.
These problems can be addressed if we take the necessary steps to bring R1 into the
third normal form (3NF). But first, we must define what 3NF is.
