Tuesday, April 30
100 pts.
I. Design (20 pts.)
Consider the form on the last page of this exam (this last page may be torn off and need not be turned in with the rest of the exam). Follow the first few steps in generating relations from the form as indicated below. Points will be deducted for doing too much too soon in the process.
a. Write down the fields in the form as an unnormalized table. Identify functional dependencies and clearly indicate repeating fields. Assume that Call Number determines title and author, and that patron ID determines Patron Name (which does not appear to be divided into last name / first name format).
b. Excise the repeating field and write as a series of tables. in first normal form, retaining all of the information in the original form. Do not worry about compound fields.
(Problem 1 continued)
c. Excise 2NF problems and write as a final set of tables in second normal form, retaining all of the information in the original form.
2. Tables for the SPJ and Hoh River databases can also be found on the last page of this exam. With respect to the Hoh River database, write SQL queries to solve the following problems:
a. (15 pts.) For each class, get the course number, course name, and average gpa (assume that the grade field in enroll is a numeric grade, with 3.0 for 'B', etc.), but do this only for classes for which the average grade is greater than 3.0.
b. (10 pts.) Find the names of students who are taking a class from their advisor.
c. (10 pts.) Find the names of students who are taking no class from their advisor.
d. (10 pts.) Find the names of students who are enrolled in all of the courses being taught by the faculty member with fno = 'F12'.
3. (10 pts. each) Do each of the following in SQL and in the relational algebra:
a. Find the names of parts used on some London project.
SQL
Algebra
b. Find the names of parts used on all London projects
SQL
Algebra
4. (15 pts.) Write a PL/SQL block of code which will print the names of suppliers of red parts. Please recall that the command
DBMS_OUTPUT.PUT_LINE(stuff to print)
can be used to print a line (this was similar to one of our demos). You will want to use a cursor.
This is the form for problem #1. This page may be torn off and need not be turned in with the rest of the exam.
|
|
|
|
|
|
|
|
Title: |
A
Potter's Book |
|
|
|
|
Author: |
Bernard
Leach |
|
|
|
|
Call No: |
TP807/L4/1976 |
|
|
|
|
|
|
|
|
|
|
Patron ID |
Patron
Name |
Due |
|
|
|
|
|
|
|
|
|
3741 |
Poirot |
4/1/1978 |
|
|
|
8745 |
Maigret |
6/3/1978 |
|
|
|
7736 |
Fansler |
10/8/1984 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SPJ database
s(sno, sname, status, city)
p(pno, pname, color, weight, city)
j(jno, jname, city)
spj(sno, pno, jno, qty)
Hoh River database (simplified form)
student(sno, sname, major, advisor)
(recall that advisor is the fno value of the student's
advisor)
faculty(fno, fname, dept)
class(cno, cname)
teaches(fno, cno)
enroll(sno, cno, grade)