Database Design and Relational Theory

Chapter 6. Preserving FDs

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 Figure 1-1 are shown in Figure 6-1.

Figure 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.^[51] The constraint in question can be stated as follows:

       CONSTRAINT ...
          COUNT ( ( JOIN { SNC , ST } ) { CITY } ) =
          COUNT ( ( JOIN { SNC , ST } ) { CITY , STATUS } ) ;

Explanation: What this constraint says is, if we join SNC and ST, we get a result—call it S—in which the number of distinct cities is equal to the ...