CS145 Assignment #7

Written Homework due Thursday, November 29, 2001

PDA-7 due Thursday Dec. 6, 2001 (along with HW-8)

Finale (7) of Your PDA

Alternatively, you may write in Java and use the JDBC interface between Java code and SQL code. The on-line place to look is the Oracle JDBC Guide.

An option is to build a Web interface, and if you do so, the Web-Interface Guide is what you need to look at.

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, if you do not use a Web form, then 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).

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

However, if you build a Web interface to your database that allows the sorts of options described above, then please submit 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 generator 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. Consider a database for an airline that stores, for each flight, which seats are booked, and the price for each booked seat. The database has the following relational schema:

    
   Routes(flightNum, fromCity, toCity, departure, arrival)
   Flight(flightNum, flightDate, aircraft)
   Bookings(flightNum, flightDate, seat, passenger, price)
   

   For each of the following two transactions, explain the advantages and disadvantages of using each of the four isolation levels in SQL: (1) READ UNCOMMITTED, (2) READ COMMITTED, (3) REPEATABLE READ, and (4) SERIALIZABLE. (Please annotate your answers with the problem number, the query, and the isolation level.) Please indicate which one you would choose for each transaction and why. Be sure to state your assumptions.

   (a)

   Consider a transaction that books the lowest price seat in flight 13 on July 13 for Joe. If there are multiple seats with the same lowest price, use the one with the lowest seat number.

   (b)

   Consider a transaction that prints a report showing for each flight, the percentage of seats that are booked and the total revenue of seats booked.

2. Consider a database for a bank listing customers and accounts. The database has the following relational schema:

    
   Customers(socialSecNum, name, address, city, state, zip, phone)
   Accounts(accountNum, socialSecNum, balance)
   Transactions(accountNum, transactionNum, transactionDate, amount, teller)
   

   For each of the following four transactions, explain the advantages and disadvantages of using each of the four isolation levels in SQL: (1) READ UNCOMMITTED, (2) READ COMMITTED, (3) REPEATABLE READ, and (4) SERIALIZABLE. (Please annotate your answers with the problem number, the query, and the isolation level.) Please indicate which one you would choose for each transaction and why. Be sure to state your assumptions.

   (a)

   Consider a transaction that deposits money into a single account.

   (b)

   Consider a transaction that transfers money from one account to another.

   (c)

   Consider a transaction finds the sum of the amounts for a specific teller on a specific date.

   (d)

   Consider a transaction that prints letters for each customer whose balance in all accounts exceeds $10,000; the purpose of the letter is to announce a new type of account.

3. In the box on page 411 of the text, it says that a trigger runs under the privileges of its creator, and not the privileges of the user whose transaction caused the trigger to be executed. Explain why this choice is wise. That is, what problem(s) do you foresee if triggers ran under the privileges of the triggerer rather than the creator of the trigger.

4. You are the Chief Technology Officer of a company and one of your database administrators leaves the company. She recently set up the privileges for a new application system. Should you REVOKE all of her privileges? Explain your answer.

5. Let us consider the following relations/predicates:

        Courses(number, quarter, instructor)
        Prereqs(course, prerequisite)
        Students(id, name, address)
        Enrolls(studentID, course, quarter, grade)
   

   Note that courses are represented by their unique numbers in relations Prereqs and Enrolls; students are represented by their ID's. Prerequisites are immediate prerequisites only. For example, if CS101 is a prerequisite of CS200, and CS200 is a prerequisite of CS342, then only the pairs (CS200, CS101) and (CS342, CS200) would appear in Prereqs. We refer to CS101 as an indirect prerequisite of CS342. Write the following questions in Datalog:

   a)

   Find the instructors in courses taken by the student(s) named ``Sally.''

   b)

   Find the students (ID's) who enrolled in at least two courses in the quarter ``aut01''.

   c)

   Find the students who enrolled in exactly one course in the quarter ``aut01''.

   d)

   Find the students who enrolled in a course and one of its direct prerequisites in the same quarter.

   e)

   Find the prerequisites, including indirect prerequisites, of CS103.

   f)

   Find the students who enrolled in CS488 but never (i.e., in no previous quarter) enrolled in one or more of its direct or indirect prerequisites.

6. Consider the following datalog program:

        p(X,Y) <- s(Y,X)
        p(X,Y) <- p(Y,X)
        q(X) <- p(X,Y)
        q(X) <- r(Y,X)
        r(X,Y) <- r(Y,X)
        r(X,Y) <- q(X) AND q(Y) AND NOT p(Y,X)
   

   The EDB predicate s has two tuples: (1,2) and (2,3).

   a)

   Draw the dependency graph constructed from this program. Is the program stratified? Justify your answer.

   b)

   Compute the stratified model for this program.

   c)

   Is there another model that is consistent with the program? If yes, then give an example.