LOGISTICS

• The written exercises on this assignment are exceptionally light - covering only recursion - so that you can devote more time to your PDA Web interface. We'll catch up with written exercises on other recent topics in Assignment #8, which does not have a PDA component.

• On the PDA, a maximum of 10 points will be given for projects meeting the minimum specification for this part. Up to 4 total additional points may be given in special circumstances:

  • To students who had difficulty completing previous project parts but have now caught up and finished the entire project.
  • To students whose project goes well beyond the minimum specification for this part.

  Remember that as we grade this part of the project we will be selecting the winners of the project contest (described in the Course Information document). Although the winners will be selected based on sustained novelty and quality of work throughout the project, certainly doing a whiz-bang job on this last part cannot hurt. Important note: Due to tight time constraints, only projects submitted by the on-time deadline of midnight on Wednesday May 30 will be considered for the project contest.

• There is one very important Oracle document for this assignment:

  • Web-Database Programming: CGI and Java Servlets

  Make sure to use the version of this document linked here and from the course home page - the old version from the course reader and the book Web site is no longer valid. Please review the newest version of this document very carefully before beginning your work.

• Turning in written exercises: The procedure for turning in the written exercises on this assignment is the same as for the previous assignments, with the same deadline and late policy.

• Turning in PDA: Your PDA work will again be submitted electronically. The overall procedure is the same as for the previous electronic submissions (see http://www.stanford.edu/class/cs145/submit-README.txt), and the same deadline and late policy applies. Please be sure to follow the additional submission instructions at the end of Problem 3.

THE ASSIGNMENT

Problem 1

(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

  Student(ID,name,dorm,GPA)  // ID is the only key

   WITH RECURSIVE Mystery AS
      ( (SELECT ID, name FROM Student WHERE GPA > 3.5)
        UNION
        (SELECT Student.ID, Mystery.name
         FROM Student, Mystery
         WHERE Student.name = Mystery.name) )
   SELECT * FROM Mystery

Problem 3 (Personal Database Application, Part 7)

• Similar to Project Part 5, your Web interface should offer the user several (at least five) different types of interactions that involve the database. You may base your Web interface on the same operations you implemented for your application front-end in Project Part 5, or you may use new or additional operations.

• Your interface should support operations that involve data retrieval as well as operations that involve data modification.

• As in Project Part 5, please support (through your Web interface) some number of "interesting" queries or modifications, i.e., operations that require some of the more complex SQL constructs such as subqueries, aggregates, set operators, etc.

• Your Web interface must include online help. In particular, your interface should be usable by someone simply sitting down in front of it with no background knowledge of the domain, application, or supported queries and modifications. We will test it with this expectation.

• The less your Web site looks like it is interacting with an Oracle database, the better. At the very least, the user should be completely shielded from anything resembling SQL.

• Most interactions should involve some sort of input values in addition to the user pressing a Submit button. Whenever possible, input values should be specified using menus, radio buttons, checkboxes, scrollers, etc. Text input boxes may also be appropriate.

• Sophisticated error handling is not necessary, however your Web site definitely should not "lock up" regardless of how the user chooses to interact with it.

• Be creative.

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.

Submission

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.

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