The concept of update anomalies is frequently mentioned in connection with normalization. Now, it should be clear that redundancy of any kind can always lead to anomalies—because redundancy means, loosely, that some piece of information is represented twice, and so there’s always the possibility that the two representations don’t agree (i.e., if one is updated and the other isn’t). More specifically, let’s consider the case of relvar S, where the following FD holds:
{ CITY } → { STATUS }The redundancy, as such, that this FD gives rise to—viz., the fact that a given city has a given status appears many times—has already been discussed. It leads to anomalies like the following (these examples assume the sample value shown for relvar S in Figure 3-1.):
Insertion anomaly: We can’t insert the fact that the status for Rome is 10 until there’s a supplier in Rome.
Deletion anomaly: If we delete the only supplier in Athens, we lose the fact that the status for Athens is 30.
Modification anomaly: We can’t change (“modify”) the city for a given supplier without changing the status for that supplier as well (in general). Likewise, we can’t modify the status for a given supplier without making the same modification for all suppliers in the pertinent city.
Replacing relvar S by the two “projection” relvars SNC and CT solves these problems (how, exactly?). Moreover, let me state for the record that relvar S is (as previously noted) in second normal form and not third, while relvars SNC and CT are both in third normal form, and in fact in BCNF as well. In general, BCNF is the solution to the problems caused by the kinds of anomalies listed above.