SQL and Relational Theory, 2nd Edition

RETRIEVAL OPERATIONS

The Principle of Interchangeability implies that (a) users should be able to operate on views as if they were base relvars and (b) the DBMS should be able to map those user operations into suitable operations on the base relvars in terms of which the views are ultimately defined. I say “ultimately defined” here because if views really do behave just like base relvars, then one thing we can do is define further views on top of them, as in this SQL example:

     CREATE VIEW LS_STATUS
       AS ( SELECT SNO , STATUS
            FROM   LS ) ;

In this section, I limit my attention to the mapping of read-only or “retrieval” operations, for simplicity (I remind you that the operations of the relational algebra are indeed all read-only). In fact, the process of mapping a read-only operation on a view to operations on the underlying relvars is in principle quite straightforward. For example, suppose we issue this SQL query on the London suppliers view LS (I deliberately show all name qualifications explicitly):

     SELECT LS.SNO
     FROM   LS
     WHERE  LS.STATUS > 10

First, then, the DBMS replaces the reference to the view in the FROM clause by the expression that defines that view, yielding:

     SELECT LS.SNO
     FROM ( SELECT S.*
            FROM   S
            WHERE  S.CITY = 'London' ) AS LS
     WHERE  LS.STATUS > 10

This expression can now be directly evaluated. However—and for performance reasons perhaps more significantly—it can first be simplified to:

     SELECT S.SNO
     FROM   S
     WHERE  S.CITY = 'London'
     AND    S.STATUS > 10

In all likelihood, this latter ...