- Once our E-R model has been converted into relations, we may find that some relations are not properly specified. There can be a number of problems:
- Deletion Anomaly: Deleting one fact or data point from a relation results in other information being lost.
- Insertion Anomaly: Inserting a new fact or tuple into a relation requires we have information from two or more entities – this situation might not be feasible.
- Update Anomaly: Updating one fact in a relation requires us to update multiple tuples.
- Here is a quick example to illustrate these anomalies: A company has a Purchase Order form:
- Our dutiful consultant creates the E-R Model directly matching the purchase order:
- When we follow the steps to convert to a set of relations this results in two relations (keys are underlined):
PO_HEADER (PO_Number, PODate, Vendor, Ship_To, ...)
LINE_ITEMS (PO_Number, ItemNum, PartNum, Description, Price, Qty)
- Consider some sample data for the LINE_ITEMS relation:
- What are some of the problems with this relation ?
- What happens if we want to add the fact that Order O103 has quantity 5 of part P99 ?
- What happens when we delete item I02 from Order O101 ?
- What happens if we want to change the price of the Plate (P99)?
- These problems occur because the relation in question contains data about 2 or more themes.
- Typical way to solve these anomalies is to split the relation in to two or more relations – This is part of the Process called Normalization discussed next.
Leave a Reply
You must be logged in to post a comment.