Database Reference
In-Depth Information
4.7 The Third Normal Form
Transitive dependence refers to dependence among non-key attributes. In particular,
if A → B and B → C, then C is transitively dependent on A (i.e. A→ C transitively).
In the previous section, relation R1 is problematic because it is not in 3NF. If it is
desirable to store additional information about the locations as indicated in the previous
section, then we must be smart enough to discern that location is to be treated as an
entity with attributes such as location code, location name (and perhaps others). Using
Heath's theorem, we may therefore decompose R1 as follows:
R4
{Supl#, Sname, LocationCode} PK[Supl#]
R5
{LocationCode, LocationName} PK[LocationCode]
We now check to ensure that the relations are in 3NF (and they are). Again, please
take careful notice of the consequences of our actions to this point:
1.
The problems with relations in 2NF only have been addressed.
2.
Again, by decomposing, we have introduced a foreign key in
relation
R4
.
3.
We can rebuild relation
R1
by simply JOINing
R4
with
R5
on
the foreign key.
4.
From the definition of 3NF, it should be obvious that if you
have a relation with one candidate key and
n
mutually
independent non-key attributes, or only one non-key
attribute, it is in 3NF.
Problems with Relations in 3NF Only
Relations
R2
,
R3
,
R4
, and
R5
above are all in 3NF. However, it has been found that
3NF-only relations suffer from certain inadequacies. It is well known that 3NF does not
deal satisfactorily with cases where the following circumstances hold:
•
There are multiple composite candidate keys in a relation.
•
The candidate keys overlap (i.e. have at least one attribute in
common).
