Page 1 (of 7 pages)


	Page 1











Computer Science 455


Third Hour Exam




Name _______________________________











Thursday, Apr. 18
100 pts.


Important note:  The database used for this exam is a modification 
of the Hoh River College database.  A copy of the tables together 
with some notes is given on the final page of this exam, which may 
be separated from the rest of the exam and used for reference.  It is 
not necessary to turn in this final page with the exam (but please 
make sure that you have written nothing important on it).


I.	Oracle Tools

In questions on Oracle tools, it will not be necessary to say what 
individual keys do, or to specify the precise sequence of keystrokes 
to do something.  Be detailed enough, however, to describe things 
well enough so that someone reading your responses to questions 
would be able to perform tasks.


1.	SQL*Report (20 pts.)	

Explain how a master-detail report is created using as an example a 
class listing report which lists, for each class, the student names, 
student numbers, and grades of students in the class.  In particular, 
give the queries that would be needed, say where they are specified, 
and say how they are joined.  In order to get the student names as 
well as the student numbers, we need to construct a view joining 
information in the STUDENT table with information in the ENROLL 
table.  Say how that view can be constructed, and use it in the 
report.  Without giving a detailed process for doing so, say also how 
the average gpa for each class could be included on the report.



2.	SQL*Forms and PL/SQL (20 pts.)

a.	Consider a data entry form for the STUDENT table.  Briefly 
outline the steps that you would take to create the data entry 
form, and how you would provide the form's user with an 
acceptable list of values for the ADVISOR field.













b.	Write a POST-CHANGE trigger for the SNO field of a form built 
on the STUDENT table (with default field names) which will 
read the student's name and advisor number into the 
appropriate fields in the form.  Assume that the block name is 
main.  If no record is found with the SNO value, print a 
message and cause the form to fail.  

3.	Embedded C (20 pts.)


a.	What is the impedance mismatch problem?






b.	Using either PL/SQL or embedded SQL in C or Pascal (but saying 
which approach you are taking), write the code necessary to 
define a cursor containing all the STUDENT information for 
students with MAJOR="CSCI", and print out each student's 
name.  If you are using PL/SQL, assume the existence of a 
PRINTOUT routine which can be used to print out the student's 
name.  If you are using embedded SQL, assume a Boolean 
function NO_DATA_FOUND which returns TRUE when there is no 
more data in the cursor to fetch.




II.	Advanced SQL (10 pts. each)

Note:  I have grouped questions by topics.  With the exception of 
question number 4 (for which I want you to use a nested 
query), please feel free to use any valid SQL statement that 
works.  The headings (again, except for question 4) are more in 
the way of hints than requirements.

1.	Tuple variables (could also be done using nested queries, I 
think)

	Find the names of students who are taking a class with student 
S12.










2.	Relational Algebra operations

	Find the names of students who are taking both the database 
class (CNAME="DBMS") and the MIS class (CNAME="MIS").

(Part II continued)

3.	Built-in functions

	Write a query which will give us the course number, course 
name, and average grade in each class offered which has an 
average grade in excess of 3.33.













4.	Nested queries

	Write a nested query (required) to find the names of students 
taking a database class (CNAME = "DBMS").







Hoh River College database schema:

	This page may be separated from the rest of the exam and used 
for reference - it contains no exam questions.

	STUDENT(SNO, SNAME, ADVISOR)
	FACULTY(FNO, FNAME, DEPT)
	CLASS(CNO, CNAME)
	ENROLL(SNO, CNO, GRADE)

Notes:

	
1.	SNO is the key for the STUDENT table
2.	ADVISOR is the FNO of the student's advisor.
3.	FNO is the key for the FACULTY table
4.	CNO is the key for the CLASS table
5.	It may be possible that two classes have the same name 
(as would be the case, for example, if there were two 
sections of a single course).
6.	GRADE is stored numerically.  For example, a grade of 
B+ is stored as 3.33.