Re: A real world example


"Bob Badour" <bbadour@xxxxxxxxxxxxxxxx> wrote in message
news:fqmDg.39802$pu3.533163@xxxxxxxxxxxxxxxxxxxxxxxxxx

[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.
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.
It's easy to prove. 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 answer is obvious: if it
didn't, then the fact that the Social Security Number didn't change
contradicts that. 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, but only within a single relation value. In
order to span multiple database states, that value must be permanent. 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."

[snip]


.