Re: Database design, Keys and some other things

The idea behind surrogates is introduced not by Codd, but by Hall, Owlett &
Todd in their '74 paper "Relations and Entities"; Codd just incorporated in
to RM/T later in his 79 paper. Here is the relevant section of his ACM
paper:
http://www.scism.sbu.ac.uk/~rmkemp/codd1979.pdf

Before taking Joe's interpretation of Codd's suggestions, consider Date's
exposition of its inconsistencies at:
http://www.intelligententerprise.com/db_area/archives/1999/990106/online1.jhtml

It is interesting to note that, little importance has been given to notion
of surrogates in formal database studies. Also, standard bodies like ISO,
have never deemed surrogates to be worthy of a formal definition, due to its
negligible distinction from other logical identifiers. But it is often a hot
topic in newsgroups though.

For practical purposes, consider the following from Date's An Introduction
to Database Systems, 7th Ed. ( ISBN 0201385902 ), p. 444 has the
explanation:

[Quote]
Surrogate keys are keys in the usual relational sense but have the following
specific properties:

* They always involve exactly one attribute.

* Their values serve solely as surrogates ( hence the name ) for the
entities they stand for. In other words, such values serve merely to
represent the fact that the corresponding entities exists -- they carry no
additional information or meaning whatsoever.

* When a new entity is inserted into the database, it is given a surrogate
key value that has never been used before and will never be used again, even
if the entity in question is subsequently deleted.

Ideally, surrogate key values would be system-generated, but whether they
are system- or user-generated has nothing to do with the basic idea of
surrogate keys as such.

It is worth emphasizing that surrogates are not ( as some writers seem to
think ) the same thing as "tuple IDs." For one thing-to state the
obvious-tuple IDs identify tuples and surrogates identify entities, and
there is certainly nothing like a one-to-one correspondence between tuples
and entities ( think of tuple IDs for derived tuples in particular ).
Furthermore, tuple IDs have performance connotations, while surrogates do
not; access to a tuple via its tuple ID is usually assumed to be fast (we
are assuming here that tuples-at least, tuples in base relations-map fairly
directly to physical storage, as is in fact the case in most of today's
products). Also, tuple IDs are usually concealed from the user, while
surrogates must not be ( because of The Information Principle ); in other
words, it is not possible to store a tuple ID as an attribute value, while
it certainly is possible to store a surrogate as an attribute value.

In a nutshell: Surrogates are a logical concept; tuple IDs are a physical
concept.
[End Quote]

--
Anith


.