Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> comp.databases.theory -> Re: View updating in practice?
"Jens Lechtenbörger <lechtej_at_uni-muenster.de> wrote in message news:<m28yzxhlih.fsf_at_pcwi1068.uni-muenster.de>...
> > Relation A:
> >
> > ID VOICE FAX
> > -- ----------- --------------
> > 1 4150000000 4081111111
> > 2 80012345672 6501234567
> >
> > select id, 'VOICE' phonetype, voice number
> > from A
> > union
> > select id, 'FAX' phonetype, fax number from A
> >
> > What is the method to invert it?
>
> It should be a join of (a) the projection of the view on id and
> voice_number and (b) the projection of the view on id and
> fax_number, i.e., something like this:
>
> select v1.id, v1.voice_number, v2.fax_number
> from
> (select id, voice_number from view) as v1,
> (select id, fax_number from view) as v2
> where v1.id=v2.id
Correct. Note that cartesian product and union are dual operations
(The summary table at the end has a typo -- union is dual to CP.it is corrected in the followup message). Your result, where inverse view has CP operation in it, is not surprising from that perspective.
Now the idea is to treat the original view
select id, 'VOICE' phonetype, voice number from A
union
select id, 'FAX' phonetype, fax number from A
as a system of equations with relational operators, and then solve it like we do it in classic algebra with step-by-step elimination process. Received on Wed Nov 13 2002 - 13:22:16 CST