NAME ____________________________________

Tuesday, April 25. 2000

100 pts.

1. (15 pts.) Briefly outline the steps involved in moving from a single form or report to a relational schema based on that form.

2. (5 pts.) In developing an entity-relationship diagram from a written description, nouns and verbs are usually associated with what objects in the resulting ER diagram (say which goes with which)?

3. (10 pts.) In the SPJ database, we often refer to the supplier table s(sno, sname, status, city) as either a table or a relation. Explain why calling it a relation is appropriate.

4. (10 pts.) We encounter the impedance mismatch problem when embedding SQL code in a traditional programming language (such as C). Explain briefly what the problem is, and how embedded SQL (or, equivalently, PL/SQL) solves the problem.

The following queries are with respect to the spj database:

s(sno, sname, status, city)

p(pno, pname, color, weight, city)

j(jno, jname, city)

spj(sno, pno, jno, qty)

5. (5 pts.) Write a statement which will delete all record of P3 from the SPJ table (ignore the parts table).

6. (5 pts.) Write a statement which will double the status of all PARIS suppliers.

8. (10 pts.) Write a statement which will add a new supplier, S12, named MARPLE, status 30, city MARYMEAD in the supplier table.

9. (10 pts.) Write the relational algebra statements necessary to find the names of parts used on either PARIS or LONDON projects.

10. (30 pts.) On this and the following page (where indicated), write SQL, Algebra, and (tuple) calculus statements to find the names of parts used on all LONDON projects (i.e., the names of parts for which the following is true: For any London project there is an spj entry saying that this part is used on that project.

a. Relational Calculus (textbook or lisp form)

b. Relational Algebra (textbook or ALGEBRA interpreter form)

c. SQL