CS145 Assignment #7

Due Monday, December 1, 1997

Step 7 of Your PDA

(50 pts.) Your PDA assignment for this week is to build a user-friendly interactive application program front end to your PDA using the C or C++ programming language and embedded SQL. There is a Guide to the Oracle Pro*C Embedded SQL System available to give you the basics of this facility.

Your program should consist of a continuous loop in which:

1.: A list of at least five alternative options is offered to the user. (an additional alternative should be quit.)
2.: The user selects an alternative.
3.: The system prompts the user for appropriate input values.
4.: The system accesses the database to perform the appropriate queries and/or modifications.
5.: Data or an appropriate acknowledgment is returned to the user.

You should include both queries and modifications among your options. For example, if your PDA were about bars, beers, and drinkers you might offer options such as

Look up the price of a given beer at a given bar.
Find the bar with the lowest price for a given beer.
Given a drinker, find all the other drinkers that frequent at least one bar in common.
Add a new beer to the Beers relation.
Increase all the prices at a given bar by a given amount.
Quit.

We are not expecting anything fancy in the way of interface. For example, a menu printed via printf is OK. Also, handling of SQL errors can be quite simple. Either write an sqlerror routine that just prints the error message from Oracle or copy the error handler from a sample program (of course you should acknowledge this borrowing just as you would acknowledge any other use of someone else's work in your homework or project).

Hand in your program and a script showing the program running. Each of the options should be exercised at least once in your script.

Problem Set

(15 pts.) Suppose we have the following relations or predicates:
```
     Students(id, name)
     Courses(number, prereq)
     Enrolls(id, number)
```
The interpretation should be obvious: students have ID's (key) and names, courses have numbers (key) and prerequisties, which are themselves course numbers, and enrollments are pairs consisting of a student ID and the number of a course in which that student is enrolled. Write the following queries in Datalog. You may use more than one rule if you wish.

a)

(5 pts.) Find the names of the students enrolled in CS145.

b)

(5 pts.) Find the students who are enrolled in a course and one of its prerequisites at the same time.

c)

(5 pts.) Find the students who are enrolled in only one course.

(8 pts.) Using the same predicates as in Problem (1), write a Datalog program for the following: Find, for each student, the set of courses that the student must have previously taken, based on the courses in which the student is currently enrolled, the prerequisites of those courses, the prerequisites of those, and so on.

(7 pts.) Write the query of Problem (2) as a recursive SQL3 query.

(20 pts.) This question uses the following ODL classes:

     interface Student (extent Students key id) {
         attribute string name;
         attribute int ID;
         relationship Set enrolledIn
             inverse Course::students;
     }

     interface Course (extent Courses key number) {
         attribute string number;
         relationship Set students
             inverse Student::enrolledIn;
         relationship Set prereqs
             inverse neededFor;
         relationship Set neededFor
             inverse prereqs;
     }

Write the following queries in OQL:

a): (5 pts.) Find the names of the students enrolled in CS145.
b): (5 pts.) Find the students who are enrolled in a course and one of its prerequisites at the same time.
c): (5 pts.) Find the students who are enrolled in only one course. (Use aggregation if you wish, but the query can be answered without it.)
d): (5 pts.) Find the prerequisites of the prerequisites of the prerequisites of CS145