Databases Reference
In-Depth Information
Finally, might there not even be good reasons to have several distinct identifiers, all of equal weight, for the
same entity in the same relvar?
We've already seen several examples in this appendix (in the section “Relvars with More than One Key”) in
which the answer to the last of these questions is clearly yes . In order to examine the other questions, let's consider
another example.
THE APPLICANTS AND EMPLOYEES EXAMPLE
This example (which, like the invoices and shipments example, is based on a real world application) concerns
applicants for jobs in a certain enterprise. Relvar APPLICANT is used to keep a record of such applicants:
APPLICANT { ANO , NAME , ADDR , ... }
PRIMARY KEY { ANO }
The applicant number (ANO) is assigned at the time the applicant applies for the job; it's unique to the applicant,
and {ANO} thus constitutes the obvious primary key (in fact, it's the only key).
Next, several further relvars are used to keep subsidiary applicant information (previous jobs held, list of
references, list of dependants, etc.). I consider just one of these here, the “previous jobs held” relvar
(APPLICANT_JOBS):
APPLICANT_JOBS { ANO , EMPLOYER , JOB , START , END , ... }
PRIMARY KEY { ANO , START }
ALTERNATE KEY { ANO , END }
FOREIGN KEY { ANO } REFERENCES APPLICANT
Observe, incidentally, that once again we seem to be faced with an arbitrary choice of primary key, but that's not the
point I want to examine here.
Now, when a job applicant is successful, he or she is assigned an employee number (ENO, unique to the
employee), and information regarding the new employee─job title, department number, phone number, etc.─is
recorded in an EMP relvar:
EMP { ENO , JOB , DNO , PHONENO , ... }
PRIMARY KEY { ENO }
Now we have two distinct anchor relvars, APPLICANT and EMP, such that the very same entity (i.e., a
successful applicant) is identified by an ANO value in one of the two and by an ENO value in the other. Of course,
it's true that the two relvars represent different roles ─a tuple in APPLICANT represents a person in an applicant
role and the corresponding tuple in EMP (if there is one) represents the same person in an employee role─but the
fact remains that there's just a single entity involved.
The foregoing isn't the end of the story. Clearly, relvar EMP needs to refer back to relvar APPLICANT
somehow (I'm assuming for simplicity, though the assumption might be a little unrealistic, that every employee was
once an applicant). Thus, we need to add an ANO attribute to the EMP relvar and define a foreign key accordingly:
EMP { ENO , ANO , JOB , DNO , PHONENO , ... }
PRIMARY KEY { ENO }
ALTERNATE KEY { ANO }
FOREIGN KEY { ANO } REFERENCES APPLICANT
 
Search WWH ::




Custom Search