Re: more on delete from join

From: Mr. Scott <>
Date: Wed, 26 Aug 2009 01:43:52 -0400
Message-ID: <>

"Walter Mitty" <> wrote in message news:QhRkm.745$
> "Mr. Scott" <> 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
> interconnected.

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 
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

Original text of this message