In this project, you will design and implement a relational database system to support the operations of an on-line auction house. You will use HTML and Java for the user interface, Sybase for the database server, and Javascript, JDBC and SQL for connectivity between the user interface and database server. The database server is accessible from the PCs in the graduate Transaction Processing labs (Computer Science Building, Room 1239), on which you will be given an NT account and a Sybase account.
Please e-mail the TA gpeng in order to receive your account information.
If you own a PC, you are encouraged to develop as much of the code as possible on your PC, to ease the congestion in the TP lab. For these purposes, you could use, for example, Microsoft Access as the DBMS. Ultimately, though, your code will need to run on the machines in the TP lab, and be able to access the Sybase database server. So please keep this in mind during the development stages.
You are to work in teams of two. Teams of any other size (including one and three) will not be allowed under any circumstances. Pick a partner and also choose a name for your team/company. E-mail the names of your team members and the name of your company to gpeng AT cs DOT sunysb DOT edu as soon as possible. If you are unable to find a teammate, please let me know asap, and I will find one for you.
In a few weeks we hope to offer a tutorial session on Java, Javascript, and JDBC; we will keep you posted on this. Also, the TA is putting together a prototypical database system on which you can base your project. We will give you access to the code, which should help you a lot. In the meanwhile, you should start to become familiar with these languages on your own. Make sure you buy the books that we have recommended - see the course homepage.
It is recommended that you regularly check the course homepage, which can be found at http://www.cs.sunysb.edu/~sas/cse532/spr00/, for information on the term project. This homepage will be updated from time to time with important information on how to proceed with the project.
The basic idea behind your on-line auction house is that it will allow customers to use the web to browse/search the contents of your database (at least that part you want the customer to see) and to hold auctions over the web. In this regard, it is a lot like the on-line auction house ebay.com So visit this site to get ideas as to what your system should look like.
Your database system must be based on the specifications and requirements that follow.
The users of your system will be the customers that buy and sell at your auction house, customer representatives who provide customer-related services, and the house manager. You should assume that the computer knowledge of the users is limited (say, that of a typical AOL subscriber), and thus your system must be easy to access and operate.
The data items required for the auction-house database can be classified into four categories: auctions, items, customers, and employees.
This classification does not imply any particular table arrangement. You are responsible for arranging the data items into tables, determining the relationships among tables and identifying the key attributes. In addition, you should include indices in your tables to speed up query processing. You should base your choice of indices on the type and expected frequency of the queries outlined in Section 3. Finally, you should specify and enforce integrity constraints on the data, including referential integrity constraints.
As I mentioned in class, you will first create an E-R diagram of your auction-house database system before developing your relational model. Details of this assignment will be forthcoming.
This category of data should include the following items:
Each auction is for a certain item in the auction house's database. The Seller establishes an opening data/time and closing date/time for the auction. An auctions may last 3, 5, or 7 days from the time the auction is opened.
Since this is an on-line auction house, the bidding rules governing an auction are a bit different than what you might expect. This is because bidders are obviously not expected to stay connected to the site for the duration of an auction. Instead, they are encouraged to bid the maximum amount they are willing to pay for an item, even on their first bid! Moreover, this amount is kept secret! If necessary, bids will be made on your behalf as other bidders increase the bid price, up to your maximum amount or the necessary preset bid increment to outbid other bidders.
All bidding is done in bid increments. An auction's bid increment is calculated by the system and changes with the current maximum bid. Click here to see how ebay calculates the bid increment, and here to reach ebay's general help menu.
To clarify the bidding process, let's consider an example. Suppose the opening bid on an item is $100 and the increment is $25. The seller stipulates what the opening or minimum bid is to be. Suppose now that Sarah bids $500 on the item and she is the first person to make a bid on the item. Then the current bid will be posted as $100 although the system knows that the high bid so far is $500. Suppose John comes along and bids $250. Then the current bid will now be posted as $275 and Sarah will be credited with this bid.
This is an example of a proxy bid, i.e., a bid made by the system on behalf of a user, and such bids are a key mechanism for making on-line auctions feasible. In this particular case, the system has outbid John by the bid increment on behalf of Sarah. John decides that enough is enough and bids $600. John has succeeded in outbidding Sarah, the current bid becomes $525, and the current maximum bid belongs to John at $600. As always, John's maximum bid is kept secret.
Auctions may also be run with reserve prices. The reserve price is the lowest price at which a seller is willing to sell an item. The reserve price is not disclosed to bidders. A seller might specify a reserve price if she is unsure of the real value of the item and would like to be able to refuse to sell the item if the market value is below a certain price. During an auction, an annotation should be displayed in the item information screen if the seller has specified a reserve price.
The seller specifies the reserve price when she lists an item. This price should be above the minimum bid price. The auction begins at the minimum bid price. When a bidder's maximum bid is equal to or greater than the reserve price, the item's current price will indicate that the reserve price has been met.
When an auction finishes, the customer who placed the current high bid becomes the buyer. You can optionally implement a scheme where at the conclusion of an auction, the buyer and seller will be notified by e-mail. The auction house receives a fixed-percentage commission of 10% on every completed auction. A customer representative from the auction house oversees each auction.
To find out more about how the bidding works in an on-line auction house, you might want to consult the help menu at ebay.com.
This category of data should include the following items:
An item is the entity that is purchased or sold in an auction. The types of items in your database can be whatever you like. Standard ones are jewelry, silverware, quilts, books, CDs, and sport memorabilia. You should keep track of the number of copies of the item sold and currently up for auction.
The items required for this category include:
A given customer may partake in any number of auctions, either as a buyer or as a seller. The customer's rating should reflect how trustworthy and reliable the customer is, either as a buyer (pays the seller what he said he would) or as a seller (sends the buyer what he said he would).
This category of data should include the following:
A database transaction can be viewed as a small program (written in the DML) that either updates or queries the database. Transactions that change the contents of the database must do so in a consistent manner. Moreover, transactions should not interfere with one another when running concurrently.
What follows is a breakdown of the user-level transactions that your database system should support.
The manager should be able to:
Customer Representatives should be able to:
The customer should be able to easily browse the auction house over the web and partake in auctions (as the buyer or seller). While the customer will not be permitted to access the database directly, the customer should be able to retrieve the following information.
Your database system should provide controlled access to the data by distinguishing between the different types of users: manager, customer representatives, and customers.
HTML and its successors provide facilities for creating pop-up and pull-down menus, value lists, input/output forms, labels and customized reports. You should make use of all of these capabilities, and in the process come up with a system that caters to users with only limited computer knowledge. The information you provide to customers should look professional and inviting.
You will be required to supplement your completed database implementation with a design document that contains information concerning your design criteria and decisions. The following is a list of some of the information you should include:
You will also be required to submit a Users Guide that carefully explains how to use all aspects of the system. It should be understandable by non-computer experts. Be sure that the user interface (screen design, menu structure, etc.) is clearly explained.
You will be given assignments to produce an E-R and relational model of your system. The due dates for these assignments will be announced shortly.
All documentation should be done in HTML and be available on-line. Do not hand-in any hardcopies of anything during this course unless I explicitly ask you to.
After you have submitted the final system, you will be asked to present a short (15 minutes) demo to myself or the TA. Alternatively, we may just attempt to use your system on-line to see if your system is working properly. This will most likely happen during finals week.