Re: A real world example


<anithsen@xxxxxxxxx> wrote in message
news:1155560420.746300.309520@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
"Brian Selzer" <brian@xxxxxxxxxxxxxxxxxxx> wrote in message
news:MNCDg.8096$9T3.560@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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

The only way one can have an attribute whose values never change is by
violating information principle.

How so? In what way does the information principle require that attributes
be mutable? A relational database contains knowledge about things, not
things. Now, if you're going to talk about something, then that thing must
either exist or have existed in the universe. If a thing exists in the
universe and is relevant to the discussion, then there must be some way to
distinguish that thing from all other things that exist, that have existed
and that can exist, otherwise there isn't any way to be sure that you're
talking about the same thing in successive database instances.

The RM is value-based: a database schema determines the possible values that
that database can take. For the database to change, two values, or
instances, must exist and be consistent prior to the change, that is, the
preceding instance and the succeeding instance. Thus, a series of changes
to a database results in a succession of database instances. It is often
necessary to know what was known about something in order to assert
something new. For example, when a credit card charge clears, the bank must
know the balance of the account in order to compute the new balance. More
importantly, the bank must be able to identify the account that is about to
change, and that identity must remain constant in both the preceding and
succeeding database instances. Because changes are set-based, without some
means to correlate the propositions in the preceding instance with those in
the succeeding instance, you cannot be certain that you're talking about the
same thing; therefore, you cannot be certain if the succeeding instance is
correct. The question is: should the solution to this problem be handled in
implementations, or should the theory be strengthened to eliminate it?


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.

Nothing in surrogacy suggests immutability of values.

I believe Codd used the term "permanent" to describe surrogates. That
implies immutability. He did mention drastic circumstances, such as merging
databases that could require that they change, but the impression I got was
that they should be permanent.


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

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


Been there. I understand the ramifications of hiding attributes. I've
argued before on this forum that surrogates shouldn't be hidden by the
database, but rather from end users by the applications that need them, or
by the DBA if the DBMS provides a means to provide views without them.
Applications need to see them because then they can be sure that the updates
they're making are correct in a concurrent environment. End users, on the
other hand, generally have no need to see them, so as a best practice, they
should be hidden *by the application* from them.

--
Anith

[snip]






.

Relevant Pages

  • Re: Surrogate Keys: an Implementation Issue
    ... a database that must provide a complete audit trail of every change. ... There is always a compelling reason to use surrogates: ... For example, Bob is preparing to update a row--that is, he has ... a logical identifier. ...
    (comp.databases.theory)
  • Re: Surrogate Keys: an Implementation Issue
    ... a database that must provide a complete audit trail of every change. ... There is always a compelling reason to use surrogates: ... For example, Bob is preparing to update a row--that is, he has ... a logical identifier. ...
    (comp.databases.theory)
  • Re: Surrogate Keys: an Implementation Issue
    ... a database that must provide a complete audit trail of every change. ... Unless the key is immutable, when Bob issues the update, there's no guarantee that the row he's updating is the same one that was read out. ... Bob issues an update statement to change some attribute identified by a logical identifier. ... Surrogates ain't gonna fix that. ...
    (comp.databases.theory)
  • Re: Surrogate Keys: an Implementation Issue
    ... a database that must provide a complete audit trail of every change. ... Natural keys are often mutable--especially compound keys. ... For myself I view surrogates as being useful in two situations, ... of integrity, given they are, at least initially, under the control of ...
    (comp.databases.theory)
  • Re: Guidelines to a decent support of surrogate key implementation
    ... totally unrelated to the logical data model. ... A surrogate key is system generated to replace the actual key ... with a quote from Dr. Codd: ... Extending the database relational model to capture more meaning. ...
    (comp.databases.theory)