Lab Assignment 1
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, Product, PC, Laptop, and Printer of Exercise 5.2.1 in your ProgreSQL database on cs.calstatela.edu.

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 not monotone?

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


[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

We further assume that a) an owner may own multiple properties, b) a property is owned by only one owner, and c) a client may lease multiple properties, but only during different time periods.

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.