Databases Reference
In-Depth Information
Now we have two candidate keys once again!─namely, {ENO} and {ANO}. This point will be relevant in a
few moments; for now, however, I'll ignore it.
Next, of course, we'll need additional relvars to carry subsidiary information for employees (salary history,
benefit details, etc.). Here's the salary history relvar:
SAL_HIST { ENO , DATE , SALARY , ... }
PRIMARY KEY { ENO , DATE }
FOREIGN KEY { ENO } REFERENCES EMP
Now we have the very same entity being not only
identified
, but also
referenced
, by an ENO value in one
relvar (SAL_HIST) and by an ANO value in others (APPLICANT_JOBS, EMP). In other words, the database
structure is as shown in Fig. A.3.
┌────────────────────────────┐
┌──▼──┬─────┐ ┌─────┬──┼──┬─────┐
APPLICANT │ ANO │ ... │ EMP │ ENO │ ANO │ ... │
└══▲══┴─────┘ └══▲══┴─────┴─────┘
│ │
┌──┼──┬─────┐ ┌──┼──┬─────┐
APPLICANT │ ANO │ ... │ SAL_ │ ENO │ ... │
_JOBS └═════┴─────┘ HIST └═════┴─────┘
Fig. A.3: The applicants-and-employees database
Now, we might avoid the apparent need for two different identifiers (ANO and ENO) for the same entity type
by regarding EMP as a
subtype
of APPLICANT; after all, every employee is an applicant (loosely speaking), while
the converse isn't true. In this way we could use {ANO} as the primary key for EMP, treating {ENO} as an
alternate key (or even dropping it altogether), and replace ENO by ANO in the SAL_HIST relvar. The database
structure is now as shown in Fig. A.4:
┌──────────────────────┐
┌──▼──┬─────┐ ┌──┼──┬─────┐
APPLICANT │ ANO │ ... │ EMP │ ANO │ ... │
└══▲══┴─────┘ └══▲══┴─────┘
│ │
┌──┼──┬─────┐ ┌──┼──┬─────┐
APPLICANT │ ANO │ ... │ SAL_ │ ANO │ ... │
_JOBS └═════┴─────┘ HIST └═════┴─────┘
Fig. A.4: Using {ANO} as the primary key for EMP
However, note the implications of this state of affairs: It's not just the database design that's changed, it's
the way the enterprise has to operate. (For a start, it now has to identify employees by applicant number instead of
employee number.) Why should the enterprise change its way of doing business, just because of a piece of
relational dogma (“one primary key per entity type”)? To be specific, why shouldn't it be allowed to identify
applicants by applicant number and employees by employee number─even though applicants and employees are all
persons, and indeed every employee is (or once was) also an applicant?
Aside:
Another possibility would be to introduce a PERSON relvar and then regard both APPLICANT and
EMPLOYEE as subtypes of PERSON. I leave the details as an exercise for the reader; I simply remark that
this approach basically doesn't solve anything, even if we invent a “person number” (PNO) and make
