Re: more on delete from join
Date: Wed, 26 Aug 2009 01:43:52 -0400
"Walter Mitty" <wamitty_at_verizon.net> wrote in message
> "Mr. Scott" <do_not_reply_at_noone.com> wrote in message
>> Assuming that your interpretation is the right one, trying to delete from
>> a join view would be like trying to delete from a denormalized table.
>> One of the reasons to normalize is to eliminate so-called update
>> anomalies. For example, a table that contains both customer information
>> and order information exhibits a delete anomaly. When the last order for
>> a customer is deleted, that customer's information is deleted.
>> Decomposing the table into a customers table and an orders table with a
>> foreign key from orders to customers eliminates that anomaly. If we
>> accept your interpretation, then trying to delete from a view that joins
>> orders and customers reintroduces the anomaly.
> The delete anomaly for unnormalized tables arises from the fact that
> delete operates on entire rows. If you could delete only part of a row,
> without deleteing
> the other part, it woild be possible to avoid the particular delete
> anomaly you mentioned.
> When the last order pertaining to a customer is deleted, you only delete
> the data pertaining to the order, but you keep the row in the table with
> the customer data intact.
> So how the heck to do you delete part of a row without deleting the entire
> row? Easy. You set the data you are removing to NULL.
> I'm not seriously suggesting this as a practice. I'm just showing how the
> updateable view question, the NULL question, and normalization are
They are related. It has do with predicates.
Pkx A table in 6NF Pkx IFF Qky A table in 5NF but not 6NF or two 6NF tables with mutual foreign keys Pkx IFF Qky IFF Rkz A table in 5NF but not 6NF or three 6NF tables in a referential cycle Pkx AND Qky A join view or a denormalized table Pkx IMPLIES Qky A join view over FK P to Q or a denormalized table Qky IMPLIES Pky A join view over FK Q to P or a denormalized table Pk XOR Qkx A table that permits inapplicable nulls Pk OR Qkx A table that permits I-don't-have-a-clue nulls Pkx OR Qkx A union view or just two tables Pkx XOR Qkx A disjoint union view or two tables with a distributed key
Note that none of these predicates preclude applicable nulls. If every applicable null is in logic a distinct individual variable, then some of the problems with null can disappear. For example, SUM(A) = SUM(A) would be true, as would SUM(A) + SUM(B) = SUM(A+B), because v = v and u = u, so v + u = v + u when u and v are arbitrary but distinct instances of applicable nulls. Received on Wed Aug 26 2009 - 07:43:52 CEST