In this lab, you will lay the database-layer groundwork for a textbook auction site. Because the scope of work is broad and flexible, even people who have read this assignment will not understand what you have done without your explaining it: you will need to produce a full report carefully explaining your overall design and the specific artifacts (such as tables, views, and stored procedures) you have created. The target audience for your report will be programmers who are continuing work on the auction site. They will be layering a user interface and additional application logic onto the database features you are providing, so they will need to understand how to use those components. Moreover, in the course of this further development, they will likely discover ways in which your database-layer work needs to be revised, so your explanation should be adequate to allow them to do so.
Users of this textbook auction site can either offer to sell a book or attempt to buy a book. In either case, the user specifies a particular book and a limit price. In the case of an offer to sell, the limit price is the minimum price at which the user would be willing to sell the book. In the case of an attempt to buy, the limit price is the maximum price the user would be willing to pay for the book.
An attempt to buy is known as a "bid" and the corresponding limit price is a "bid price." An offer to sell is simply known as an "offer," and the limit price can be called an "offer price," though "ask price" is more common. The word "order" refers to either a bid or an offer. Often the word "price" is omitted, so that the two kinds of limit price are referred to as "bid" and "ask".
The auction site can match a bid with an offer if there is any overlap between the buyer's and seller's acceptable price ranges, that is, if the bid price is greater than or equal to the ask price. The particular auction process we will use, Continuous Double Auction (CDA), performs this matching as follows. The site keeps track of all orders, including whether they are matched. When a new order is placed, the auction site determines whether it can be matched with any previous unmatched order. If so, the match takes place immediately. But if not, the new order is added to the collection of unmatched orders that will be searched in the future, when orders of the opposite kind are placed.
When a match is made, the auction system determines the sale price by using the limit price from the earlier order. For example, suppose a seller has listed a book with an ask of $50, indicating that they would be willing to sell it for any price of $50 or more. If a buyer now enters a bid of $100, the two will be matched and the sale price will be set at $50; the buyer is not penalized for bidding more than was needed to make the match. Conversely, suppose the system has an unmatched bid on record of $100. If a seller comes along with an ask of $50, the system will match them and set the sale price as $100; the seller is not penalized for asking less.
If a new order can be matched with more than one previous unmatched order, then a rule is needed for how to select the specific match. The first criterion used is to pick whichever match is most favorable to the newly received order. For example, suppose a bid is received for $100. There are two existing offers for the book, with asks of $80 and $90. The bidder would be willing to pay either of those asking prices (in fact, would be willing to pay even more), but would prefer to pay the lesser amount. Therefore, the bid is matched to the $80 offer. Similarly, if there are two outstanding bids of $40 and $50, a new offer with an ask of $30 will be matched with the $50 bid so as to give the seller the best possible price. If this first criterion is not enough to select a single matching order, because multiple unmatched orders share the best price, then the oldest of the best-price orders is matched.
Once a match is made, the auction system's involvement in the transaction is over; the seller sells the book directly to the buyer for the sale price the auction system established. In particular, the auction system does not receive any money from the transaction, being supported through some other revenue stream.
Your database should store information about books, users, and orders. Because the auction site is intended specifically for textbooks, it should store not only the basic bibliographic information about the books, but also the courses for which they are used. (This would support such features as searching for books given only the course information.) For each order, the database should store the user who placed the order, the book at issue, and the limit price. For each matched pair of orders, the database should store the sale price.
Your first job is to decide in greater detail what information your database will store and draw a corresponding E/R diagram.
Your second job is to translate your E/R diagram into an Oracle database schema, that is, into the appropriate collection of CREATE TABLE statements. Be sure to include all appropriate constraints. In order that your report can include explanatory examples, you may want to insert some sample data into the tables.
Your third job is to write a collection of four stored procedures in PL/SQL that the auction application could use to handle the basic processing of orders. (If you want to write additional helper procedures, that is fine.) The four procedures are as follows:
The BID procedure submits a bid. It has input parameters to specify the user who is is bidding, the book they are bidding for, and the bid price. It also has three output parameters. The first output parameter gets set to a new identifier assigned for the bid, which can be used later to retrieve information about it or to rescind it. If the bid was immediately matched to a pre-existing offer, then the second and third output parameters are set to the identifier of the offer and to the sale price. If the bid was simply filed away for later matching, then the second and third output parameters are set to NULL.
The OFFER procedure is analogous to the BID procedure but submits an offer rather than a bid.
The RESCIND_BID procedure attempts to withdraw a bid, which succeeds if the bid has not already been matched to an offer. The procedure has an input parameter for the bid identifier. It also has two output parameters. If the bid is successfully rescinded, these are both set to NULL. But if the bid cannot be rescinded, because it has been matched to an offer, then the output parameters are set to the matching offer's identifier and to the sale price.
The RESCIND_OFFER procedure is analogous to the RESCIND_BID procedure.
Your final job is to create a view that provides overall information about each book. Each row should include information about a book and a course that uses the book. However, it should also include the following information about the book's auction status: number of unmatched bids, number of unmatched offers, highest unmatched bid (or NULL), lowest unmatched ask (or NULL). That way, the auction program will be able to get most of the information it needs just by selecting appropriate rows from this view, without needing the sort of complex query that might go beyond the abilities of an application programmer.