Re: Requirements for update languages?

Jens Lechtenbörger wrote:

> while there are some criteria to assess relational query languages
> (adequate, relationally complete, optimizable) I wonder what makes a
> good update language for a data model.

        Basically, it should be a D as in The Third Manifesto: http://www.thethirdmanifesto.com/.

> In particular, I wonder about the following points in SQL.
>
> 1. I believe that SQL data manipulations are not adequate for bags,
> as they lack the ability to manipulate duplicates. (E.g., you can
> neither delete 3 out of 5 duplicates nor insert 3 duplicates at
> once.)

        That's more of a fault in SQL as a data model, which isn't relational at all, than in SQL as a language. Except in so far as the language is inextrincably linked to the data model, so the blame so as to say falls on both.

        But to answer your point, is there such a thing as a proper data manipulation for bags? AFAIK no one has ever devised a proper bag-based data model; rather, SQL got bags by involution from the RM. Perhaps that would be a nice thing; like, if people how complex dealing properly with bags would be, they would pay more attention to being relationally sane.

        OTOH, perhaps a proper treatment of bags would make for a better SQL than SQL even if it didn't get the full relational benefits.

> 2. As a db user, I expect that I can undo (inadvertent) data
> manipulations, e.g., undo an insertion via a deletion or vice versa.
> Does anybody else believe that this is a reasonable requirement?

        That's one of the ideas behind transactions: you can do whatever you want, until you terminate your transaction with a COMMIT.

        OTOH there is this new idea from Date that transactions are an unnecessary complication because we should just have atomic statements that make all the transaction's updates in parallel. This would take COMMIT from us, but than perhaps we should create something like a branch of work particular to an interactive session, even perhaps using a transaction-like sintax:

BEGIN WORK;
[…]
COMMIT;         A nice UI would then keep the user informed in a kinda status bar as to how many system resources he's using in uncommitted work.

        Now for the specific case of inserting back deleted data. I don't think it is reasonable. That because if I say "delete all this user's information", I don't necessarily care for what's that information, asides from the key I used to specify the user. I know it's gone. Now if I wanted to have it back, I'd like to have a nice way of restoring deleted data, like using a backup in the way of HSMs: it would be nice to tell a system "give me whatever was in such and such a tuple at such and such a time". A relational system could be able to fetch that data from backup thru an HSM and give me that data, but that is orthogonal to deleting it.

> 3. As a db admin, I expect that users know what they are doing when
> they manipulate data. Does anybody else believe that this is a
> reasonable requirement?

        Well, that's what we have access control and declared constraints for.

> Again, for view updates this is often not the case. E.g., in case of
> projections users delete tuples they do not know completely; in case
> of deletions from unions they do not even know from which relation
> something gets deleted.

        And why it should matter? Suffices them to know to identify which information they deleted; the system (and therefore the DBA) should know where it was deleted from because he knows the constraints derivation rules. Then the information can be fetched from a backup if necessary.

> It turns out that (2) and (3) above are equivalent. I wrote a paper
> about this issue, where I warn against using view updates. If you
> are interested, take a look at:
> http://dbms.uni-muenster.de/publications/downloads/view-updates.pdf A
> small warning: An almost identical version of that paper got rejected
> at ICDT'03. Basically, the anonymous referees did not buy (2) and
> (3).

        I have to agree with them. While you have a nice point on your points (2) and (3) being equivalent, and your first paragraphs make for interesting reading, I don't agree with the assumptions that SQL is sane, bags are desirable, and reversal thru views are desirable.

-- 
  _
/ \ Leandro Guimarães Faria Corsetti Dutra        +41 (21) 216 15 93
\ / http://homepage.mac.com./leandrod/        fax +41 (21) 216 19 04
  X  http://tutoriald.sourceforge.net./      Orange Communications CH
/ \ Campanha fita ASCII, contra correio HTML      +41 (21) 644 23 01