Computer Science 455
Third Hour Exam
Name
_______________________________
Tuesday, April 24
100 pts.
I. Some basics.
a. (10 pts.) Explain what the impedance mismatch problem is, and how PL/SQL uses cursors to get around it.
b. (10 pts.) Outline the steps involved in creating a simple form based on a table. What is the role of the optional button palette?
c. (10 pts.) SQL is in some respects a hybrid language; part relational calculus and part relational algebra. Using the SPJ database, write two queries (your choice), one which uses only relational calculus features, and one which uses a relational algebra feature. Be sure to label which example is which, and to highlight the relational algebra feature in the appropriate example.
d. (10 pts.) When we say that SQL is primarily a relational calculus language, what do we mean? As a part of your answer, provide a distinction between relational calculus and relational algebra statements that would be understandable to a computer science student who has not yet taken CSci 455.
e. (10 pts.) Why do we call a relational database management system relational?
II. (20 pts.) The form on the last page of this exam can be removed for this exercise.
a. (5 pts.) Write the table as an unnormalized relation, identifying repeating fields. What is the key field of this unnormalized relation?
b. (5 pts.) Normalize the relation of part (a) by removing the repeating fields, and give the resulting tables.
c. (10 pts.) Identify partial key and transitive dependencies in the tables of part (b), and arrive at a final relational schema for this form. Do you recognize it from an earlier assignment?
III. The questions following 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)
1. (10 pts.) Using the full relational algebra or the formal relational calculus (your choice, but not SQL)), write a query to find the names of parts used on some London project. You may use either the textbook syntax for the algebra/RelCalc language, or that of the appropriate interpreter.
4. (20 pts.) Using both SQL and (either the full relational algebra or the relational calculus (again your choice)), write queries to find the names of parts used on all London projects (i.e., if there is a project in London, then this part is supplied to it. Please note that this is a forall query). Again, you may use either the textbook syntax for the algebra/RelCalc language, or that of the appropriate interpreter.
SQL
ALGEBRA or RelCalc
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Order Number: |
Date: |
|
|
|
|
|
|
|
|
|
|
|
|
Customer Name: |
|
|
|
|
|
|
Customer Number: |
|
|
|
|
|
|
Customer Address |
|
|
|
|
|
|
Customer Telephone |
|
|
|
|
|
|
|
|
|
|
|
|
|
Item Code |
Description |
Qty ordered |
Unit Cost |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Total Cost: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|