Database Reference
In-Depth Information
Example 4:
Problems with Relations in 1NF Only
Relation
R0
of the previous section is in 1NF only. However it is undesirable to store it as
is due to a number of problems. In the interest of clarity, the relation is restated here:
R0
{Supl#, SuplName, Item#, ItemName, Quantity, SuplStatus, Location}
Functional dependencies of
R0
as illustrated in Figure
4-2
are as follows:
•
FD1: [Suppl#, Item#] → {Quantity, SuplName, SuplStatus,
Location, ItemName}
•
FD2: Suppl# → {SuplName, SuplStatus, Location}
•
FD3: Item# → ItemName
The following data anomalies exist with
R0
(and most relations in 1NF only):
•
Replication of data:
Every time we record a supplier - item pair,
we also have to record supplier name and item name.
•
Insertion anomaly
: We cannot insert a new item until it is
supplied; neither can we insert a new supplier until that supplier
supplies some item.
•
Deletion anomaly
: We cannot delete an item or a supplier
without destroying an entire shipment, as well as information
about a supplier's location.
•
Update anomaly
: If we desire to update a supplier's location or
item name, we have to update several records, in fact, an entire
shipment, due to the duplication problem.
Insertion, deletion update anomalies constitute
modification anomalies,
caused by
duplication of data due to improper database design.
