SQL and Relational Theory, 2nd Edition

EXERCISES

9.1 Define a view consisting of supplier-number / part-number pairs for suppliers and parts that aren’t colocated. Give both Tutorial D and SQL definitions.

9.2 Let view LSSP be defined as follows (SQL):

     CREATE VIEW LSSP AS ( SELECT SNO , SNAME , STATUS , PNO , QTY
                           FROM   S NATURAL JOIN SP
                           WHERE  CITY = 'London' ) ;

Here’s a query on this view:

     SELECT DISTINCT STATUS , QTY FROM LSSP
     WHERE  PNO IN ( SELECT PNO FROM P WHERE  CITY <> 'London' )

What might the query that’s actually executed on the underlying base tables look like?

9.3 What key(s) does view LSSP from Exercise 9.2 have? What’s the predicate for that view?

9.4 Given the following Tutorial D view definition—

     VAR HP VIRTUAL ( P WHERE WEIGHT > 14.0 ) KEY { PNO } ;

—show the converted form after the substitution procedure has been applied for each of the following expressions and statements:

1. HP WHERE COLOR = ‘Green’

2. ( EXTEND HP : { W := WEIGHT + 5.3 } ) { PNO , W }

3. INSERT HP RELATION { TUPLE { PNO 'P9' , PNAME 'Screw' , WEIGHT 15.0 ,
                                COLOR 'Purple' , CITY 'Rome' } } ;

4. DELETE HP WHERE WEIGHT < 9.0 ;

5. UPDATE HP WHERE WEIGHT = 18.0 : { COLOR := ‘White’ } ;

9.5 Give SQL solutions to Exercise 9.4.

9.6 Give as many reasons as you can think of for wanting to be able to declare keys for a view.

9.7 Using either the suppliers-and-parts database or any other database you happen to be familiar with, give some further examples (over and above the London vs. non London suppliers example, that is) to illustrate the point that which relvars are base ...