Re: practical view updates

Mikito,

> Here are at least 2 missing points:
>
> 1. Definition. A view T is inverse to a view S iff T*S is identity
> map.

Are you saying that in order to update a view, we must know its inverse?

> 2. We need to broaden narrow perspective of updating a single view to
> that of a set of views. For example, a view
>
> create view Expensive
> select ename, sal from Emp
> where sal in (select max(sal) from emp)
>
> is clearly not updateable. A set of views
>
> create view Expensive
> select ename, sal from emp
> where sal in (select max(sal) from emp)
>
> create view Others
> select ename, sal from emp
> where sal not in (select max(sal) from emp)
>
> however, allows to define inverse view as
>
> select ename, sal from Expensive
> union
> select ename, sal from Others
>
> Then, a transaction
>
> delete from Others where ename = ‘Smith';
> insert into Expensive values (‘Smith', 10000);
> insert into Others values (‘John', 5000);
> commit
>
> could be resolved into
>
> delete from Emp where (ename, sal) in
> ((select ename, sal from Expensive
> union
> select ename, sal from Others) where ename = ‘Smith');
> insert into Etmp (ename, sal) values (‘Smith', 10000);
> insert into Otmp (ename, sal) values (‘John', 5000);
> insert into Emp
> select ename, sal from Etmp
> union
> select ename, sal from Otmp;
> delete from Etmp;
> delete from Otmp;
> commit;

I really don't see where you're going with this one. First, both views are already updatable, so long as the predicate of the view is satisfied. Second, The insert into Expensive still couldn't be done, unless the max salary of the set was already the given value. Third, why would it be done this way, I really don't see the relevance of the example.

> The conclusion "XPlusY must be equal to the evaluation of X + Y" seems
> trivial to me

As trivial as the statement is, I think you are missing the point it is making. What is the predicate of a view defined with the extend operator? In other words, the newly defined column forms a constraint on the view, and therefore no update is allowed which violates that constraint, namely that the value of a column introduced by the extend operator must be equal to the evaluation of its definition.

> unless the sole purpose of your exercise was
> demonstrating "superiority" of Mr.Date relational syntax.

This is not Mr. Date's syntax. It is D4. My point in stating that the examples were not relational was not based on syntax, but usage. The examples can be given in SQL and remain true to the relational model, as you're examples above have done. The only superiority as far as the syntax is concerned is that it does not allow a non-relational formulation of the statements.

> In other words, you exclude domain operators from the view update
> analysis?
> Then, you miss many practical problems like updating the view
>
> select FirstName || ‘ ‘ || LastName from customer

While updates based on non-relational operators could certainly be valuable, we regard it as a separate issue, orthogonal to the question of view updatability. For example, I should be able to say:

Substring(LString, 1, 5) := "New Substring";

Where LString is a variable of type String. Scalar and other types of updatability could certainly be useful in practice, but this functionality can be built on top of the existing rules for relational view updates. Moreover, we have no clear set of rules for doing it. There are only some very special case scenarios, like the few examples we have been discussing, and each case has a specific rule. We would like to see a much more general solution to this problem. The view updatability rules set forth by Mr. Date are a very general solution to the problem, there are no special case snags, _all_ views are updatable, meaning, very specifically, that each operator of the relational algebra has well-defined semantics regarding updatability.

> There are 2 problems.
>
> 1. NULL value in the Fax column. Mr. Date doesn't admit NULLs, so how
> application of his rules suddenly contain one?

As pointed out by Mr. Larsen, there are no nulls here. If not for the definition of the default values in the columns of the base table variable, the updates would fail, as I explicitly pointed out in the original example. Note that this is not to say that the view is updatable, but that a given update fails, there is a _big_ logical difference between the two statements.

> 2. It is not a single tuple insertion that must be translated, but the
> whole transaction.
>
> insert into phoneview (id, type, number) values (1,'VOICE',
> ‘123-4567');
> insert into phoneview (id, type, number) values (1,'FAX', ‘555-5555');
> commit;
>
> is equivalent to
>
> insert into contact (id, voice, fax) values (1, ‘123-4567',
> ‘555-5555');
> commit;
>
> It is obvious from inverse view perspective, but I admit that deriving
> inverse view itself is hard.

This is precisely the problem, as stated above, we want a general solution to the problem that does not involve inordinate amounts computation by either the system or the user.

> Again, fully supported except domain operators? Fully supported in
> purely relational domain, except for the problems like in the example
> above?

I have to disagree here, as I stated before, we don't see scalar updatability and view updatability as the same problem. Surely there are practical benefits to be gained from allowing scalar updatability, but this is far from worked out in general. It should be noted that we are actively researching this problem, as it has other implications for us as well.

> Well, my criticism is not up to high standards, but neither does
> product promotion in a theory newsgroup.

What is a theory newsgroup for if not the general solution to problems faced by the industry. We (Alphora) can hardly remain unbiased in these discussions when we know that there is a solution available. We are not trying to use the newsgroup for product promotion, but we do want the database community to be aware that there is a product that remains true to relational _theory_. It's hard to make that type of announcement without sounding like we are marketing. As Nathan has stated elsewhere, We wanted it, no one had it, so we built it. And we thought other people would want to know about it, I certainly would want to know about it, even in a theory newsgroup.

> I must correct my statement: Mr.Date's view updates coverage in "The
> Introduction to the Databases" is inspiring, but, nevertheless, his
> solution barely scratches the surface of the problem.

There are always ways to improve a given technology, but I believe Date's coverage does more than just scrath the surface. Rather, I believe it lays a foundation for solving the more complex problems.

Regards,
Bryn Rhodes
Alphora Received on Tue Oct 01 2002 - 05:32:24 CEST