SQL and Relational Theory, 2nd Edition

EXERCISES

7.1 For each of the following Tutorial D expressions on the suppliers-and-parts database, give both (a) an SQL analog and (b) an informal interpretation of the expression (i.e., a corresponding predicate) in natural language. Also show the result of evaluating the expressions, given our usual sample values for relvars S, P, and SP.

1. S MATCHING ( SP WHERE PNO = ‘P2’ )

2. S NOT MATCHING ( SP WHERE PNO = ‘P2’ )

3. P WHERE ( !!SP ) { SNO } = S { SNO }

4. P WHERE SUM ( !!SP , QTY ) < 500

5. P WHERE TUPLE { CITY CITY } ∈ S { CITY }

6. EXTEND S : { TAG := ‘Supplier’ }

7. EXTEND ( S MATCHING ( SP WHERE PNO = 'P2' ) ) :
                               { TRIPLE_STATUS := 3 * STATUS }

8. EXTEND ( P JOIN SP ) : { SHIPWT := WEIGHT * QTY }

9. EXTEND P : { GMWT := WEIGHT * 454 , OZWT := WEIGHT * 16 }

10. EXTEND P : { SCT := COUNT ( !!SP ) }

11. EXTEND S :
        { NP := COUNT ( ( SP RENAME { SNO AS X } ) WHERE X = SNO ) }

12. SUMMARIZE S BY { CITY } : { SUM_STATUS := SUM ( STATUS ) }

13. SUMMARIZE ( S WHERE CITY = 'London' ) PER ( TABLE_DEE ) :
                                                  { N := COUNT ( SNO ) }

    Note: Tutorial D allows the PER clause to be omitted, in which case PER (TABLE_DEE) is assumed by default. The foregoing SUMMARIZE could therefore be simplified slightly, thus:

    SUMMARIZE ( S WHERE CITY = 'London' ) : { N := COUNT ( SNO ) }

14. EXTEND SP WHERE SNO = ‘S1’ : { SNO := ‘S7’ , QTY = 0.5 * QTY }

7.2 In what circumstances (if any) are r1 MATCHING r2 and r2 MATCHING r1 equivalent?

7.3 Show that RENAME isn’t primitive.

7.4 Give an expression involving EXTEND instead of SUMMARIZE that’s logically equivalent to the following: ...