CS422, Summer 2004

Please print or write down your answers to the following questions on a piece of paper.

[SQL] (5pt)

1. Create the four tables,

2. Insert at least three tuples in each table.

3. Write SQL queries for Exercise 5.2.1 (a), (b), (c)

[Relational Algebra] (5pt)

1. Exercise 5.2.4 (a), (b), (h). Note that the answers should be relational algebra expressions, not SQL statements.

2. Exercise 5.2.8. Instead of answering the question "which opertors are monotone" (most of them are), answer the question: which operator(s) are

3. Exercise 5.2.10. Check out the online solution, and use an example to explain why PROJ_R ( R JOIN S ) does

[Decomposition] (5pt) Consider the following relation R about property leases:

clientNo |
cName |
propertyNo |
pAddress |
rentStart |
rentFinish |
rent |
ownerNo |
oName |

- clientNo -- client number, which uniquely identifies a client
- cName -- client name
- propertyNo -- property number, which uniquely identifies a property
- propertyAddress -- address of the property
- rentStart, rentFinish -- start and end date of the lease
- rent -- monthly rent in dollars
- ownerNo -- owner number which uniquely identifies an owner
- oName -- name of an owner

1. List the non-trivial FDs in R.

2. List the key(s) of R.

3. Decompose R into 3NF or BCNF or 4NF relations as appropriate.

4. For each relation after the decomposition, explain whether it's 3NF, BCNF, or 4NF.