O'Reilly logo

Database Design and Relational Theory by C.J. Date

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required