what are keys and surrogates?

JOG wrote:

>Let's see what people make of the following definitions. I'm going to
>distinguish the use of the term 'key' as used in different layers as:
>
>1) A 'Key' is a set of attributes, used at the logical layer to
>uniquely identify a proposition.
>2) An 'Identifier' is a set of attributes, used at the conceptual
>layer to uniquely identify an entity.

Why use two different terms?

The definition I was taught is different: a key is a constraint on a relation, namely a set of attributes such that any two different tuples in the relation differ in at least one of these attributes.

This is quite different from your notion, which says something about the relationship between the keys in your model and something outside of your model. (Of course there is a connection; but it's not the same.)

>* A 'Surrogate Identifier' is an attribute designated at design time
>to stand in place of natural identifiers that are either: (i)
>Impractical to observe, (ii) Impractical to manipulate.

My try: an "artificial" identifier is an relation attribute that is a key, and on which equality is the only meaningful operation. (I.e. it only serves to cross-identify the tuples of the relation with something else, e.g. tuples in another relation, or something outside the database). A "surrogate" identifier is a *hidden* artificial identifier, i.e. even the database queries only use them in equalities. (So unless we have specific value domains to safeguard this, this is  a property of the queries we use rather than the attribute itself.)

>* Once a 'Surrogate Identifier' has been created it merely becomes an
>unfamiliar 'Natural Identifier' (it too must become an observable
>attribute of the entity it applies to).

This is not true: they can serve as foreign keys without ever being published (if your queries are careful enouygh). I'd like to use a separate term for arbitrary but published identifiers.

>* A 'Surrogate key' is merely a key that contains an attribute that
>was a surrogate identifier at the conceptual layer.

For me there is no such thing as a surrogate identifier at the conceptual layer. A surrogate is a "1NF" counterpart of an arrow in a conceptual model.

>Any comments?

-- 
Reinier