SQL and Relational Theory, 2nd Edition

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