Re: A pk is *both* a physical and a logical object.

"DBMS_Plumber" <paul_geoffrey_brown_at_yahoo.com> wrote in message news:1184480376.333327.38370_at_i13g2000prf.googlegroups.com...
> On Jul 13, 5:00 am, "David Cressey" <cresse..._at_verizon.net> wrote:
>> By bringing the "SQL school of data management" into the discussion, I
>> did
>> not intend to "appropriate" the concept of unique identifier (key, or
>> candidate key) nor even the concept of "primary key". I specifically did
>> NOT intend the inference that in order to advocate the selection and use
>> of
>> a primary key, one had to be of the SQL school of data management.
>>
>> The only reason I brought SQL oriented people into the discussion was to
>> describe a specific reason for naming one of the keys to be primary, a
>> reason that may or may not be grounded in relational theory. It's my
>> perception that the concept of primary key is not in fact grounded in
>> relational theory, and that relational theory gets along just as well
>> without it. I wanted either confirmation or refutation of that
>> perception
>> from this group.
>
> As a footnote, Quel had/has a primary key / foreign key concept. As
> did the data language GEM.
>
> It wasn't just the SQL community who thought picking a single key was
> a practical and useful idea.
>
> And Jan's absolutely correct. There is no theoretical justification
> for the idea of a primary key as distinct from a simple candidate key
> at all.
>

I disagree. There is indeed justification. Here's an example:

Given a database D with:

R{A, B, C} where {A} and {B} are both candidate keys,
S{A, D} where {D} is a candidate key and S[A] in R[A],
T{B, E} where {E} is a candidate key and T[B] in R[B],

The expression S JOIN T returns a cross product even though A functionally determines B and B functionally determines A.

In the logically equivalent database D' having:

R{A, B, C} where {A} and {B} are both candidate keys,
S{A, D} where {D} is a candidate key and S[A] in R[A],
T{A, E} where {E} is a candidate key and T[A] in R[A],

The expression S JOIN T clearly does not return a cross product.

The relations T in D and T in D' represent the exact same information in both D and D' in the presence of the inclusion dependencies T[B] in R[B] in D and T[A] in R[A] in D'. Since S in D and S in D' are identical, S JOIN T in D should represent the exact same information as S JOIN T in D'. This is clearly not the case.

In addition, the definition of a candidate key does not demand that its values rigidly designate individuals in the universe of discourse. That's why it is sometimes necessary to introduce an additional key to a relation schema whose values do rigidly designate individuals so that some transition constraints can be enforced. Since transition constraints need not be limited to single relations, distinguishing the one candidate key whose values do rigidly designate individuals is theoretically justified. Received on Mon Jul 16 2007 - 00:01:33 CEST