CS145 Assignment #7

Due Wednesday, December 6, 2000

Step 7 of Your PDA

(60 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 Oracle Pro*C available to give you the basics of this facility. Alternatively, you may write in Java and use the JDBC interface between Java code and SQL code. This material, which has not been covered in class, can be found in the Oracle JDBC Guide.

Your program should consist of a continuous loop in which:

A list of at least five alternative options is offered to the user. (an additional alternative should be quit.)
The user selects an alternative.
The system prompts the user for appropriate input values.
The system accesses the database to perform the appropriate queries and/or modifications.
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
  1. Look up the price of a given beer at a given bar.
  2. Find the bar with the lowest price for a given beer.
  3. Given a drinker, find all the other drinkers that frequent at least one bar in common.
  4. Add a new beer to the Beers relation.
  5. Increase all the prices at a given bar by a given amount.
  6. 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.

Optional: If you are familiar with building Web interfaces, or you are willing to read and master the Oracle Web Guide, you may build a Web interface to your database that allows the sorts of options described above. In this case, simply hand in your code, the URL and some sample data values that will give nontrivial responses to your queries (because otherwise we may not be able to guess that ``xyz'' is the name of a bar generated by your random-data genertor from PDA-3). Your project should be kept on some server that is normally available, such as with your Stanford home page if you have one, since a TA will try out your project at some time we cannot predict.

Problem Set

  1. (5 pts.) Using the running example of books:

    Write a recursive Datalog program to find all the books (including year) that are referenced by First Course in Database Systems, 1997, books that are referenced by those books, books that are referenced by books that are referenced by those books, and so on.

  2. (15 pts.) Design an ODL schema, including extents, keys, and suitable data types, for the books schema last mentioned in problem (1). Hint: As with E/R designs, it helps to think first about which concepts deserve to be classes, and which can be attributes of other classes.

  3. (20 pts.) Below is an ODL schema about students, the courses they are taking and TA-ing, and the prerequisites for courses. The meaning of attributes and relationships should be transparent from their names.

              INTERFACE Student (EXTENT Students KEY id) {
                  ATTRIBUTE string name;
                  ATTRIBUTE int ID;
                  RELATIONSHIP Set<Course> enrolledIn
                      INVERSE Course::students;
                  RELATIONSHIP Course TAing
                      INVERSE Course::TAsOf
              INTERFACE Course (EXTENT Courses KEY number) {
                  ATTRIBUTE string number;
                  RELATIONSHIP Set<Course> prereq INVERSE prereqFor;
                  RELATIONSHIP Set<Course> prereqFor INVERSE prereq;
                  RELATIONSHIP Set<Student> students
                      INVERSE Student::enrolledIn;
                  RELATIONSHIP Set<Student> TAsOf
                      INVERSE Student::TAing;

    Write OQL queries for each of the following:

    Find the students (objects) who are enrolled in a prerequisite of CS145.

    Find the numbers of the courses being TA-ed by students who are enrolled in courses for which CS145 is a prerequisite.

    Find how many students are enrolled in CS145.

    Find, for each course, the average number of courses enrolled in by all the students in that course. For example, if CS145 has three students, enrolled in 1, 4, and 5 courses, respectively (including CS145), then the average for CS145 is 3.33. Note that we do not care if two or more of these three students are enrolled in the same course.