So far, then, our suggested overall design for the running example looks like Figure C-7 below.
I’m assuming here, and will continue to assume for the rest of this appendix, that there’s just one reason why STATUS values might be missing (viz., we don’t know the value) and just two reasons why CITY values might be missing (viz., either we don’t know the value or no such value exists). Note, however, that the design of Figure C-7 requires certain constraints to be satisfied in order to hold it together, so to speak. To be specific, the following constraints need to be stated and enforced:
Each table has {SNO} as a key.
Each row in SN has a matching row in exactly one of ST and SUT, and conversely.
Each row in SN has a matching row in exactly one of SC, SUC, and SNC, and conversely.
Of course, the first of these is just a conventional key constraint on each of the six tables; it can thus be expressed by means of conventional KEY specifications. As for the other two, they can easily be expressed in Tutorial D using D_UNION, as follows:[191]
CONSTRAINT EQD2
SN { SNO } = D_UNION { ST { SNO } , SUT { SNO } } ;
CONSTRAINT EQD3
SN { SNO } = D_UNION { SC { SNO } , SUC { SNO } , SNC { SNO } } ;Aside: Actually it might not be a good idea to use D_UNION in a constraint as I’ve just done. After all, if some update violates the constraint in question, we don’t want a run-time error, we just want the constraint to evaluate to FALSE and the update to be rejected. So constraint EQD2, for example, might better be formulated as follows:
CONSTRAINT EQD2 ARE_DISJOINT { ST { SNO } , SUT { SNO } } AND
SN { SNO } = UNION { ST { SNO } , SUT { SNO } } ;(The operator ARE_DISJOINT is defined to give FALSE if and only if two or more of its argument relations have any tuples in common.) End of aside.
[191] They can be expressed in SQL, too, though not quite so easily (exercise for the reader). In fact, they’re both examples of what are called—for obvious reasons—equality dependencies (EQDs). Note that if an EQD is in effect, and if that EQD spans two or more tables, then certain updates on just one of those tables will necessarily cause that EQD to be violated. See the discussion of multiple assignment and related matters in Chapter 8.