Re: On view updating

On 21 Sep 2004 03:19:00 -0700, "Tony Andrews" <andrewst_at_onetel.com> wrote:

>Perhaps. Do you know where I could read his latest rules?

I don't know, but you migth try to search in the US patents database.

>> var r relation { n char, a integer } key { n };
>> var v view summarize r per r{n} add Sum(a) as s;
>>
>> The view "v" is clearly updateable (and I am not crazy).
>
>Yes, but I doubt your claim that "most views are updateable". This
>example is a very contrived view that no one would sensibly want to
>create in reality. Most aggregate views would actually be summing more
>than one row per group, surely?

My view sums more than one row per group, but you still can delete groups and to change group names, and these are updates.

I dare you to find a "sensible to create in reality" non updateable view.

>According to D/McG join views are updateable, and deletes are processed
>by deleting from both base tables. But then 9 times out of 10 that
>DELETE will violate either the Assignment Principle or other
>constraints and will fail - when it is intuitively clear that the user
>intends to delete from just one of the base tables. For example, if
>there is a view called LONDON_SHIPMENTS that selects shipments for
>suppliers based in London, it is fairly clear (intuitively) that a
>DELETE from that view is intended to delete shipments for suppliers
>based in London. It is NOT intended to delete all the London-based
>suppliers as well, or even the city of London itself!

Agreed. My point is that the situation is a lot better if we use the "uniqueness criteria". In most cases the DBMS would "know" that there is only one translation that does not violate any constraint .

Although we would have a problem if the supplier is not referenced in any other tuple. In that case the DBMS would have 3 possible solutions.

>> > 1) the transformation made by a view is often one way: you can't be
>> > sure what is in the base tables to achieve the result you see from
>the
>> > view.
>>
>> With a good database design this is not a problem.
>
>Please explain. Are you saying that views that perform a one-way
>transformation of base data are necessarily BAD views? For (simple)
>example:
>
>create view v_emp as
>select empno, name, salary+commission as total_compensation
>from employee;

I missunderstood you. I thought you were talking about non reversible updates. For instance if you delete a view from the v_emp view you can not insert it again.

If you don't like that you only have to revoke the delete privileges on that view.

>There's no way to derive a salary or commission value from the view, so
>does that make it a bad view?

Of course not, but the "uniqueness criteria" will not allow you to insert tuples, or to update the total_compensation attribute.

I would call that kind of views: "views with information lose".

But of course it is still possible to delete tuples and to update the names. So the view is updateable.

>Do you mean that Date & McGoveran's rules are using logic without
>logic?

I mean that they are trying to solve logical problem without using the logic. In other words they are trying to solve a fundamental issue of a direct application of the predicate logic without using a logic based approach.

View updates are too fundamental to The RM for not using logic.

>Base tables are (should be) normalised and involve no data duplication.
>Views by contrast are often deliberately denormalised (e.g. join
>views), contain summary data, and duplicate data also visible in other
>views (e.g. LONDON_SUPPLIERS and FURNITURE_SUPPLIERS). Or perhaps this
>is a very common mis-use of views - base database design?

No, but I don't see any fundamental difference. Views are still relation variables.

Regads Received on Tue Sep 21 2004 - 16:10:13 CEST