inverse view
Mikito Harakiri
V. Vianu: Databases and Finite-Model Theory, AMS DIMACS Series in Discrete
Mathematics and Theoretical Computer Science, vol.31, N. Immerman and P.
Kolaitis eds, vol. 31, pp. 97-148, 1997
It says that complementary views were discovered in Bancilhon & Spyratos paper
but Keller & Ullman demonstrated their limited applicability.
From: Vadim Tropashko (vadi...@yahoo.com)
Subject: inverse view
Newsgroups: comp.databases.theory
Date: 2001-02-06 11:20:07 PST
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Introduction
^^^^^^^^^^^^
Relational databases live longer than applications that use them. In a
long database lifetime its schema inevitably changes. Those changes
often break existing applications unless application developers provide
some kind of logical data independence. Database Views provide an
exellent way to achieve logical data independence. In theory, database
views are superior to any procedural solution (such as stored produre
interfaces), because views have fundamental relational closure
property. This statement, however, is seriously undermined by existing
limitations on view updates.
Problem of database view updates has a long history. "An Introduction
to Database System" by C.J. Date has an inspiring chapter on view
updates with extensive bibliography. Not surprisingly, author proposes
his solution to update view problem as well. He revised it in his
latest "Relational Database Writings" series of papers, but his
approach is inherently case-by-case analysis. It is a matter of
believe, of course, whether case-by-case approach is fitting well into
Relational Theory.
This article introduces a new concept of inverse view and demonstrates
that a problem of view updates is essntially a problem of deducing
inverse view. An open question remains if it’s possible to deduce
inverse view from direct view automatically.
Schema evolution example
^^^^^^^^^^^^^^^^^^^^^^^^
In a typical database schema evolution example the table
TABLE contact (
id NUMBER,
voice VARCHAR2(10),
fax VARCHAR2(10)
);
might require additional cellular column. Instead of growing the table,
however, it is better to reorganize it like this:
CREATE TABLE newcontact (
id NUMBER,
phonetype VARCHAR2(5),
number VARCHAR2(10)
);
Each record in the old contact table
ID VOICE FAX
----------------------------------
1 4150000000 4081111111
2 80012345672 6501234567
is equivalent to 2 records in the newcontact table
ID PHONETYPE NUMBER
---------------------------------
1 VOICE 4150000000
1 FAX 4081111111
2 VOICE 8001234567
2 FAX 6501234567
Clearly, if we simply drop the old table and naively assign contact
name to the new table it would break existing applications. Let’s take
a look how we can handle this problem.
Mapping View
^^^^^^^^^^^^
The view
CREATE VIEW oldcontactview AS
select a.id, a.number voice, b.number fax
from newcontact a, newcontact b
where a.id = b.id
and a.phonetype = 'VOICE'
and b.phonetype = 'FAX';
transforms the data in the newcontact table to look just like the old
contact table. If we drop the old contact table and use a synonym to
this view instead of the old table, then, nothing have to be changed in
the applications that still use old contact name.
There is one technical problem, though. View oldcontactview is not
modifiable, and any attempt to insert, update or delete a row would
fail. Some RDBMS allow user to define view as updatable through update
trigger coding. Update triggers, therefore, are essential for full
backward compatibility of this schema transformation. Here is the code
that works in our example (you might ignore trigger declaration syntax,
which is unimportant; just note the two insert statements in the
trigger body):
CREATE TRIGGER oldcontactview_insert
INSTEAD OF INSERT ON oldcontactview
REFERENCING NEW AS n
FOR EACH ROW
BEGIN
insert into newcontact values
(:n.id, 'VOICE', :n.voice );
insert into newcontact values
(:n.id, 'FAX', :n.fax );
END;
(I omitted update and delete parts in order to save the space).
Writing update triggers might be the end point in the traditional
database schema redesign project, but I’m still left somewhat
unsatisfied with this solution.
Writing even that small amount of trigger code (actually, 3 times more
than that;-) makes the whole view approach less appealing. I’ll
demontrate in the next section, that oldcontactview is fully updatable,
though, only if we extend the definition of what updateable view is.
Inverse View
^^^^^^^^^^^^
Informal criteria for deciding if a view is modifiable is ambiguity.
Basically, if update operation on a view could be interpreted in
different ways how could database server know what operations on the
base table(s) to execute? In Oracle RDBMS manual this informal idea has
been narrowed down to a rigorous definition of a view based upon key-
preserving table. Omitting the details, I’ll just say that
oldcontactview doesn’t fall into this category and, therefore, is not
modifiable. (this is why I had to write update trigger in the previous
section). Are update operations on oldcontactview ambiguous, though? If
they are not, then how could we extend the definition of what
modifiable view is?
Trigger code that we wrote in previous section have no resemblance to
oldcontactview definition. Where those 2 insert operations came from,
then? To answer these questions let’s declare another view:
create view NEWCONTACTVIEW as
select id, "VOICE" phonetype, voice number
from contact
union
select id, "FAX" phonetype, fax number
from contact
This view is exactly the opposite of oldcontactview. While
oldcontactview defines what data must be in the old table if we know
the content of the new table, the newcontactview defines new table data
if we know the old one. Yes, this view is definitely useful, especially
if we want to migrate the data from old table to new one, but I have
more advanced application in mind. I reserve new name for it
- let’s call it an inverse view, while the original oldcontactview
would simply be a direct view. Formally, an inverse view is such a view
that applying it after direct view will restore the content of the base
table that the direct view uses in its declaration.
Modifiable view definition
^^^^^^^^^^^^^^^^^^^^^^^^^^
Thesis: A view is modifiable if an inverse view exists.
~~~~~~
To justify this thesis I’ll demonstrate that writing "INSTEAD OF"
trigger is simply a matter of embedding inverse view into trigger’s
body. Some auxiliary table
TABLE oldcontact (
id NUMBER,
voice VARCHAR2(10),
fax VARCHAR2(10)
);
is required, since the new record data must be put into some kind of
table before they could be used by a view’s select statement
CREATE TRIGGER oldcontactview_insert
INSTEAD OF INSERT
ON oldcontactview
REFERENCING NEW AS N OLD AS OLD
BEGIN
insert into OLDCONTACT values
(:n.id, :n.voice, :n.fax);
insert into newcontact (
select id, 'VOICE' phonetype, voice number
from OLDCONTACT
union
select id, 'FAX' phonetype, fax number
from OLDCONTACT
);
delete from OLDCONTACT;
END;
This is more code than the last version of the trigger had, and it will
definitely be slower executed, but the point here is that trigger code
is nothing more than mechanical application of inverse view. If you
still don’t quite believe in this, try to improve the code as follows:
1. Introduce a new procedure trigger_insert that accepts two
arguments: new record, the name of the view, and, the name of temporary
table.
2. One possible (not necessary the most efficient) way to implement
trigger_insert procedure body, is to make both insert statements to be
dynamic SQL. You need to navigate Oracle Data Dictionary in order to be
able to figure out the column names of OLDCONTACT table. You also need
to substitute the name of the table in newcontactview when building
second insert statement.
Again any further refactoring of the trigger code is purely academic
exercise as you’ll hardly do any better than the original version. But
this is a proof that database server can treat this view, and any other
view that has inverse, as a modifiable view. It is server’s job then to
implement a view update the most efficient way.
Inverse view deduction
^^^^^^^^^^^^^^^^^^^^^^
It would be ideal if RDBMS would be able to deduce inverse view
automatically. My next example demonstrates that in order to do that
RDBMS must be at least as poweful as computer algebra system:
TABLE CartesianPoint (
x NUMBER,
y NUMBER,
);
TABLE PolarPoint (
r NUMBER,
theta NUMBER,
);
create view Cartesian2Polar as
select SQRT(x*x+y*y) r, arcsin(y/SQRT(x*x+y*y)) theta from
CartesianPoint
create view Polar2Cartesian as
select r*cos(theta) x, r*sin(theta) y from PolarPoint
Here, Cartesian2Polar and Polar2Cartesian are two views that are
inverse to each other, but deducing one from another requres abilites
at least to solve systems of algebraic equations.
Join Example
^^^^^^^^^^^^
Finally what is a conclusion about classic natural join examples like
Employee/Department? It is intuitively clear that inverse view dosn't
exist, but better observation would be that inverse view is not defined
in such cases simply because there is more than one source table. We
need to generalise our definition where a set of direct views maps a
set of source tables into a set of target tables. Inverse views, then
map a set of target tables back to sources. In order to be able to
define inverse view in Employee/Department case we need to add one more
direct view, say, identity map
create view EmployeeIdentityMap
select * from Employee
Then, every transaction that updates EmployeeDepartment joint view must
also perform some action on EmployeeIdentityMap and this would resolve
the ambiguty that prevented us defining inverse view!