312572 SQL and Perl Cart

Project Requirements (also attached) Create a multi-user application to buy and sell items in a real-time auction. The application consists of: 1. MySQL database – including tables and “starter set” data [Provided ] 2. Perl DBI program that perform the “Auction Bid Management” functions 3. Perl DBI program that provides the user interface and performs all the required user functions. The application must also perform basic cleanup and error handling. Note: The completed project must resemble a reasonable implementation of the project requirements. Every requirement has not been listed. For example, it is common sense that an item can only be sold once, therefore there is no explicit requirement stating this rule. Business Rules  Valid user types (USR_TYP) are guests, members and DBOs (DB owners)  Login rules by user type  Users without a user ID can access the system as a guest user  Multiple guests are allowed in the system at the same time  Members and DBOs must exist in the USER table  Members and DBOs can only be logged in once  Functions available by user type  Guests can only perform search functions  Members can perform search functions  Guests and DBOs cannot buy or sell items  Only members can “watch” items  Only members can buy or sell items  DBOs can begin and end auctions  DBOs can generate reports  Auction watching process  Members can maintain a watchlist of items. This enables members to “watch” items they are interested in but not actively bidding on.  Auction listing process (all bid prices in whole dollars only)  Items may have a minimum starting bid price  Items may have a “buy-it-now” bid price  Items must have an auction start date & time  No bids allowed for items with a future auction start date & time  Items must have either an auction end date & time OR an auction length. For items with an auction length specified, the auction end date & time must be computed.  No bids allowed for items after the auction end date & time  Members should be allowed to add new items in 2 ways: 1. One item at a time through a user-interface AND 2. Multiple items at once through a bulk-load from a file interface (must use the DBD-CSV method) 3. Auction bidding process (all bid prices in whole dollars only)  Each item has its own auction based on its auction start date & time and auction end date & time  The buyer and seller cannot be the same member in a transaction  Buyers bid on items by  Selecting a “buy-it-now” option (if it is available), OR  Specifying a maximum bid price  All items have a bid price increment of $1  For items with an active “buy-it-now” option:  The first buyer to select the “buy-it-now” option, wins the item at the “buy-it-now” price  The first accepted bid disables the “buy-it-now” option from this item  If the auction ends with the “buy-it-now” option still active, there is no winner for this item  For items whose minimum starting bid price has not been met:  If a buyer's maximum bid price is less than the item's minimum starting bid price, the bid is rejected  If a buyer's maximum bid price is greater than or equal to the item's minimum starting bid price, the buyer becomes the current high bidder and the current bid price is set to the minimum starting bid price  If the auction ends with the minimum starting bid price still not met, there is no winner for this item  For items whose minimum starting bid price has been met:  If a buyer's maximum bid price is less than or equal to the item's current bid price, the bid is rejected  If a buyer's maximum bid price is greater than the item's current bid price, the current bid price is only incremented by $1. If there are multiple buyers with maximum bid prices greater than the item's current bid price, the buyer with the highest maximum bid price becomes the current high bidder at the second highest bidder's maximum bid price + $1.  When the auction ends, the current high bidder wins the item  For items that are unsold after its auction ends, update its status (set itm_stat = 'E') Application Requirements (User Interface) 1. User login – prompt user for user ID 2. Display user type-specific menu  For guests  Display item search – prompt user for item category and display all current items [itm_stat = ‘C']  For members  Display item search – prompt user for item category and display all current items [itm_stat = ‘C']  Display items member is currently bidding on, including current bid status and current high bid  Display member's item watchlist  For all items, display item id, item name, item description, auction start and end date/time  For current items, also display the current bid price  For sold items, also display the final sales price  For future or unsold items, also display item status  Allow member to place a bid  Display add items menu  One at a time  Multiple items at once  For DBOs  Start auction for an item  End auction for an item  Generate sales report (for all sales)  Generate item report (for all items)  Generate bid history for an item – prompt use for item id 3. User logout – current bids by user are NOT affected Application Requirements (Auction Bid Controller) 4. Background process that “wakes up” every 30 seconds and evaluates every unprocessed bid [bid_stat = ‘U'] according to the specified business rules.