Databases Reference
In-Depth Information
{ 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 Fig. 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.
THE NORMAL FORM HIERARCHY
As you know, there are many different normal forms. Fig. 3.3 is our first take on the normal form hierarchy (but
please note immediately that I'll be expanding the hierarchy later in this topic—in Chapter 13, to be specific).
Note:
You might think the hierarchy is upside down, since it shows the highest normal form at the bottom and the lowest
at the top. I don't want to argue the point; let me just say that showing it the way the figure does fits better (in my
view) with the fact that, e.g., all 2NF relvars are in 1NF but some 1NF relvars aren't in 2NF. To elaborate on the
figure:
There are several different normal forms: first, second, third, and so on. The figure shows six such, but as
you can see they aren't labeled first, ..., sixth (not quite)—there's an interloper, BCNF, between third and
fourth.
10
I'll explain the reason for this terminological oddity in Chapter 4; for now, let me just say that the
name BCNF is short for
Boyce/Codd normal form
.
Note:
Despite the BCNF exception, it's convenient to
use the term
nth normal form
to refer generically to the different levels of normalization, and I'll adopt that
usage from time to time in what follows.
In general, the higher the level of normalization the better, from a design point of view—because the higher
the level of normalization, the more redundancies are prevented and the fewer update anomalies can occur.
10
There's also a gap between BCNF and 4NF, to reflect the fact that there's a kind of conceptual jump in the hierarchy between the first four
normal forms and the last two. See Part III of this topic.
