Page 1


	Page 1











Computer Science 455


Third Hour Exam




Name _______________________________


















Thursday, April 27
100 pts.


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

	Answer the following queries in the language (SQL, relational 
algebra, relational calculus) asked for. 

a.	(20 pts.)	Find the names of suppliers who supply RED parts 
to LONDON projects:

	Relational algebra











	Relational calculus

(problem 1 continued)

b.	(20 pts.)	Find the names of suppliers who supply no parts to 
LONDON projects

	SQL
















	(Relational Algebra or Relational Calculus (your choice, but say 
which one you are using)).


(problem 1 continued)

c.	(5 pts.)	Find the average STATUS of LONDON suppliers.













d.	(10 pts.)	Retrieve the list of supplier cities (i.e., cities in 
the supplier table) together with the average status of 
suppliers in those cities, but only retrieve those cities for 
which the average status is greater than 15.


d.	(20 pts.)	Find the names of projects that use all of the parts 
stored in PARIS (i.e., p.city = PARIS)

	SQL
















	(Algebra or Calculus (your choice, but say which one you are 
using)).


2.	(15 pts.)	In a form based on the supplier table S(SNO, SNAME, 
STATUS, CITY) we have a block called SUPPLIERS with fields 
SNO, SNAME, STATUS, and CITY.  Write the code for a 
POSTCHANGE trigger on the SNO field which will take the SNO 
value typed into that field and use that to find the 
corresponding SNAME, STATUS, and CITY values from the 
supplier table, putting those values into the corresponding 
fields of the form.  If the supplier number is not found, your 
code should produce a message and raise the appropriate error 
condition.

3.	(10 pts.)	What is the impedance mismatch problem in 
embedded SQL, and how is it solved?