Computer Science 455
Second Hour Exam
Name __________________________
Friday, March 27, 1998
90 pts.
1. (15 pts.) From the following, construct an entity-relationship diagram.
A customer (for whom we store a customer number, name, and address) has several orders with our company. For each order, we store an order number, a date on which the order was made, and, for each order, a list of items ordered and the number of each item ordered. Each item comes from our catalog, and each catalog entry has a catalog number, a description, and a unit price.
2. (15 pts.) From the following entity-relationship diagram, construct a relational schema. Identify primary and foreign keys.
3. (20 pts.) Given the following form, identify entities and relationships, and construct a basic entity-relationship diagram.
|
YOYODYNE INC. |
|||||
|
CUSTOMER INVOICE |
|||||
|
Customer No: |
Order No. |
||||
|
Customer Name: |
Date |
||||
|
Customer Address: |
|||||
|
Item Number |
Description |
Unit Cost |
Amt |
Cost |
|
|
12345 |
Widget |
$27.42 |
$2.00 |
$54.84 |
|
|
34721 |
Gizmo |
$15.37 |
$3.00 |
$46.11 |
|
|
Total: |
$100.95 |
||||
(workspace for problem 3)
4. (25 pts.) Suppose that in a relation
STUDENT(SNO, SNAME, CLASS, TNO, TNAME, GRADE)
we have the following basic functional dependencies:
SNO -> SNAME
CLASS -> TNO
TNO -> TNAME
(SNO, CLASS) -> GRADE
a. (5 pts.) What is the key of the relation?
b. (10 pts.) Give a definition of second normal form. Find a violation of second normal form in the relation STUDENT, identify it, and decompose STUDENT into two relations resolving only this violation of second normal form.
c. (10 pts.) Give a definition of third normal form. Find a violation of third normal form in the relations you created above, identify it, and decompose the relation with the problem into two relations resolving only this violation of third normal form.
5. (15 pts.) Suppose that you have a table CUST(CNO, CNAME) and a table ORDERS(CNO, ORDNO, DATE, TOTAL_AMOUNT). Outline the steps you would take in creating a master-detail form with CUST in the master block and ORDERS in the detail block.