With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required