SQL and Relational Theory, 2nd Edition

CHAPTER 9

9.1

        VAR NON_COLOCATED VIRTUAL
 ( ( S { SNO } JOIN P { PNO } ) NOT MATCHING ( S JOIN P ) )
KEY { SNO , PNO } ;
     CREATE VIEW NON_COLOCATED AS
          ( SELECT SNO , PNO
            FROM   S , P
            WHERE  S.CITY <> P.CITY )
            /* UNIQUE ( SNO , PNO ) */ ;

9.2 Substituting the view definition for the view reference in the outer FROM clause, we obtain:

     SELECT DISTINCT STATUS , QTY
     FROM ( SELECT SNO , SNAME , STATUS , PNO , QTY
            FROM   S NATURAL JOIN SP
            WHERE  CITY = 'London' ) AS Temp
     WHERE  PNO IN
          ( SELECT PNO
            FROM   P
            WHERE  CITY <> 'London' )

This simplifies (potentially!) to:

     SELECT DISTINCT STATUS , QTY
     FROM   S NATURAL JOIN SP
     WHERE  CITY = 'London'
     AND    PNO IN
          ( SELECT PNO
            FROM   P
            WHERE  CITY <> 'London' )

9.3 The sole key is {SNO,PNO}. The predicate is: Supplier SNO is under contract, is named SNAME, has status STATUS, is located in London, and supplies part PNO in quantity QTY.

9.4 Note that a. and b. are expressions, the rest are statements.

1.   ( P WHERE WEIGHT > 14.0 ) WHERE COLOR = 'Green'

   This expression can be simplified to:

   P WHERE WEIGHT > 14.0 AND COLOR = 'Green'

   The simplification is worth making, too, because the first formulation implies two passes over the data while the second implies just one.

2.   ( EXTEND ( P WHERE WEIGHT > 14.0 ) :
                    { W := WEIGHT + 5.3 } ) { PNO , W }

3.   INSERT ( P WHERE WEIGHT > 14.0 )
   RELATION { TUPLE { PNO 'P9' , PNAME 'Screw' , WEIGHT 15.0 ,
                                 COLOR 'Purple' , CITY 'Rome' } } ;

   Observe that this INSERT is logically equivalent to a relational assignment in which the target is specified as something ...