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 Assignment #6.
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 slow.

Project Part 6

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.

Functionality

The functionality of your AuctionBase system is quite flexible and open-ended. However, we do expect all students to implement some basic capabilities:

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 data field.
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 schema.
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 imagination.
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 your imagination.

Notes:

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 Transactions 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.

User interface

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.

Browser compatibility

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 interface.

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( );
   stmt.setQueryTimeout(180);
   ...

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 xterm.
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 order:

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 computed.
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 user interface.
A list of any additional capabilities in your system, and how the user gets to them if it's not obvious.

Submission 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 ignored.

The (Optional) CS145 AuctionBase Contest

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:

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.

The criteria for selection will be some combination of beyond-the-basics functionality and a good user interface.

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 contest.