Solving view transformantion equations

"Vadim Tropashko" <vadimtro_at_yahoo.com> wrote in message news:22d2e427.0309160856.76a0e143_at_posting.google.com... > mikharakiri_at_yahoo.com (Mikito Harakiri) wrote in message news:<bdf69bdf.0309151944.606515f3_at_posting.google.com>...

> Better example would be
>
> Q1 = (SELECT name
>   FROM Emps as D
>   WHERE dept = "sales"
> )
> Q2 = (SELECT name, dept
>   FROM Emps as D
>   WHERE dept <> "sales"
> )
>
> Perfectly updateable view, no constraint is necessary. The inverse view is
>
> Q^(-1) = (
>   select name, 'sales' as dept from EmpsTr as V1
>   union
>   select name, dept from V2
> )

Here is how we solve equations

Q1 * D = V1
Q2 * D = V2

in small incremental steps. We'll get explicit formula for Q^(-1) at the end!

We start with system of equations:

V1(name) = (SELECT name FROM D1
  WHERE dept = "sales"
) * D1(name,dept)

V2(name, dept) = (SELECT name, dept FROM D1   WHERE dept <> "sales"
) * D1(name,dept)

Where switching notation D -> D1 ephasizes the fact that we have one base relation only.

At the very first step we apply (SELECT name, "sales" as dept FROM V1) to both sides of the first equation. The result:

(SELECT name, "sales" as dept FROM V1) * V1(name)
= (SELECT name, "sales" as dept FROM V11) * (   SELECT name FROM D1
  WHERE dept = "sales"
) as V11 * D1(name,dept)

where V11 alias has been introduced as a name of intermediate relation. We merge inner view next:

(SELECT name, "sales" as dept FROM V1) * V1(name)
= (SELECT name, "sales" as dept
  FROM D1
  WHERE dept = "sales"
) * D1(name,dept)

Then, we apply (SELECT name, dept FROM V2) to both sides of the second equation:

(SELECT name, dept FROM V2) *V2(name, dept)
= (SELECT name, dept FROM V22) *
(SELECT name, dept FROM D1
  WHERE dept <> "sales"
) as V22 * D1(name,dept)

Next, we trivially merge inner view:

(SELECT name, dept FROM V2) *V2(name, dept)
= (SELECT name, dept FROM D1
  WHERE dept <> "sales"
) * D1(name,dept)

We pick up where we left the first equation, and add both equations together:

(SELECT name, "sales" as dept FROM V1) * V1(name)
union
(SELECT name, dept FROM V2) *V2(name, dept)
= (SELECT name, "sales" as dept FROM D1
  WHERE dept = "sales"
) * D1(name,dept)
union
(SELECT name, dept FROM D1
  WHERE dept <> "sales"
) * D1(name,dept)

Next, we syntactically rewrite left side as a single query aplied to a vector of views:

(SELECT name, "sales" as dept FROM V1
 union
 SELECT name, dept FROM V2) *<V1(name),V2(name, dept)> =
(SELECT name, "sales" as dept FROM D1
  WHERE dept = "sales"
) * D1(name,dept)
union
(SELECT name, dept FROM D1
  WHERE dept <> "sales"
) * D1(name,dept)

Same manipulation at the right side. We also substitute literal "sales" in the select list with the dept column name leveraging "where dept='sales'" restriction:

(SELECT name, "sales" as dept FROM V1
 union
 SELECT name, dept FROM V2) * <V1(name),V2(name,dept)> =
(SELECT name, dept FROM D1
  WHERE dept = "sales"
 union
  SELECT name, dept FROM D1
  WHERE dept <> "sales"
) * <D1(name,dept)>

Next, we rewrite UNION as OR:

(SELECT name, "sales" as dept FROM V1
 union
 SELECT name, dept FROM V2) * <V1(name),V2(name,dept)> = (SELECT name, dept FROM D1
  WHERE dept = "sales" OR dept <> "sales" ) * <D1(name,dept)>

Then, we drop tautological restriction:

(SELECT name, "sales" as dept FROM V1
 union
 SELECT name, dept FROM V2) *< V1(name),V2(name,dept)> = (SELECT name, dept FROM D1
) * <D1(name,dept)>

Finally, by eliminating identity operator we arrive to the equation defining the inverse view:

(SELECT name, "sales" as dept FROM V1
 union
 SELECT name, dept FROM V2)

• <V1(name),V2(name,dept)> = <D1(name,dept)>