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.

Relvars SNC and ST—sample values

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 ...

Get Database Design and Relational Theory now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.