Re: A real world example

"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message news:s7%Dg.44810$pu3.553247_at_ursa-nb00s0.nbnet.nb.ca...
> anithsen_at_gmail.com wrote:
>
>> "Brian Selzer" <brian_at_selzer-software.com> wrote in message
>> news:MNCDg.8096$9T3.560_at_newssvr25.news.prodigy.net...
>>
>>>"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message
>>>news:fqmDg.39802$pu3.533163_at_ursa-nb00s0.nbnet.nb.ca...
>>>
>>>[snip]
>>>
>>>
>>>>A natural key is simply a familiar surrogate. Nothing more. Nothing
>>>>less.
>>>
>>>I disagree. A the value of a surrogate (at least according to Codd, and
>>>also Date, if I recall correctly) should permanently identify something.
>
> Cite?

I think I did. Did you read Codd's paper? I found the Date reference as well: "An Introduction to Database Systems," Seventh Edition, page 444. Look it up.

>... The value of a candidate key is it permanently identifies something;
>hence, the importance of stability as a design criterion. That is as true
>for natural keys as for surrogates.
>

I think you need to review the definition of a candidate key. According to Date in "Database in Depth," page 63:

<<<<

Definition: Let K be a subset of the heading of relvar R. Then K is a candidate key (or just key for short) for R if and only if it possesses both of the following properties:

Uniqueness

    No possible value for R contains two distinct tuples with the same value for K.

Irreducibility

    No proper subset of K has the uniqueness property.

>>>>

This is slightly different from his definition in "An Introduction to Database Systems," Seventh Edition, page 258:

<<<<

Let K be a set of attributes of relvar R. Then K is a candidate key for R if and only if it possesses both of the following properties:

1. Uniqueness: No legal value of R ever contains two distinct tuples with the same value for K.
2. Irreducibility: No proper subset of K has the uniqueness property.

>>>>

It's clear from both of these definitions that the scope of the uniqueness property of a candidate key is *any* legal value for R, not *every* legal value for R. In other words, the value of a candidate key does *not* permanently identify something.

>
>> The only way one can have an attribute whose values never change is by
>> violating information principle.
>
> How so? I agree that hiding an attribute violates the information
> principle, but I don't see how precluding changes does.
>
>
>>>That has always been my understanding, and that has always been how I've
>>>used the term. Natural keys can change and still refer to the same
>>>thing.
>
> Selzer's understanding is flawed. I suggest he should try harder to use
> correct terms.
>
>
>> Nothing in surrogacy suggests immutability of values.
>
> Hear! Hear!
>
>
>>>It's easy to prove.
>
> If one accepts Selzer's definitions of terms, then by definition nothing
> needs proof. However, his definitions of terms differ greatly from the
> generally accepted definitions, which makes any pretense of communication
> with him pointless.
>

I guess C.J. Date is out of the mainstream, yes?

>
> Consider a relation schema that describes employees
>>>and has two candidate keys, Social Security Number and Badge Number. If
>>>an employee gets a new Badge Number because he lost his badge, does the
>>>new Badge Number refer to the same employee?
>
> The badge number logically identifies a badge and does so immutably. The
> attribute describing the employee merely describes the current assignment
> of a badge to the employee.

No, by the definition of a candidate key, the badge number identifies an employee.

>
> Suppose a criminally minded individual finds the lost badge and uses it to
> gain unauthorized access to the employer's resources. Where does Selzer's
> proposed schema record who the badge is assigned to?
>

If the security system identifies an employee by his badge number, then a lookup by badge number for a lost badge will fail, and access won't be granted. By the way, what has this to do with my argument?

> In the example he gave above, he proposed two arbitrarily assigned numbers
> as candidate keys. What is a surrogate if not an arbitrarily assigned
> number?
>

What does this have to do with my argument?

> The numbers happen to be somewhat familiar because the employee already
> uses the SSN for his taxes, and the badge number presumably appears on a
> physical artifact handed to the employee: ie. the badge. The familiarity
> of the keys makes them natural keys.
>

Again, what has this to do with my argument?

>
> The answer is obvious: if it
>>>didn't, then the fact that the Social Security Number didn't change
>>>contradicts that.
>
> Ironically, the idiot is too stupid to realize that SSN's are just as
> mutable as badge numbers. While one may not legally have two SSN's at the
> same time, transcription errors and identity theft mean SSN's do change
> over time from the perspective of the organization.
>

It appears that you can't counter my argument, and you're trying to deflect attention away from that fact.

>
> The definition of a candidate key guarantees that the
>>>propositions in a single relation value are unique; therefore, a
>>>candidate
>>>key value can identify a tuple
>
> True.
>
>
> , but only within a single relation value.
>
> Bullshit. See foreign key references.
>

What do you mean by that? Are you trying to say that the value of a foreign key identifies a tuple within the relation value that contains the foreign key? How can the values in a referencing relation value guarantee uniqueness in the referenced relation value?

>
>>>In order to span multiple database states, that value must be permanent.
>
> Well, duh. This is why stability is an important design criterion for
> choosing candidate keys. Saying that stability is an important design
> criterion is a wholly unremarkable statement. If that is what the idiot
> was trying to say, why didn't he just say it?
>
>
>>>Codd understood this even if you can't get it through your head: I refer
>>>you to the paper he wrote in 1979, "Extending the Database Relational
>>>Model to Capture More Meaning."
>>
>> Perhaps you may want to read through the contradictions in Codd's RM/T
>> paper.
>> http://www.intelligententerprise.com/db_area/archives/1999/990106/online1.jhtml
>
> Indeed. Thank you.
Received on Mon Aug 14 2006 - 19:53:50 CEST