Re: candidate keys in abstract parent relations

Forrest L Norvell wrote:

> In this case, I guess I'm being finicky because if I use a generated
> key column, it's *not* going to be a truly surrogate key: a surrogate
> key implies that there is a candidate key for which the new key is
> acting as a surrogate, and as defined, my album table lacks any
> candidate keys. It's entirely possible that duplicate rows will be
> added to the table without some kind of nonce or generated identifier
> being added to its definition, so the entire justification for adding a
> generated column is to guarantee uniqueness over the relation.
> According to my (definitely shaky) grasp of the relational model, this
> is a bad idea, and I'm afraid doing things this way exposes a weakness
> in the underlying data model, even if I can't identify it.

Yes it's a bad idea. Here's a generic example. Consider two tuples:

a tuple = {'A','B','C'}
a tuple = {'A','B','C'}

They are the same. It serves no purpose to represent the same
information twice, which is why we can safely say that a relvar must
have at least one candidate key - the generality of the model isn't
compromised by prohibiting redundancy. Now let's add an arbitrary
artificial key on the understanding that this key has no external
meaning whatever:

a tuple = {1,'A','B','C'}
another tuple = {2,'A','B','C'}

Isn't this still just as redundant as in our first case? The artificial
key isn't supplying any extra information in either tuple. In fact the
artificial key usually isn't exposed to business users AT ALL. A
relation containg these two tuples won't violate any principle of RM
but it would cause us a major problem - the redundancy means that we
have the potential for anomalies to arise and so we may get incorrect
results from our database.

Possibly {'A','B','C'} represents some entity that we are interested in
counting - we want to know how many there are in our universe - but we
don't need to add an artificial key to do that. We can just add another
attribute ("quantity") and then a single tuple would be suffficient to
represent any quantity of whatever we are modelling.

So conceptually nothing is gained by adding the artificial key and
nothing is lost by removing it. An artificial surrogate key may have
certain practical advantages but an artificial key is redundant in the
logical model by definition.

The candidate key is defined by a subset of the attributes, so the real
question is: what attributes do you need to model about an entity? If
you aren't sure what the key is then you probably haven't answered that
question properly.

Now let's reconsider your example. You define an album by the tracks
that it contains. There may be more than one album with the same name
and in fact with all other attributes in common and those albums will
be distinguishable only by the fact that they represent a different
collection of tracks. Your mistake though is to identify a one-to-one
correspondence between rows in your Album table (information about an
album) and the album itself (a collection of tracks). Those are two
different things. The missing piece in your model is a table or
attribute(s) to supply the information that relates the two entities. I
would have expected to see a "joining" table to represent the concept
of a many-to-many relationship between the Album table and the Tracks
table.

--
David Portas

.

Relevant Pages

  • Re: candidate keys in abstract parent relations
    ... which has "natural key", then it is not possible to represent these ... If we can connect our database objects with reality on some other way, ... > artificial key usually isn't exposed to business users AT ALL. ... There may be more than one album with the same name ...
    (comp.databases.theory)
  • Re: candidate keys in abstract parent relations
    ... But don't forget there is another phase after the logical model and thats ... I get a real bee in my bonet over the surrogate key issue, ... > artificial key usually isn't exposed to business users AT ALL. ... You define an album by the tracks ...
    (comp.databases.theory)
  • Re: candidate keys in abstract parent relations
    ... an artificial key fails to improve a relation without a natural key. ... Does it make sense to think about an Album as some sort ... the difficulty I'm having in coming up with a candidate key ... becomes Album(primary performer, name), and I create a new relation ...
    (comp.databases.theory)