Re: candidate keys in abstract parent relations

Comments inline

vldm10 wrote:
> David Portas wrote:
>
> > 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
>
> Here we have the following problem: Is it always possible to represent
> the entity's ( or relationship's) instance in a RM? If you know
> for some theory which support these ideas (even if it is a
> philosophical theory) let us know.

I'm not certain what you mean by "represent the entity's (or relationship's) instance in a RM". If the "relationship's instance" is information that can be stored in some type supported by our database then the answer is self-evidently YES. Why do you doubt it?

The question I *think* you are implying is this: Can or should each "real" object be represented by exactly one tuple in some relation? The point of my example is that that question is unimportant. Tuples are just part of the model for representing *facts* about the universe. Given the right data types any set of facts can be so represented.

>
> I don't think that regarding the real world situation "we can
> safely say that relvar must have at least one candidate key" as you
> stated. Regarding some abstract mathematical relation, which doesn't
> represent reality, your sentence is true.

A relation IS an abstract mathematical construction used to represent reality.

>
> Example:
> In the case that the entity's (or relationship's) instance repeats
> the same values or in the case when we have the two entity's
> (relationship's) instances with the equal values and the relation
> which has "natural key", then it is not possible to represent these
> entities' (relationships') instances in the relation. But, if the
> key is "surrogate key", then we can represent mentioned instances
> in the relation of the RM.

I think you are confusing logical and conceptual models. Why should a tuple correspond to a single "entity's instance"? See my earlier example and discussion of the "quantity" attribute. To put it another way, I sometimes use the analogy of an abacus. A relation with only an artificial key that has no external meaning is like an abacus - each tuple is a counting bead that represents an instance of some object or concept - namely the instance or concept identified by the meaningful attributes of that relation. There is nothing wrong with the abacus design in purely relational terms but it is a bad design for at least 2 reasons:
1) It is highly redundant. A single tuple is NOT in fact identifiable with a real instance of a real entity because there is no external key that permits such identification. Which tuple or tuple(s) get updated by the users is essentially an arbitrary choice. Dangerously arbitrary. Anomalies are virtually inevitable - in fact you cannot prove that they don't exist!
2) As a consequence of the redundancy, the abacus model will almost certainly require more physical storage than an alternative model without redundancy.

The model that uses natural keys instead is akin to ditching the abacus and recording only facts. Represent quantity as a NUMBER not as a set of counting beads.

> Do you mean key which is not externally verifiable?

Yes

> As you explained here the externally not verifiable keys haven't some
> great usage. Key should be externally verifiable, but this is not a
> "must" - it is not even mentioned in the definition of the key.

RM doesn't require verifiable keys. Good design does.

> Let me give you one example from the business environment but from a
> different point of view.
> A Honda dealer has received 100 new 2006 Honda civic cars and these
> 100 cars have the list with the 50 same attributes. You should design
> the Database for these entities using "natural key" and without
> using Vehicle Identification Number (VIN) or any other "surrogate
> key" or "artificial key". And you will see that this is
> impossible, because all "fields" have the same corresponding
> values.

Well a VIN is an externally verifiable key and is probably a good candidate key in this case. Even assuming you don't have such a thing the solution in this case is trivial - if all attributes are the same then you need only a single tuple to represent all 100 cars. You will surely have a quantity column in that table to record the number the dealer has in stock.

--
David Portas