Databases Reference
In-Depth Information
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?
EXAMPLE 5
Now I'd like to extend Example 4 slightly in order to make an additional point. Suppose relvar EMP does include at
least one additional attribute, JOB; suppose further that a given employee is a programmer, and is represented in
relvar PGMR, if and only if the JOB value in that employee's tuple in EMP has the value “Programmer” (perhaps
other values of JOB─“Janitor”, for example─correspond to other relvars). This kind of situation is not at all
uncommon in practice, by the way. Now there's definitely some redundancy, because the design is subject to the
following equality dependency (as well as many similar ones, possibly):
CONSTRAINT ...
( EMP WHERE JOB = 'Programmer' ) { ENO } = PGMR { ENO } ;
Note, however, that there's no violation of orthogonality in this example, even if all employees, programmers
included, are represented in EMP. Suppose they are; then it's clearly the case that the projection of PGMR on
{ENO} is equal to a certain subset─it's not a restriction as such─of the projection of EMP on {ENO}. (
Exercise:
Why isn't it a restriction as such?) But neither of those projections corresponds to a component of any irreducible
JD that holds in the pertinent relvar.
10
(Check the final version of
The Principle of Orthogonal Design
in Chapter 14
if you need to refresh your memory on this point.) Thus, a database can be fully orthogonal and yet still exhibit
some redundancy.
10
In fact relvars EMP and PGMR are both in 6NF, and the only irreducible JDs that hold are trivial ones.
