Re: Requirements for update languages?

Jens Lechtenbörger wrote:
>hidders_at_hcoss.uia.ac.be (Jan Hidders) writes:
>
>> In article <m2k7jo87eh.fsf_at_pcwi1068.uni-muenster.de>,
>> Jens Lechtenbörger <lechtej_at_uni-muenster.de> wrote:
>> >
>> >1. I believe that SQL data manipulations are not adequate for bags,
>> > as they lack the ability to manipulate duplicates. [...]
>>
>> Although in views bags can sometimes convey some meaningful information it
>> is probably a better idea to stick to the relational model [...]
>
>As I said in my reply to Paul Vernon: I don't believe in bags, but
>SQL has bag semantics.
>
>And I might buy two boxes of beer, which---you must agree---is
>better that just one.

That's one of the best arguments for bags I've heard in a long time. :-) Anyway, the bags are probably not the main issue, so even though I believe it is not unimportant I will let that rest for now.

>> >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?
>>
>> Not always. I would consider it a consequence of the fact that you are
>> looking at a view and not at the original base tables. Compare it for
>> example to the case where you have two base tables R1(A,B,C) and R2(A,B)
>> which are linked by the consraint that R2 = R1[A,B].
>
>*grin* Such constraints do not exist.
>
>You don't store the contents of R2 redundantly but define a view.

What is stored or not is in some sense an implementation issue. At the logical level there should not be a difference between the situation where R1 and R2 are base tables with the mentioned constraint, or the situation where R2 is a view defined on R1. The update problems are in both cases the same.

>> If you tell the database that R2(a,b) no longer holds than it can infer
>> that certain other tuples in R1 also no longer hold, and ultimately the
>> database will be consistent again. But if you add the fact that R2(c,d)
>> then the database is not consistent after you add all the derived
>> consequences.
>
>This is a very nice point. In fact, I thought about "on cascade
>delete" foreign keys. They might break my assumptions but for now I
>believe that you should not be allowed to delete from R2 if you are
>not allowed to delete from R1.

Ok. So let's look at this more closely. Let's say we have tables EMP(Emp#, Dept#) and DEPT(Dept#) with a foreign key from EMP.Dept# to DEPT.Dept#. If I now only have DEPT in my view then if I delete a department I also delete all its employees. However, if I then again add the department (because I made a mistake the first time) then the employees are still gone. You would probably claim that this is not a good view because the user can delete employees but not put them back. I would argue that it is not the user who has deleted the employee, but the database. Compare this to the situation where there is no foreign key, but another user who has gotten the task of firing all employees that do not belong to an existing department. Also then it could happen that you delete a department and after a while you put it back and then you are left with a department without employees. I don't think there is really such a big difference between the database computing some extra updates after you have committed and another user that bases some of his or her updates on the data you committed.

>BTW, congratulations to your ICDT paper,

Thank you.

• Jan Hidders