O'Reilly logo

Database Design and Relational Theory by C.J. Date

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required