SQL and Relational Theory, 2nd Edition

CONSTRAINTS

So far, then, our suggested overall design for the running example looks like Figure C-7 below.

Figure C-7. Fully decomposing table S

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:

1. Each table has {SNO} as a key.

2. Each row in SN has a matching row in exactly one of ST and SUT, and conversely.

3. 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, ...