13.1 See Figure 13-1.

13.2 See the body of the chapter.

13.3 Irreducibility of keys and FDs, and the relevance of FD irreducibility to 2NF, are all discussed in Chapter 4; FD irreducibility is discussed further in Chapter 5. Irreducible covers are discussed in Chapter 6. Irreducible JDs are discussed in Chapter 11. Irreducible (i.e., 6NF) relvars and the associated notion of “irreducible facts” are discussed in the body of the present chapter (i.e., Chapter 13).

13.4 See the body of the chapter.

13.5 The main point that occurs to me is that it might be nice to have some kind of “master” relvar whose primary purpose is just to record the part numbers for all parts currently represented in the database. If we call that relvar P, there’ll be EQDs between that relvar P and the projection on {PNO} of each of the relvars PN, PL, PW, and PC (instead of EQDs between, arbitrarily, the projection of PN on {PNO} and the projection on {PNO} of PL, PW, and PC; indeed, one nice thing about having the master relvar is precisely that it avoids that slight arbitrariness).

Moreover, suppose every part always has a known name and weight but doesn’t necessarily have a known color or city. Then we can combine relvars P, PN, and PW, making that combination—which I’ll still call P—the master relvar, and replace the EQDs previously required by foreign key constraints from PL and PC to that master relvar. (A part with no known color will be represented in P but not PL; likewise, a part with no known city will be represented in P but not PC.)

Incidentally, another argument in favor of including that master relvar P has to do with the shipments relvar SP: Given that master relvar, we can retain the conventional foreign key constraint from SP to P; without it, life becomes rather messier.

13.6 Every pair of attributes is a key. The specified JD doesn’t hold, because the following is certainly a legitimate value for the relvar:

     a1  b1  c2
     b1  a1  c2
     a1  b2  c1
     b2  a1  c1
     a2  b1  c1
     b1  a2  c1

(a1 ≠ a2, b1 ≠ b2, c1 ≠ c2); that is, the tuples (a1,b1,c2), (a1,b2,c1), and (a2,b1,c1) most certainly don’t force the tuple (a1,b1,c1) to appear (!). The relvar is in 6NF. Note, however, that it’s subject to a certain symmetry constraint; to be specific, the tuple (a,b,c) appears if and only if the tuple (b,a,c) appears (see the sample value above for an illustration of this point).^[199] As a consequence, the relvar is also subject to certain insertion and deletion anomalies, and it isn’t in DK/NF.

13.7 Let relation r have heading H. Then r will certainly satisfy all possible FDs and JDs that can be defined with respect to H if r has cardinality either one or zero. Thus, all possible sets of dependencies (FDs and JDs) are consistent (though some such sets might have the implication that any relation that satisfies them can have cardinality at most one).

13.8 The following is certainly a legitimate value for relvar SPJ′—

     s1  p1  j1
     s2  p1  j1

(s1 ≠ s2)—so {PNO,JNO} isn’t a key. Likewise, the following is also a legitimate value for SPJ′—

     s1  p1  j1
     s1  p2  j1

(p1 ≠ p2)—so {JNO,SNO} isn’t a key either.

13.9 The thing to do here is to separate matches that have already been played from those that haven’t:

     PAST_MATCHES { DATE , OPPONENT , GOALS_FOR , GOALS_AGAINST , ... }
                  KEY { DATE }

     FUTURE_MATCHES { DATE , OPPONENT , ... }
                    KEY { DATE }

These relvars are both in 5NF. PAST_MATCHES in particular probably shouldn’t be replaced by 6NF projections.

13.10 Yes, we can define it as a view:

     VAR SCP VIRTUAL ( ( JOIN { S , SP , P } ) { SNO , PNO , CITY } ) ;

The following FD holds in this view:

     { SNO , PNO } → { CITY }

(in fact, {SNO,PNO} is a key). The following (nontrivial) MVDs also hold:

     { CITY } →→ { SNO } | { PNO }

Because of these MVDs, relvar SCP isn’t in 4NF, though it is in BCNF. As for “conventional wisdom,” this example gives the lie to another popular misconception: viz., that a relvar consisting of a single key and a single nonkey attribute is necessarily in 6NF, or at least 5NF (see Exercise 1.8).

13.11 For the definition, see the body of the chapter. As for an example, suppose relvar SP is subject to a constraint to the effect that odd numbered parts can be supplied only by odd numbered suppliers and even numbered parts only by even numbered suppliers (the example is very contrived, of course, but it suffices for the purpose at hand). Then this constraint is clearly not implied by the domain and key constraints that hold in relvar SP, and so the relvar isn’t in DK/NF; yet it’s certainly in 6NF.

13.12 There certainly is a difference, since overstrong PJ/NF implies 5NF and 5NF implies SKNF and the reverse implications don’t hold. But it’s easy to confuse the two, because the following superficially similar observations are both true (note the boldface):

13.13 Apologies if you think these definitions a little late in coming:

13.14 Loosely speaking, a disjunction of predicates is the OR of two or more other predicates. If some relvar R had a disjunctive relvar predicate, then each of the individual predicates that are OR’d together would have to have the same parameters (because the tuples that satisfy them would all have to be of the same type). Reducing such a predicate to simple predicates would involve decomposition of the relvar via restriction instead of projection (and recomposition via union instead of join). Indeed, that’s exactly what the final subsection in the body of the chapter was all about. More specifics are given in Part IV of this book.