12.1 Give (a) an example of a relvar of degree at least three that’s in BCNF but not 4NF and (b) an example of a binary relvar that’s in BCNF but not 4NF.
12.2 Write Tutorial D CONSTRAINT statements to express (a) the MVDs that hold in relvar CTX and (b) the embedded MVDs that hold in relvar CTXD, where relvars CTX and CTXD are as in the body of the chapter.
12.3 Consider relvar CTX from the body of the chapter. What kinds of update anomalies can occur with that relvar?
12.4 Give an example of a relvar that’s in 4NF but not 5NF.
12.5 Prove that, given some relvar R and a projection of R whose heading includes both X and Y, the FD X → Y holds in that projection if and only if it holds in R itself.
12.6 Show that if relvar R is subject to the FD X → Y, it’s also subject to the MVD X →→ Y.
12.7 Let X →→ Y be an MVD, M say, with respect to heading H. Prove that M is trivial if and only if either (a) Y is a subset of X or (b) the union of X and Y is equal to H. Incidentally, note that it follows from this result that, given the pair of MVDs X →→ Y | Z (defined with respect to heading H, where H is equal to the union of X, Y, and Z), then X →→ Y is trivial if and only if X →→ Z is trivial.
12.8 The following rule of thumb is often adopted in practice:
Let relvar R have heading H and let the heading H of R be partitioned into disjoint subsets X, Y, and Z. Further, let X be the sole key and let Y and Z both be relation valued. Then, using Heath notation once again, R should be replaced by R1 and R2, where R1 = (R{XY}) UNGROUP (Y) and R2 = (R{XZ}) UNGROUP (Z), respectively. Note: UNGROUP is an operator of Tutorial D. I used it in the answer to Exercise 4.14 in Appendix D. It’s discussed in detail in SQL and Relational Theory and elsewhere.
How does this rule of thumb relate to the topics discussed in the present chapter?
12.9 (Modified version of Exercise 9.3.) Design a database for the following. The entities to be represented are sales representatives, sales areas, and products. Each representative is responsible for sales in one or more areas; each area has one or more responsible representatives. Each representative is responsible for sales of one or more products, and each product has one or more responsible representatives. Each product is sold in each area; however, no two representatives sell the same product in the same area. Each representative sells the same set of products in each area for which that representative is responsible.
12.10 The following dependencies are defined with respect to a heading consisting of attributes ABCD:
B→DA→→B|C
Use the chase to show these dependencies imply the MVDs A →→ C | D. Note: I’m making use of a certain shorthand notation here, according to which A →→ B | C and A →→ C | D denote, respectively, A →→ B | CD and A →→ C | DB. See the answer to the exercise in Appendix D for further explanation.