Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?

"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),

and in database B:

TABLE phones (

id              NUMBER,
phonetype       VARCHAR(5) CHECK phonetype IN ('VOICE', 'FAX'),
number          VARCHAR(10),

Then the 'mapping view' from A -> B, is

select id, 'VOICE' type, voice phone

        from contact
 union
 select id, 'FAX' type, fax phone

        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

the 'mapping view' from B-> A, is

select v.id, v.number as voice, f.number as fax

        from phones V, phones F
where V.i = F.i
and V.phonetype = 'VOICE'
and F.phonetype = 'FAX'

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