Databases Reference
In-Depth Information
Although I didn't say as much at the time, this quote effectively constitutes an appeal to
The Principle of
Cautious Design
, a principle I do still strongly believe in.
4
Indeed, it seems to me the very fact that I'm able to shift
my position on the PK:AK distinction now─which is indeed what I'm doing─can be regarded as a vindication of
that principle.
Before closing this section, I remark that Codd himself is also on record, in the same paper where he said
there was no formal basis for choosing the primary key, as a defender of the PK:AK distinction (not surprisingly,
since he originated it):
Severe problems would arise ... if any relvar whatsoever were permitted to have more than one primary key [
sic
] ... The
consequences of permitting more than one primary key ... for a single base relvar [would be] disastrous.
(I've taken the liberty of replacing the term
relation
by the term
relvar
twice in this extract.) And he goes on to give
an example involving employees with “several distinct responsibilities”─project management, department
management, inventory management, etc.─and then he says:
Comparing for equality of identifiers ... is intended to establish that one and the same employee is involved ... This
objective is dealt a severe blow if the types of identifiers used for employees can be different depending on which pair of
employee-identifying [attributes] is selected for the comparison.
Well, I think you can see this argument is essentially the same as the one given under points 2 and 3 above,
which (a) as I've already indicated, is slightly confused, and (b) as we'll see later in this appendix, doesn't fully
stand up under close scrutiny anyway.
RELVARS WITH MORE THAN ONE KEY
Now let's consider some reasonably realistic examples of relvars with more than one key. The first concerns a
relvar EXAM, with attributes S (student), J (subject), and P (position), and predicate
Student S was examined in
subject J and achieved position P in the class list
. For the sake of the example, let's assume there are no ties (that is,
no two students obtained the same position in the same subject). Then, clearly, given a student and subject, there's
exactly one corresponding position; equally, given a subject and position, there's exactly one corresponding student.
So the FDs {S,J} → {P} and {J,P} → {S} both hold, and {S,J} and {J,P} are both keys (or candidate keys, if you
prefer):
EXAM { S , J , P }
KEY { S , J }
KEY { J , P }
Here's another example (it's basically Exercise 13.6 from Chapter 13): We're given a relvar representing
marriages, with attributes A, B, and C and predicate
Person A married person B on date C
. Assuming no polygamy,
and assuming also that no two persons marry each other more than once, every pair of attributes here is a key:
MARRIAGE { A , B , C }
KEY { A , B }
KEY { B , C }
KEY { C , A }
4
The Principle of Cautious Design
says: Given a design choice between options
A
and
B
, where
A
is upward compatible with
B
and the full
implications of
B
aren't yet known, the cautious decision is to go with
A
.
