CS145 - Introduction to Databases
Spring 2001, Prof. Widom

Assignment #7
Due Wednesday May 30



Problem 1

Recall from Problem 3 in Assignment #3 and Problem 3 in Assignment #4 the relation Flight(city1,city2,cost,airline), containing nonstop flights from one city to another. In those problems we were interested in finding the cheapest cost of flying between each pair of cities, regardless of the number of stops. In Assignment #3 we learned that it is not possible to write this query using a single statement of SQL2, and in Assignment #4 we learned that we can compute the answer using SQL2 embedded in a programming language.

(a) Write the query in one statement of SQL3, using the recursive WITH construct.

(b) Can your query for part (a) return tuples where city1 and city2 are the same city? If so, what is the cheapest cost of traveling from a city to itself? Modify your solution to part (a) so that no such tuples appear in the query result.

Problem 2

This problem uses relation Student:
  Student(ID,name,dorm,GPA)  // ID is the only key
Can you write a SQL query that is equivalent to the following one but does not use recursion? If so, give the query and state in English what it computes. If not, explain why not.
      ( (SELECT ID, name FROM Student WHERE GPA > 3.5)
        (SELECT Student.ID, Mystery.name
         FROM Student, Mystery
         WHERE Student.name = Mystery.name) )
   SELECT * FROM Mystery

Problem 3 (Personal Database Application, Part 7)

Your final project part is straightforward: build a user-friendly Web interface to your PDA. As we discussed at the beginning of the course, a huge number of Web sites are based largely on a relational database system. The HTML pages a user browses are generated from the database, and user actions and inputs result in behind-the-scenes SQL queries and updates. Although the database-backed site you will create is not likely to be as snazzy as a typical Web shopping or auction site, the basic idea is the same. Here are some general ground rules:

Your Web front-end will interact with your Oracle database using CGI scripts and Pro*C, or using Java Servlets and JDBC. Both modes are described in the document Web-Database Programming. If you used Pro*C for Project Part 5 then you may want to choose CGI scripts for this part, and if you used JDBC for Part 5 then you may want to choose Servlets for this part. However, you are free to switch languages at this point if you like. You are also free to use other languages to implement your Web interface, such as Perl or PHP (both of which are running on the CGI machine), but you should be aware that currently these languages are not supported by the course staff and you are responsible for any problems incurred along the way.


The directory you submit should contain the following two or three types of files:
  1. A single file called README, specifying which language you used to program your Web interface, specifying precisely the names and contents of all other files submitted, and including any other special information about your PDA and its Web interface that will be useful for the grader. Note that you should not include any kind of "user help" information here - all of that should be included as part of the interface itself.

  2. One or more files containing all code used to implement your Web interface. To receive full credit your code must be documented enough that the grader can read and understand the overall structure and algorithms.

  3. In the case of an interface programmed using CGI, a single file called URL containing the URL for your interface.
We will be running everyone's Web interface in order to grade this part of the PDA. You will not receive credit if the code you submit does not run, or if the URL you submit is not valid. Also, if you decide to require a username and/or password to access your database through the Web, then you must either disable this feature in your submitted version or provide a working username and password in your README file.

Please take care to perform a clean submission - it's worth the effort for everyone involved!