Re: A different definition of MINUS, Part 3

On Dec 19, 10:29 am, vadim..._at_gmail.com wrote:
> x ^ R00 = dx ^ R00 &    % x and dx have the same headers
> y ^ R00 = dy ^ R00 &    % y and dy have the same headers
> x ^ R00 = y ^ R00 &     % x and y have the same headers
> x ^ R00 = dz ^ R00 &    % x and dz have the same headers
> (dz ^ (x ^ y)) v R00 = R00 &     % dz disjoint with x ^ y
> (x v dx) ^ (x v dy) = (x ^ y) v dz   % application of increments on
> the base relations
>                                      % is the same as increment on
> join view
> -> x v dx = x v dz.

There were typos, some weak, and some wrong assumptions! Here is corrected "intersection view update" statement:

x ^ R00 = dx ^ R00 &     % x and dx have the same headers
y ^ R00 = dy ^ R00 &     % y and dy have the same headers
x ^ R00 = y ^ R00 &      % x and y have the same headers
(x ^ y) ^ R00 = dz ^ R00 & % x ^ y and dz have the same headers
(dx ^ x) v R00 = R00 &               % dx disjoint with x
(dy ^ y) v R00 = R00 &               % dy disjoint with y
(dz ^ (x ^ y)) v R00 = R00 &         % dz disjoint with x ^ y
dx = dz v (x ^ R00) &                % dx is a projection of dz
dy = dz v (y ^ R00)                 % dy is a projection of dz

                                     % on the base relations
                                     % is the same as increment
                                     % on join view

It can be proved as follows. Since dz, x and dx all have the same header, then dx = dz. The statement conclusion is nothing more than distributivity assertion under condition of all relations having the same header.

Let's move on to other cases. Oracle treats a join view as updatable under the following conditions:

    *The DML statement must affect only one table underlying the join.     *For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.

    *For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view was created WITH CHECK OPTION, then join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.

Let's restrict the scope to updates only. Here is a proposition I translated these conditions to:

x ^ R00 = dx ^ R00 & % x and dx have the same headers

(x ^ y) ^ R00 = dz ^ R00 &  % x ^ y and dz have the same headers
(dx ^ x) v R00 = R00 &           % dx disjoint with x
(dx ^ y) v R00 = R00 &           % dx "doesn't affect" y
(dz ^ (x ^ y)) v R00 = R00 &     % dz disjoint with x ^ y
dx = dz v (x ^ R00) ->           % dx is a projection of dz
(x v dx) ^ y = (x ^ y) v dz.     % Then, application of increments
                                 % on the base relations
                                 % is the same as increment
                                 % on join view

dx =
{<y=a,x=0,>,<y=b,x=0,>,<y=b,x=2,>,<y=c,x=0,>,<y=c,x=1,>,<y=c,x=2,>,}
dz =
{<y=a,x=0,>,<y=b,x=0,>,<y=b,x=2,>,<y=c,x=0,>,<y=c,x=1,>,<y=c,x=2,>,}

so it must be that I interpreted "The DML statement must affect only one table underlying the join" wrong... Received on Fri Dec 19 2008 - 20:56:49 CET