Re: Does entity integrity imply entity identity?

From: paul c <toledobythesea_at_oohay.ac>
Date: Fri, 07 Aug 2009 00:12:39 GMT
Message-ID: <XjKem.39857$PH1.636_at_edtnps82>


Mr. Scott wrote:
...
> I mentioned Darwen's approach and the problems associated with it in an
> earlier post. This example illustrates those problems. If the table is
> decomposed into tables (X), (X,A), (X,B) and (X,C), then multiple assignment
> is needed in order to meet the requirement that whenever there is an X there
> must be either an A or a B or both. In addition to the referential
> constraints from (X,A), (X,B) and (X,C) to (X), there is also a need for a
> referential constraint from (X) to the union of the projections over X on
> (X,A) and on (X,B). As far as I know, multiple assignment is not supported
> in any commercially available DBMS, and I could be wrong but I don't think
> it is possible to reference a view in a foreign key constraint, so from a
> practical standpoint Darwen's decomposition solution is clearly not viable
> at present. From a theoretical standpoint, the example shows that the
> decomposition alternative introduces 'referential cycles' which I think
> should be avoided. (By 'referential cycles' I mean that whenever there is a
> row in (X), there must also be a row in either (X,A), (X,B) or both with the
> same value for X, and whenever there is a row in (X,A), (X,B) or both with a
> value for X, there must also be a row in (X) with the same value.)
>
> <snip>
>
>

  1. I think the predicate for {X} is mixed up. The above argument seems be using it for two different purposes at the same time, eg., "X is a customer" and "X is a customer with an order". The predicate here is a two-headed monster. I would rather avoid two-headed monsters before I worried about avoiding multiple assignment.
  2. I don't buy the argument that nulls are okay because SQL products don't support multiple assignment.
  3. If the predicate of {X} is "X is a customer with an order", then {X} isn't needed, only the three relations {X,A}, {X,B}, {X,C} are needed and the constraint to enforce one of A and B having known values is logically satisfied by a constraint on {X,C}. This doesn't depend on multiple assignment.
  4. If the predicate of {X} is "X is a customere (regardless of order)" then the three necessary relations above could have foreign keys referencing {X}. Still no multiple assignment needed.

(The reason I don't like nulls is that sometimes they actually are values, eg., "false", and sometimes they aren't (don't know enough SQL to say exactly when they aren't, could have to do at least with aggregate ops. Anyway it seems to use them in tables I must remember various arbitrary implementation rules which is my main objection. As Codd said early on, some tables can't be expressed in a single relation.) Received on Fri Aug 07 2009 - 02:12:39 CEST

Original text of this message