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.
S MATCHING ( SP WHERE PNO = ‘P2’ )S NOT MATCHING ( SP WHERE PNO = ‘P2’ )P WHERE ( !!SP ) { SNO } = S { SNO }P WHERE SUM ( !!SP , QTY ) < 500P WHERE TUPLE { CITY CITY } ∈ S { CITY }EXTEND S : { TAG := ‘Supplier’ }EXTEND ( S MATCHING ( SP WHERE PNO = 'P2' ) ) : { TRIPLE_STATUS := 3 * STATUS }EXTEND ( P JOIN SP ) : { SHIPWT := WEIGHT * QTY }EXTEND P : { GMWT := WEIGHT * 454 , OZWT := WEIGHT * 16 }EXTEND P : { SCT := COUNT ( !!SP ) }EXTEND S : { NP := COUNT ( ( SP RENAME { SNO AS X } ) WHERE X = SNO ) }SUMMARIZE S BY { CITY } : { SUM_STATUS := SUM ( STATUS ) }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 ) }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:
SUMMARIZE SP PER ( S { SNO } ) : { NP := COUNT ( PNO ) }7.5 Consider the following Tutorial D expressions. Which if any are equivalent to which of the others? Show an SQL analog in each case.
SUMMARIZE r PER ( r { } ) : { CT := SUM ( 1 ) }
SUMMARIZE r PER ( TABLE_DEE ) : { CT := SUM ( 1 ) }
SUMMARIZE r BY { } : { CT := SUM ( 1 ) }
EXTEND TABLE_DEE : { CT := COUNT ( r ) }
7.6 Consider the relational aggregate operators UNION and INTERSECT. What do you think these operators should return if their argument (a set of relations) happens to be empty?
7.7 Let relation R4 in Figure 7-1 denote the current value of some relvar. If R4 is as described in Chapter 2, what’s the predicate for that relvar?
7.8 Let r be the relation denoted by the following Tutorial D expression
SP GROUP ( { } AS X )What does r look like, given our usual sample value for SP? Also, what does the following expression yield?
r UNGROUP ( X )7.9 Write Tutorial D and/or SQL expressions for the following queries on the suppliers-and-parts database:
Get the total number of parts supplied by supplier S1.
Get supplier numbers for suppliers whose city is first in the alphabetic list of such cities.
Get city names for cities in which at least two suppliers are located.
Get city names for cities in which at least one supplier or at least one part is located, but not both.
Get part numbers for parts supplied by all suppliers in London.
Get suppliers who supply at least all parts supplied by supplier S2.
7.10 Let relation pp be as defined in the section A NOTE ON RECURSION and let TCLOSE be the transitive closure operator. What does the expression TCLOSE(TCLOSE(pp)) denote?
7.11 Given our usual sample values for the suppliers-and-parts database, what does the following Tutorial D expression denote?
EXTEND S : { PNO_REL := ( !!SP ) { PNO } }7.12 Let the relation returned by the expression in the previous exercise be kept as a relvar called SSP. What do the following updates do?
INSERT SSP RELATION
{ TUPLE { SNO 'S6' , SNAME 'Lopez' , STATUS 30 , CITY 'Madrid' ,
PNO_REL RELATION { TUPLE { PNO 'P5' } } } } ;
UPDATE SSP WHERE SNO = 'S2' :
{ INSERT PNO_REL RELATION { TUPLE { PNO 'P5' } } } ;7.13 Using relvar SSP from the previous exercise, write expressions for the following queries:
Get pairs of supplier numbers for suppliers who supply exactly the same set of parts.
Get pairs of part numbers for parts supplied by exactly the same set of suppliers.
7.14 A quota query is a query that specifies a desired limit, or quota, on the cardinality of the result: for example, the query “Get the two heaviest parts,” for which the quota is two. Give Tutorial D and SQL formulations of this query. Given our usual data values, what exactly do these formulations return?
7.15 Using the explicit SUMMARIZE operator, how would you deal with the query “For each supplier, get the supplier number and the sum of distinct shipment quantities for shipments by that supplier”?
7.16 Given a revised version of the suppliers-and-parts database that looks like this—
S { SNO } /* suppliers */
SP { SNO , PNO } /* supplier supplies part */
SJ { SNO , JNO } /* supplier supplies project */—give Tutorial D and SQL formulations of the query “For each supplier, get supplier details, the number of parts supplied by that supplier, and the number of projects supplied by that supplier.” For Tutorial D, give both EXTEND and SUMMARIZE formulations.
7.17 What does the following Tutorial D expression mean?
S WHERE ( !!(!!SP) ) { PNO } = P { PNO }7.18 Is there a logical difference between the following two Tutorial D expressions? If so, what is it?
EXTEND TABLE_DEE : { NSP := COUNT ( SP ) }
EXTEND TABLE_DEE : { NSP := COUNT ( !!SP ) }7.19 Give an example of a join that’s not a semijoin and a semijoin that’s not a join. When exactly are the expressions r1 JOIN r2 and r1 MATCHING r2 equivalent?
7.20 Let relations r1 and r2 be of the same type, and let t1 be a tuple in r1. For that tuple t1, then, what exactly does the expression !!r2 denote? And what happens if r1 and r2 aren’t just of the same type but are in fact the very same relation?
7.21 What’s the logical difference, if any, between the following SQL expressions?
SELECT COUNT ( * ) FROM S
SELECT SUM ( 1 ) FROM S7.22 By definition, ORDER BY can’t appear in a relational expression (or table expression, rather, in SQL). So where can it appear?