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

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 Figure 1-1 are shown in Figure 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 Figure 5-1 whether supplier S2 is in Paris or Athens—note what happens if we join the two projections together[48]—and so we’ve lost information.

Relvars SNT and CT—sample values

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

Now recall the predicate for relvar S (see the answer to Exercise 2.6 in Appendix D):

Supplier SNO is named SNAME and is located in city CITY, ...

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