Re: On view updating

Alfredo Novoa wrote:
> "Tony Andrews" <andrewst_at_onetel.com> wrote in message
news:<1095685404.749627.19430_at_h37g2000oda.googlegroups.com>...
>
> > Agreed. I don't like a lot of the view updating rules laid out in
the
> > latest Intro to DB Systems, and nor apparently does C J Date any
more.
> > I'm intrigued that David McGoveran still considers it a "solved
> > problem".
>
> Perhaps because he has fixed the rules.

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

> > I suppose it depends on what you mean by solved. We seem to
> > have started from "All views are updatable; SQL vendors should do
> > better" via "all views are updateable - subject to certain
constraints"
> > to "all views are updatable - subject to certain constraints AND
the
> > Assignment Principle". This is all very well, but it seems to me
we
> > are effectively back to square one: "some views are updateable;
some
> > are not".
>
> All views are updateable, but some views are updateable never :D
>
> But most views are updateable.
>
> For instance.
>
> 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?

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!

I know that computers can't exhibit intuition or common sense, and so have to follow some kind of generalised rule. But the generalised rule here is (a) almost certainly undesirable, and (b) will (hopefully) fail due to integrity constraints and the Assignment Principle. So yes, we have a method under which DML against a view can be transformed into DML against the base tables, but in practice it doesn't do what we want. That is rather different from saying that view updating is solved, IMHO.

> > Some clear problems with updating views:
> > 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;

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

> > 2) views, unlike base tables, are often not even in 2NF - hence
> > updating views could lead to "update anomalies": if you delete the
last
> > v_employee then you also delete his department.
>
> This is an specifical problem of a rule that does not follow the
> logical inference: not (P(A) or P(B)) => not P(A) and not P(B).
>
> That stuff happens when do you use the logic without logic, like a
> direct application of Set Theory and Predicate Logic, blah, blah,
> blah, that uses strange (but symmetrical) ad hoc rules instead of
> making logical inferences.
>
> Frankly, I am rather surprised about this.

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

> > My feeling is that views are NOT equivalent to base tables in all
> > cases
>
> Feelings are not enough. In which cases views are not equivalent to
> base tables?

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? Received on Tue Sep 21 2004 - 12:19:00 CEST