6.1 What if anything is wrong with the following SQL expressions (from a relational perspective or otherwise)?

6.2 Closure is important in the relational model for the same kind of reason that numeric closure is important in ordinary arithmetic. In arithmetic, however, there’s one situation where the closure property breaks down, in a sense—namely, division by zero. Is there any analogous situation in the relational algebra?

6.3 Given the usual suppliers-and-parts database, what’s the value of the Tutorial D expression JOIN {S,SP,P}? What’s the corresponding predicate? And how would you express this join in SQL?

6.4 Why do you think the project operator is so called?

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

6.6 Union, intersection, product, and join are all both commutative and associative. Verify these claims. Are they valid in SQL?

6.7 Which of the operators described in this chapter (if any) have a definition that doesn’t rely on tuple equality?

6.8 The SQL FROM clause FROM t1, t2, ..., tn (where each ti denotes a table) returns the product of its arguments. But what if n = 1?—what’s the product of just one table? And by the way, what’s the product of t1 and t2 if t1 and t2 both contain duplicate rows?

6.9 Write Tutorial D and/or SQL expressions for the following queries on the suppliers-and-parts database:

6.10 Prove the following statements (making them more precise where necessary):

6.11 Union is said to be idempotent, because r UNION r is identically equal to r for all r. (Is this true in SQL?) As you might expect, idempotence can be useful in expression transformation. Which other relational operators, if any, are idempotent?

6.12 Let r be a relation. What does the Tutorial D expression r{} mean (i.e., what’s the corresponding predicate)? What does it return? Also, what does the Tutorial D expression r{ALL BUT} mean, and what does it return?

6.13 The boolean expression x > y AND y > 3 (which might be part of a query) is equivalent to—and can therefore be transformed into—the boolean expression x > y AND y > 3 AND x > 3. (The equivalence is based on the fact that the comparison operator “>” is transitive; i.e., a > b and b > c together imply a > c.) Note that the transformation is certainly worth making if x and y are from different relations, because it enables the system to perform an additional restriction (using x > 3) before doing the greater-than join implied by x > y. As we saw in the body of the chapter, doing restrictions early is generally a good idea; having the system infer additional “early” restrictions, as here, is also a good idea. Do you know of any SQL products that actually perform this kind of optimization?

6.14 Consider the following Tutorial D expression:

     WITH ( PP := P WHERE COLOR = 'Purple' ,
            T  := SP RENAME { SNO AS X } ) :
     S WHERE ( T WHERE X = SNO ) { PNO } ⊇ PP { PNO }

What does this expression mean? Given our usual sample data values, show the result returned. Does that result accord with your intuitive understanding of what the expression means? Justify your answer.

6.15 SQL has no direct counterpart to either D_UNION or I_MINUS. How best might the D_UNION and I_MINUS examples from the body of the chapter—i.e., S{CITY} D_UNION P{CITY} and S{CITY} I_MINUS P{CITY}—be simulated in SQL?

6.16 What do you understand by the term joinable? How could the definition of the term be extended to cover the case of n relations for arbitrary n (instead of just n = 2, which was the case discussed in the body of the chapter)?

6.17 What exactly is it that makes it possible to define n-adic versions of JOIN and UNION (and D_UNION)? Does SQL have anything analogous? Why doesn’t an n-adic version of MINUS (or I_MINUS) make sense?

6.18 I claimed earlier in the book that TABLE_DEE meant TRUE and TABLE_DUM meant FALSE. Substantiate and/or elaborate on these claims.

6.19 What exactly does the following SQL expression return?

     SELECT DISTINCT S.*
     FROM   S , P

Warning: There’s a trap here.