Re: MERGE as the imperative form of aggregation

From: Brian Selzer <brian_at_selzer-software.com>
Date: Mon, 16 Apr 2007 15:47:44 GMT
Message-ID: <zUMUh.3836$2v1.3356@newssvr14.news.prodigy.net>

"paul c" <toledobythesea_at_oohay.ac> wrote in message news:zhLUh.83055$DE1.35612_at_pd7urf2no...

> Brian Selzer wrote:

>> "paul c" <toledobythesea_at_oohay.ac> wrote in message
>> news:yGCUh.82245$aG1.56433_at_pd7urf3no...
>>
>>>Brian Selzer wrote:
>>>...
>>>
>>>>Update may be "ugly," but it's a necessary and primative operation. It
>>>>is not just a combination of insert and delete. Because a key value can
>>>>only be used to identify a tuple in single database state, update
>>>>provides the means to correlate the tuples in the preceding state with
>>>>those in the succeeding state so that transition constraints can be
>>>>enforced.
>>>
>>>...
>>>
>>>Whenever I've heard people say that update is not a combination of delete
>>>and update, without exception they've failed to describe it using any
>>>kind of fundamental algebra or calculus.
>>
>>
>> That's because you appear to have bought into the erroneous notion that a
>> relational database is a collection of relation variables. Information
>> is lost when transforming an insert or a delete or an update or some
>> combination of them into an assignment--especially when you add multiple
>> assignment into the mix. As a result, you can't determine what is
>> different between successive database states, and consequently, you can't
>> define transition constraints. This thinking is backwards. Assignment
>> is not primative. It is a combination of the primatives delete and
>> insert, since all of the information in a relation is replaced. It
>> should be obvious that no information is lost when transforming an
>> assignment into a delete and an insert. As for update, I refer you to
>> the example I provided later on in the previous post. Update provides
>> correlation between tuples in successive database states. This
>> correlation is lost when transforming an update into a delete and an
>> insert.

>
> That example relation looks like a cheat, being about part locations and 
> trying to do double duty for part movements. Could be mysticism too - 
> every time I see that word "primative" it makes me think of apes, suspect 
> it's used by anthropologists but surely not many of them!
>

Why would you have a separate table for part movements? All that is needed is the current locations for parts. I should probably have stated that I compressed the table definition for brevity. The actual constraint would have restricted movement of parts from one location other than Receiving to another other than Shipping unless the transaction being processed is an inventory transfer (in another table). Because of the limitations of the DBMS, it was not possible to implement it even with triggers, so I was forced to add logic to enforce the constraint to every stored procedure and application that updated the table.

For some reason I was thinking of apes, too. Must have something to do with the people I work with. I even ran a spell check because I thought it looked funny. Who'd a thunk it!

> I say play the ball where it lies and declare the relations one really 
> wants.  I don't see the need for the word "states" either, if one wants a 
> dbms to retain all historical values, so be it, even though the few 
> attempts at that I've seen failed to fly.  Talk of operators that infer 
> previous values of unmentioned relations seems like subterfuge.
>

I'm not talking about a temporal database. There's no need to retain history to enforce transition constraints. All that is needed is what is currently committed to the database and the information submitted by the user when he issues a modification.

> By the way, I don't think assignment is necessary, therefore it's not 
> primitive.  But I have no idea whether it's primative!
>
> p 

Received on Mon Apr 16 2007 - 10:47:44 CDT