CS145 - Spring 2002
Introduction to Databases
Assignment #7   --   Due Wednesday May 29
- There is no written work on this assignment - we want to
give you as much time as possible to work on your last project part.
Assignment #8, which will not contain any programming work, will
contain exercises and challenge problems for all topics covered since
- Even more so than in Project Part 5, in this part of the
project there is likely to be extremely heavy load on Oracle and
significant system slowdown as the deadline approaches. We strongly
suggest that you start early for your own benefit, and please be aware
that we will not extend the on-time deadline because the system is
In the sixth and last part of the project you are to (finally) create
a fully functioning AuctionBase system accessed by users exclusively
through a Web interface. You may use your Web-database code for Project Part 2 as a starting point for this project
part, or you may start from scratch.
The functionality of your AuctionBase system is quite flexible and
open-ended. However, we do expect all students to implement some
- Ability to manually change the "current time."
- Automatic auction closing. An auction is "open" after its
start time and "closed" when its end time is past or its buy price is
reached for its entire quantity. Your design may be such that an
auction closes implicitly with high enough bids or a time update, or
you may have chosen to represent open/closed status with an explicit
- Ability for new auction users to provide their information to
be entered into the database (name, initial rating if not assigned
automatically, optional location and country), if relevant in your
- Ability to browse auctions of interest based on a variety of
input choices. Possible parameters include open/closed status,
category, date, price, substring match in description, etc. Use your
- Ability to see the winner(s) of a closed auction.
- Ability for auction users to enter bids on open auctions.
- Ability for auction users to add new items up for auction.
- Ability to retrieve auction or bidding history for a given
user, including current auctions or bids.
- Ability to run various statistics over the auctions.
Possibilities include average number of bids per user, highest selling
price over initial bid, average time to reach buy-price, etc. Use
- All constraints and triggers from Project Part 5 should
continue to be active in the "live" AuctionBase system. In
particular, constraint violations due to data entry errors or bad
input values should be managed gracefully: it should be possible for
users to continue interacting with the system after a constraint
violation is detected, and the database should not be corrupt. For
information on constraint error-handling in your Web interface please
see the Constraints
and Triggers help document (under Constraint Violations).
- You will need to automatically generate unique itemIDs for new
auction items. You may do so any way you like.
- Don't forget to think a little bit (not much) about
transactions, particularly if you're using Pro*C. See the section on
in the Oracle 9i SQL help document.
- You don't have to implement user authentication.
For example, it's OK to ask the user to enter his username when bidding,
without asking for a password.
- For Java users: when you generate dynamic HTML
pages from your program, please try to use relative paths for URLs, i.e., do
not hard-code the machine name and port number into your program (as we may
be testing your program on a different machine). If you use relative paths
then the browser will automatically find the right machine and port number.
This does not apply to C/CGI users, who may continue to hard-code the
machine name (cgi-courses) and user name into the program.
- For C/CGI users: if you use the cgiparse routines we provided,
and have called CleanUp() somewhere, please be sure there is no code
that accesses CGI parameters after CleanUp(), since CleanUp() releases
memory pointed to by CGI parameters. To be safe, you can simply
remove CleanUp() altogether.
While the functionality of your AuctionBase system is quite
open-ended, the interface itself is extremely open-ended.
CS145 is not a user interface class and you can certainly get full
credit for a solid system with simple input boxes, menus, and/or radio
buttons, and simple HTML output tables, similar in style to what you
implemented in Project Part 2. (However, as in Project Part 2, you
definitely should not be exposing SQL to the end user.) Of course we
welcome much snazzier interfaces, with the zenith being a near-replica
of eBay itself.
As in Project Parts 1 and 2, before turning in your final project
please ensure that it operates correctly using the Netscape
browser on the Sweet Hall Solaris workstations. If there is a
compelling reason you cannot make your system work in the Solaris
Netscape browser environment (e.g., you really want to exploit certain
features in Internet Explorer), you must get "preapproval" from the
course staff to use a different browser environment. Send an email
message to cs145-staff@cs telling
us precisely what browser environment you wish to use for your
project. The message must be sent by Sunday May 27 so that we have
time to work things out if your browser environment poses a problem
for us. You will receive a reply within 24 hours of your message, and
you do need to receive a positive confirmation message before assuming
that your alternate environment is okay.
When the preapproval process is not followed, projects that have
problems on the Sweet Hall Solaris Netscape browsers may lose points,
possibly all points if we cannot run your project at all.
System testing and runaway queries
We strongly suggest that you debug your queries directly on Oracle
before hooking them into your Web interface. JDBC and Pro*C are not
particularly friendly when it comes to "runaway" queries, so you will
benefit yourself and the rest of the class by using sqlplus
first (through which runaway queries are easy to kill using
Ctrl-C) to ensure that your queries are working properly and
are finishing in a reasonable amount of time. Once you are certain
your queries are working correctly, incorporate them into your Web
Even with prior debugging, it is prudent to set a timeout mechanism
in JDBC for all of your queries. Use setQueryTimeout([time in
seconds]) on each of your statement objects, for example:
Statement stmt = conn.createStatement( );
Normally, CGI will kill runaway processes automatically, although this
timeout behavior is not reliable, and we know of no reliable solution
for C users.
Finally, you should never close an ssh or telnet
session or an xterm window without stopping all queries and
Java servlets first. Otherwise, queries may continue to run on Oracle
long after you've turned in for the night. Specifically:
- Always quit your programs cleanly (including sqlplus),
run stopserver for Java servlets, and type exit or
logout to quit from ssh, telnet, or
- Do not force-close any windows.
- If you have trouble quitting normally, log into the same
machine from another window, use "ps -aef | grep
[username]" to find your process ID(s), and use "kill
[processID]" to kill each process.
- If you don't remember which machine your process(es) may be
running on, quickly log into any leland machine and type
"sweetfinger [username]", which tells you the machines you
were logged into within the last few minutes.
What to submit
As usual, prepare a submission directory containing a text file called
README along with all your code, using the same structure you
used for Project Parts 1 and 2.
C users should submit all source files, your Makefile, and
all relevant .html files. Java users should submit your
entire servletdir directory.
Your README should include at least the following, in this
instructions are as usual, points may be deducted if you do not
follow the submission procedures exactly as specified, and if you
submit more than once, all submissions except your last will be
- The line "I WOULD LIKE TO ENTER THE AUCTIONBASE
CONTEST." if you want your project to be considered for the
contest. Otherwise leave blank.
- The URL for the main page of your system if you are a C user,
otherwise leave blank.
- A short description of all the input parameters that a user can
provide when browsing auctions.
- A short description of the different statistics that can be
- How the user gets to each of the capabilities required by the
assignment, if there's any chance at all it's not obvious from the
- A list of any additional capabilities in your system, and how the
user gets to them if it's not obvious.
We will select a small number of AuctionBase systems as winners of our
first annual (and, for now, experimental) CS145 AuctionBase
Contest. Winners of the contest will:
The (Optional) CS145 AuctionBase Contest
The criteria for selection will be some combination of
beyond-the-basics functionality and a good user interface.
- Demonstrate their projects to the class and the world (via
Stanford Online) as part of the last class meeting on June 5.
- Logistics permitting, have their running systems linked to the
CS145 home page for anyone to try.
- Be invited to enjoy lunch at the Stanford Faculty Club with
Prof. Widom, the TA's, and the other contest winners.
- Receive no extra credit, just the extra recognition.
Important - If you want your project to be considered for the
contest, you must:
- Submit your project no later than one day late, i.e., by Thursday
May 30 before midnight. This deadline is to allow sufficient time for
us to carefully select winners. Please note that you may NOT submit
one version of your project for grading and one for the contest. Your
last submission will be the one used for grading and late penalty
calculations, and it can be considered for the contest only if it is
submitted before Thursday midnight.
- As specified in What to submit, clearly indicate at the
top of your README file your desire to be considered for the