1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |

11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 |

21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 |

31 | 32 | 33 |

(b) is not right because *A->B* follows from the given FD; in
fact, the given FD is really a shorthand for this FD and the FD
*A->C*.
Since an FD is an MVD, we see that *A->->B* holds.

(c) is not right because *A->C* is a given FD, this FD implies
MVD *A->->C*, and by the complementation rule,
*A->->BD*.

In (a), *D* is the only key, so *B->C* is both a 3NF and
BCNF violation.

In (b), *AB* is the only key, so both FD's are 3NF and BCNF
violations.

In (d), *AD* is the only key, so *B->C* violates both
normal forms.

To check dependency preservation, note that *A^+ = ABCD*, thus,
when we project dependencies onto *AB*, we get *A->B*.
The projection onto *BCD* surely gives us the second and third of
the given FD's: *B->D* and *D->BC*.
We claim that the three dependencies
*A->D*, *B->D*, and *D->BC* are equivalent to the
three dependencies that we can preserve in the projection:
*A->B*, *B->D*, and *D->BC*.
The last two in each set are the same.
Also, *A->B* is easily seen to follow from the first three, and
*A->D* follows from the last three.
If we can preserve an equivalent set of FD's in the projections, then
surely we can preserve the given set.

Incidentally, although it is irrelevant, the Oracle system accepts (c),
and it objects only to `ISNULL` in (a), allowing us to put
`NULL` in the `SELECT` clause.

You might imagine that several of the other MVD's must hold as well. However, if you believe that, you are probably making the tacit assumption that players can only play for one team, and coaches can only coach for one team. While possibly true in practice (if you ignore things like Deion Sanders playing for both the Falcons and the Braves at one point in his career), nothing of the sort was stipulated in the problem statement. If you add to the relation mentioned in the solution to Question 23 another team 6 with player 2 and coach 4 (only), then the resulting relation violates all three other MVD's among the choices.

R1(c,d) = R(a,b) /* A renamed R */ S1(a,b) = PI_{a,b}(SIGMA_{bd}(R TIMES R1)) /* Nonminimum b's */ S3(a,b) = R - S1 /* a with its maximum b */ S4(a,c) = R - S2 /* a with its minimum b, renamed c */ Answer = S3 NATURAL JOIN S4

(b) is not enforced. Remember that attribute-based checks cannot enforce a foreign-key constraint, such as (b), because they are not triggered on a deletion. Yet a deletion can cause a foreign-key violation.

(c) is not enforced. The assertion only requires that stores in California have sales of at least a million.

(a) makes no sense, since *R* doesn't even have a `class`
attribute.
(b) is wrong, because the natural join is a product when the relations
have no attributes in common.
(d) is wrong because it takes the entire projection of `theView`,
with the `f>10` test applying only to *S*.

Then, the body of the PL/SQL statement empties *S*.
Next, it looks at each of the cursor pairs and places into *S*
those pairs where the first component is less than the second.
Datalog query (a) does exactly the same thing.
The two *R* subgoals are joined by requiring the same value
*z* in the second components, and the comparison subgoal requires
that the first component in the head be less than the second.

(b) doesn't work because the join is on first components. The body of (c) looks right, but we take the wrong components for the head. (d) fails to enforce the inequality.

(b) and (d) use `THE` to apply to the relation of a subquery, rather
than to a nested table (there is none in this problem).
(c) is an incorrect version of (a), where we neglect to create the
necessary tuple variable.