CS145 - Introduction to Databases
Spring 2001, Prof. Widom
Due Wednesday May 30
- 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
- There is one very important Oracle document for this assignment:
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
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.
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.
WITH RECURSIVE Mystery AS
( (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
- 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
- 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.
The directory you submit should contain the following two or three
types of files:
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
- 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.
- 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
- 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!