CS145 Assignment #7

Due Wednesday, December 2, 1998

Step 7 of Your PDA

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.

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.

Problem Set

1. (12 pts.) Suppose we have the following relations or predicates:

         Employee(id, name, salary)
         Manages(emp_id, mgr_id)
         Department(id, deptNo)
   

   The interpretations should be obvious. Employees have ID's (key), name and salary. Manages has a listing of the immediate manager for each employee (emp_id is the key). Department has a listing of id (key) and the corresponding deptNo. Each employee has one direct manager. In addition, all the mangers of an employee's manager also serve as a manger for that employee. Write the following queries in Datalog. You should use only safe rules, but you may wish to use several IDB predicates corresponding to subexpressions of complicated expressions.

   a)

   (4 pts.) Find the ID's of all employees who work for department 10.

   b)

   (4 pts.) Find the names and the salaries of all employees who make more money than their immediate managers.

   c)

   (4 pts.) Find the name and the ID of the CEO of the company (the only person who doesn't have a manager).

2. (6 pts.) Using the same predicates as in Problem (1), write a Datalog program to produce triples consisting of an employee ID, the name of one of the managers of that employee, and the manager's ID. Remember that managers of managers are managers, and so on.
3. (6 pts.) Write the query of Problem (2) as a recursive SQL3 query.
4. (16 pts.) This question uses the following ODL classes:

        interface Student (extent Students key id) {
            attribute string name;
            attribute int ID;
            relationship Set<Course> enrolledIn
                inverse Course::students;
            relationship Set<Course> CoursesTAing
                inverse Course::TAsOfCourse;
        }
   
        interface Course (extent Courses key number) {
            attribute string number;
            relationship Set<Student> students
                inverse Student::enrolledIn;
            relationship Set<Student> TAsOfCourse
                inverse Student::CoursesTAing;
        }
   

   The interpretation should be obvious: students have ID's (key) and names, courses have numbers (key) and TAs, who are themselves students. Students are enrolled in certain courses. Write the following queries in OQL:

   a)

   (4 pts.) Find the names of all the TAs of CS145.

   b)

   (4 pts.) Find all the students who are enrolled in a course and also TAing that course.

   c)

   (4 pts.) Find all the TAs who are enrolled in more than 5 courses.

   d)

   (4 pts.) Find all the TAs of the courses that are TAed by the TAs of CS145.