We Need More Science - Database Design and Relational Theory - page 186

CONSTRAINT EVERY_EMP_HAS_A_DEPT EMP { ENO } = EMPDEPT { ENO } ;
CONSTRAINT EVERY_DEPT_HAS_AN_EMP DEPT { DNO } = EMPDEPT { DNO } ;
There seem to me to be at least two factors that militate against adopting such a “default department” design,
however. The first is that the choice of which department to make the default is likely to be arbitrary. The second is
that now we need to be extremely careful over the meaning of relvar EMPDEPT! The obvious predicate Employee
ENO is in department DNO doesn't work. Why not? Because, under that predicate (and assuming department D3 is
the default), omitting the tuple (E5,D3), say, would mean─thanks to The Closed World Assumption ─that employee
E5 isn't in department D3! So the predicate has to be something like this:
Employee ENO is in department DNO (which is not the default department number D3).
Now, this predicate does work (I think!), but it's pretty tricky. Suppose the tuple (E1,D1) appears in the
relvar, as shown in Fig. 15.5. Then the corresponding proposition is:
Employee E1 is in department D1 (which is not the default department number D3).
And of course this proposition evaluates to TRUE. OK so far. However, now suppose there's no tuple in the relvar
for employee E5. The intended interpretation is, of course, that employee E5 is in department D3; but what does
The Closed World Assumption actually say? Well, first of all, observe that, e.g., the specific tuple (E5,D1) doesn't
appear. By The Closed World Assumption , then, the following must be a true proposition:
It's not the case that employee E5 is in department D1 (which is not the default department number D3).
Or a little more formally:
NOT ( E5 is in D1 AND D1 ≠ D3 )
By De Morgan's laws, this expression is equivalent to:
E5 is not in D1 OR D1 = D3
Since D1 = D3 is false, this expression reduces to just “E5 is not in D1,” which is what we want (I mean, it's a true
proposition).
A similar analysis shows that we can infer that E5 certainly isn't in any department that's not the default one,
D3. But what about that default one? Well, the tuple (E5,D3) doesn't appear, and so the following must be a true
proposition:
NOT ( E5 is in D3 AND D3 ≠ D3 )
Equivalently:
E5 is not in D3 OR D3 = D3
Since D3 = D3 is true, this expression reduces to just TRUE. Note, however, that this proposition doesn't actually
tell us E5 is in D3! Now, perhaps we can infer this latter fact, given that E5 does exist and certainly isn't in any