Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?
Date: Thu, 20 Feb 2003 11:48:53 -0000
Message-ID: <b32jok$19em$1_at_sp15at20.hursley.ibm.com>
"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message
news:D0z4a.21$h_3.106_at_news.oracle.com...
[snip]
> > The systematic approaches to the view update problem based on
> > predicate logic does not work with bags.
>
> I have a doubt about Date's approach. Is he treating each operation
> individually; independently of each other? Is he treating each new tuple
> inserted into a relation individually; unrelated to other tuples inserted
> (in the same transaction)?
I also have doubts about Date's view updating approach.
> Returning to the example
>
> select id, 'VOICE' type, voice phone
> from contact
> union
> select id, 'FAX' type, fax phone
> from contact
>
> we can easily see that both assumtions aren't correct:
>
> 1. Sucessively applied operations can't be considered independently of each
> other. In the above example union can't be considered independently of the
> antiprojection (BTW, what is the correct term for adding a pseudocloumn?).
> 2. Two tuples inserted together into a derived relation correspond to a
> single tuple insertion in the base relation. The approach based upon "single
> tuple" translation is flawed.
Good example and good points Mikito. It would be interesting to know if Date would have answers to the above problem.
So, given in database A;
TABLE contacts (
id NUMBER, voice VARCHAR(10), fax VARCHAR(10),
UNIQUE (id)
)
and in database B:
TABLE phones (
id NUMBER, phonetype VARCHAR(5) CHECK phonetype IN ('VOICE', 'FAX'), number VARCHAR(10),
UNIQUE (id, phonetype),
CHECK NOT EXISTS
(SELECT 1 from PHONES GROUP BY id HAVVING COUNT(*) <> 2) )
from contact
So the
INSERT INTO phones VALUES
(1,'VOICE','01922 123 123'),
(1,'FAX','01922 123 100')
should be maped automatically to
INSERT INTO contacts VALUES
(1,'01922 123 123', 'FAX','01922 123 100')
and vis-versa
and the
INSERT INTO contacts VALUES
(1,'01922 123 123', 'FAX','01922 123 100')
should be maped automatically to
INSERT INTO phones VALUES
(1,'VOICE','01922 123 123'),
(1,'FAX','01922 123 100')
Now a RDBMS that supported all that would be top.
Especially if it supported:
Deriving the 2 mapping views solely from the 2 tables. Deriving table B and mapping B -> A from table A and the A->B mapping. If A, B and A-> B are declared by a user, the DBMS should be able to check the correctness of the mapping.
BTW Is information equivelence required? Do we need to know that every possible value of table A maps to a unique value of table B and vis-versa.
FYI In DB2 we can insert into UNION views, but it's strictly a one tuple in the view to one tuple in one table affair. I could use INSTEAD OF triggers, but to map the two inserts into one could still be difficult.
Regards
Paul Vernon
Business Intelligence, IBM Global Services
Received on Thu Feb 20 2003 - 12:48:53 CET