Databases Reference
In-Depth Information
{STATUS}, which also holds in relvar S, “disappears,” in a sense; thus we have indeed “gotten rid of it.” But what
does it mean to say the FD has disappeared? The answer is: It's been replaced by a multirelvar constraint (that is, a
constraint that spans two or more relvars). So the constraint certainly still exists—it just isn't an FD any more.
3
Similar remarks apply whenever I talk, elsewhere in this topic, of “getting rid of” some dependency.
HEATH'S THEOREM
Consider relvar S once again, with its FD {CITY} → {STATUS}. Suppose we decompose that relvar, not as in
Chapter 3 into relvars SNC and CT, but instead into relvars
SNT
and CT—where CT is the same as before, but SNT
has heading {SNO,SNAME,STATUS} instead of {SNO,SNAME,CITY}. Sample values for SNT and CT
corresponding to the value shown for S in Fig. 1.1 are shown in Fig. 5.1 below. From that figure, I hope you can see
that:
Relvars SNT and CT are both in BCNF (the keys are {SNO} and {CITY}, respectively, and the only
nontrivial FDs that hold in those relvars are “arrows out of superkeys”).
Unlike the decomposition in Chapter 3, however, this decomposition is not nonloss but
lossy
. For example,
we can't tell from Fig. 5.1 whether supplier S2 is in Paris or Athens—note what happens if we join the two
projections together
4
—and so we've lost information.
SNT CT
┌─────┬───────┬────────┐ ┌────────┬────────┐
│ SNO │ SNAME │ STATUS │ │ CITY │ STATUS │
├═════┼───────┼────────┤ ├════════┼────────┤
│ S1 │ Smith │ 20 │ │ Athens │ 30 │
│ S2 │ Jones │ 30 │ │ London │ 20 │
│ S3 │ Blake │ 30 │ │ Paris │ 30 │
│ S4 │ Clark │ 20 │ └────────┴────────┘
│ S5 │ Adams │ 30 │
└─────┴───────┴────────┘
Fig. 5.1: Relvars SNT and CT─sample values
Let's take a slightly closer look at this example. First of all, here are the predicates for relvars SNT and CT:
SNC:
Supplier SNO is named SNAME and has status STATUS.
CT:
City CITY has status STATUS.
So the predicate for the join of those two relvars is:
Supplier SNO is named SNAME and has status STATUS
and
city CITY has status STATUS.
3
Well ... it
is
an FD, but one that holds in the join of two relvars, viz., SNC and CT, rather than in an individual relvar as such. Note, however,
that enforcing the key constraints on those two relvars will enforce that multirelvar constraint “automatically”; that is, the multirelvar constraint
in question is implied by, or is a logical consequence of, certain explicitly declared constraints.
4
See the remarks on
lossy joins
in a footnote in the section “Normalization Serves Two Purposes” in Chapter 3.
