Re: Negative Numbers in "Identity" or" Autonumber" fields

On 20 Mar, 17:11, "-CELKO-" <jcelko..._at_earthlink.net> wrote:
> >> Whose definition? <<
>
> Dr. E. F. Codd, the inventor of RDBMS.
>
> WHAT IS A SURROGATE KEY?
>
> A quote from Dr. Codd: "..Database users may cause the system to
> generate or delete a surrogate, but they have no control over its
> value, nor is its value ever displayed to them ..."(Dr. Codd in ACM
> TODS, pp 409-410) and Codd, E. (1979), Extending the database
> relational model to capture more meaning. ACM Transactions on
> Database Systems, 4(4). pp. 397-434.
>

My view of this paper gets worse each time I read it - in several ways It almost seems a retro-active step to the original RM :(

> This means that a surrogate ought to act like an index; created by the
> user, managed by the system and NEVER seen by a user. That means
> never used in queries, DRI or anything else that a user does.

Like 1NF, the definition of surrogates has evolved. Nothing in a proposition should ever be hidden from the user. Propositions come from outside of the logical layer after all. If an attribute is an identifier then it clearly impacts on identifying items in the real world.

>
> Codd also wrote the following:
>
> "There are three difficulties in employing user-controlled keys as
> permanent surrogates for entities.
>
> (1) The actual values of user-controlled keys are determined by users
> and must therefore be subject to change by them (e.g. if two companies
> merge, the two employee databases might be combined with the result
> that some or all of the serial numbers might be changed.).
>
> (2) Two relations may have user-controlled keys defined on distinct
> domains (e.g. one uses social security, while the other uses employee
> serial numbers) and yet the entities denoted are the same.
>
> (3) It may be necessary to carry information about an entity either
> before it has been assigned a user-controlled key value or after it
> has ceased to have one (e.g. and applicant for a job and a retiree).
>
> These difficulties have the important consequence that an equi-join on
> common key values may not yield the same result as a join on common
> entities. A solution - proposed in part [4] and more fully in [14] -
> is to introduce entity domains which contain system-assigned
> surrogates.
>
> [emphasis begin] Database users may cause the system to generate or
> delete a surrogate, but they have no control over its value, nor is
> its value ever displayed to them....." (Codd in ACM TODS, pp 409-410)
> [emphasis end].
>
> That means if IDENTITY were a surrogate, we would not see its values,
> print them out as invoice numbers and so forth. That means if
> IDENTITY were a surrogate, we could drop it and the schema would still
> work. Like an index. But IDENTITY fails on both those points.
Received on Tue Mar 20 2007 - 19:31:18 CET