Re: Does entity integrity imply entity identity?
Date: Thu, 6 Aug 2009 22:35:55 -0400
Message-ID: <UeqdnS2ShaCRDubXnZ2dnUVZ_u6dnZ2d_at_giganews.com>
"paul c" <toledobythesea_at_oohay.ac> wrote in message
news: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.
There are no two-headed monsters:
(X) "X is an order."
I probably should have included D and E columns that don't allow nulls.
Then there would be no question as to whether an order table would be needed
in addition to the order_e-mail table and the order_snail-mail table.
<snip>
(X,D,E) "X is an order placed on date D by customer E."
(X,A) "A is the e-mail address for order X."
(X,B) "B is the snail-mail address for order X."
(X,C) "C is the delivery phone number for order X."