My next example is an extremely simple version of the familiar employees-and-programmers example, in which all programmers are employees but some employees aren’t programmers (as in Exercise 5.7). Note that some people would say that employees and programmers in this example correspond to an entity supertype and an entity subtype, respectively. Be that as it may, here’s the conventional design:
EMP { ENO }
KEY { ENO }
PGMR { ENO , LANG }
KEY { ENO }I’m assuming for the sake of simplicity that nonprogrammers have no attributes of interest apart from ENO (if they do, it makes no significant difference to the example), and programmers have just one additional attribute, LANG (programming language skill—e.g., “Java” or “SQL” or “Tutorial D”). Now we have a choice: Record all employees in EMP, or record just the nonprogrammers in EMP. Which is better?
If we record just the nonprogrammers in EMP, the processing involved when an employee becomes or ceases to be a programmer is slightly nontrivial—in both cases we have to delete a tuple from one relvar and insert a tuple into the other. We also need to state and enforce the following constraint:
CONSTRAINT ... IS_EMPTY ( EMP JOIN PGMR ) ;
Also note the implications if we want some other relvar to include a reference to employees. Normally that reference would be a simple foreign key; but if employees are split across two relvars, EMP and PGMR, it can’t be (at least, not as foreign keys are conventionally understood). The net of such considerations is that this particular design is probably not recommended.
But on the other hand, if we record all employees in EMP, we have some redundancy in our design: If e is a programmer, e is certainly an employee, so why say so explicitly?