Databases Reference
In-Depth Information
Chapter 6
P r e s e r v i n g F D s
Nature does require
Her times of preservation
—William Shakespeare:
Henry VIII
Once again consider our usual suppliers relvar S. Since {SNO} is a key, that relvar is certainly subject to the FD
{SNO} → {STATUS}. Thus, taking
X
as {SNO},
Y
as {STATUS}, and
Z
as {SNAME,CITY}, Heath's Theorem
tells us we can decompose that relvar into relvars SNC and ST, where SNC has heading {SNO,SNAME,CITY}
and ST has heading {SNO,STATUS}. Sample values for SNC and ST corresponding to the value shown for S in
Fig. 1.1 are shown in Fig. 6.1.
SNC ST
┌─────┬───────┬────────┐ ┌─────┬────────┐
│ SNO │ SNAME │ CITY │ │ SNO │ STATUS │
├═════┼───────┼────────┤ ├═════┼────────┤
│ S1 │ Smith │ London │ │ S1 │ 20 │
│ S2 │ Jones │ Paris │ │ S2 │ 30 │
│ S3 │ Blake │ Paris │ │ S3 │ 30 │
│ S4 │ Clark │ London │ │ S4 │ 20 │
│ S5 │ Adams │ Athens │ │ S5 │ 30 │
└─────┴───────┴────────┘ └─────┴────────┘
Fig. 6.1: Relvars SNC and ST─sample values
In this decomposition:
Relvars SNC and ST are both in BCNF—{SNO} is the key for both, and the only nontrivial FDs that hold in
those relvars are “arrows out of superkeys.”
What's more, the decomposition is certainly nonloss (as is in fact guaranteed by Heath's Theorem)—if we
join SNC and ST together, we get back to S.
However,
the FD {CITY}
→
{STATUS} has been lost
─by which I mean, of course, that it's been replaced by
a certain multirelvar constraint, as explained in the previous chapter.
1
The constraint in question can be
stated as follows:
CONSTRAINT ...
COUNT ( ( JOIN { SNC , ST } ) { CITY } ) =
COUNT ( ( JOIN { SNC , ST } ) { CITY , STATUS } ) ;
1
To say an FD is “lost” in such circumstances is usual but a trifle inappropriate─all that's happened is (to repeat) that the FD in question has
been replaced by another constraint. But the point is, that other constraint isn't an FD as such.
