EXERCISES

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

  1. SELECT * FROM S , SP

  2. SELECT SNO , CITY FROM S

  3. SELECT SNO , PNO , 2 * QTY FROM SP

  4. SELECT S.SNO FROM S , SP

  5. SELECT S.SNO , S.CITY FROM S NATURAL JOIN P

  6. SELECT CITY FROM S UNION SELECT CITY FROM P

  7. SELECT S.* FROM S NATURAL JOIN SP

  8. SELECT * FROM S JOIN SP ON S.SNO = SP.SNO

  9. SELECT * FROM ( S NATURAL JOIN P ) AS TEMP

  10. SELECT * FROM S CROSS JOIN SP CROSS JOIN P

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.

  1. ( S JOIN ( SP WHERE PNO = ‘P2’ ) ) { CITY }

  2. ( P { PNO } MINUS ( SP WHERE SNO = ‘S2’ ) { PNO } ) JOIN P

  3. S { CITY } MINUS P { CITY }

  4. ( S { SNO , CITY } JOIN P { PNO , CITY } ) { SNO , PNO }

  5. JOIN { ( S RENAME { CITY AS SC } ) { SC } ,
               ( P RENAME { CITY AS PC } ) { PC } }

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:

  1. Get all shipments.

  2. Get supplier numbers for suppliers who supply part P1.

  3. Get suppliers with status in the range 15 to 25 inclusive.

  4. Get part numbers for parts supplied by a supplier in London.

  5. Get part numbers for parts not supplied by any supplier in London.

  6. Get all pairs of part numbers such that some supplier supplies both of the indicated parts.

  7. Get supplier numbers for suppliers with a status lower than that of supplier S1.

  8. Get part numbers for parts supplied by all suppliers in London.

  9. Get (SNO,PNO) pairs such that the indicated supplier does not supply the indicated part.

  10. Get suppliers who supply at least all parts supplied by supplier S2.

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

  1. A sequence of restrictions of a given relation can be transformed into a single restriction.

  2. A sequence of projections of a given relation can be transformed into a single projection.

  3. A restriction of a projection can be transformed into a projection of a restriction.

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.220.106.9