Computer Science 455
Third Hour Exam
Name ______________________
The supplier-parts-projects (SPJ) database:
s(sno, sname, status, city)
p(pno, pname, color, weight, city)
j(jno, jname, city)
spj(sno, pno, jno, qty)
90 pts (will be normalized to 100 pts. in the gradebook)
I. Bottom-up design:
a. (15 pts.) List and briefly describe the steps taken for each form or exhibit in a bottom-up design..
b. (15 pts.) Consider the following form, an order-invoice form for a company selling items through a catalog. Construct a relational schema from the information on the form (remembering that there is an underlying catalog) and an entity-relationship diagram. You do not need to go through the systematic steps you described in part (a). Simply identify reasonable entities, attributes, and relations (writing them into a relational schema and an entity-relationship diagram).
|
|
|
|
|
|
|
|
|
YoYoDyne
Ceramics Supply |
|
|
||
|
|
|
|
|
|
|
|
Customer
Number: |
C123 |
|
Order No: |
O53426 |
|
|
Customer
Name |
Bob
Matthews |
Date: |
|
||
|
Customer
Telephone number |
some
number |
|
|||
|
Shipping
Address (treat as a single field) |
some
address |
||||
|
|
|
|
|
|
|
|
Item No: |
Item
Description |
|
Unit cost |
Amt |
Cost |
|
C142 |
Wood rib |
|
$3.00 |
5 |
$15.00 |
|
C859 |
50 lb
DSRM |
|
$14.00 |
4 |
$56.00 |
|
C112 |
Kiln
Blanket |
|
$50.00 |
1 |
$50.00 |
|
|
|
|
|
|
|
|
|
|
|
|
Total: |
$121.00 |
|
|
|
|
|
|
|
II. Some more SQL (all queries are with respect to the SPJ database on the front cover)
a. (10 pts.) Find the part number and part names of
parts that are not used on any
b. (10 pts.) Write a query to find out how many projects
are in
c. (15 pts.) Construct a view (from the spj table) consisting of sno and pno fields and the total number of the part supplied by the supplier (over all projects). The relational schema for the view is SP(sno, pno, qty).
d. (15 pts.) Find the names of parts which are used on every project (i.e., parts for which, if jno is a project, then this part is used on that project). This is a ‘forall’ question.
.
III. Miscellaneous questions:
a. (5 pts.) What is a trigger on a form (for example, associated with a button)?
b. (5 pts.) Java deals with one record at a time, and a relational database works with a table at a time. How does Java deal with this (please provide a little detail).