DH Auctioneers South Australians have come to know DH Auctioneers as THE company to go to for auctioning off antiques, unique pieces, and hard to find collectables. The knowledge, expertise, and service of the DH team provided to its users has seen business flourish in recent years. For this assignment you are to work individually to design a new database to store all information about auctions, sellers, and lotsders to help the company move forward. You will need to develop a suitable conceptual design using UML notation and demonstrate the implementation by writing a series of table creation statements. Auction Explanation There are several types of auctions held by DH Auctioneers and more than one can run on a given date. Estate auctions are held almost weekly whereas specialty/online auctions are less frequent (it takes time to build up the inventory). Each auction day can have one ore more auctions. As an example, weekly Estate auctions are generally broken into two auction catalogues (Furniture and Gallery) to cater for different sized items and interests. Weekly Estate auctions are held on Mondays at 9am except on public holidays where they are delayed to the following business day. Specialty auctions may be held any day of the week (except public holidays) and generally start after 5pm.

All auction catalogues have a name (Furniture, Gallery, or a such as Movie Memorabilia, Mid Century Modern, Model Trains and Lead Soldiers etc). Auction catalogues also have a description and showcase photo which is not necessarily a photo of a single lot. Each lot within a given auction catalogue has a brief descriptive title, is generally accompanied by 1 or more photos (including a thumbnail photo), a guid price range as well as a description. Most lots will have only one photo unless there is some specialty interest.

Auction Bidders Bidders can register to participate in auctions either online or "on-floor" on the day of the auction. Online registrations can be completed before the auction date (or even during the auction with a restriction on the maximum sale amount of $500 across all bids). When registering online, bidders receive a unique bidder number that starts from 1000. By contrast, bidders who register "on-floor" receive a card number between 001 and 999 which can change each auction. Regardless of how bidders register, the auction house collects details such as name, address (including suburb, postcode), phone numbers (usually mobile) and other identifying information. Online users also need to provide an email address for invoicing. Online bidders also need to register a credit card if they want to spend more than $500 in any auction. The card registration is only used to confirm the details of the bidder and is not actually used for setting up an online payment process. Bidding Each catalogue is manually opened prior to the auction date to enable bidders to view the lots and enter any "absentee" bids (these are bids on a lot before the actual auction date). Absentee bids are visible to other users as they chance the current auction price from the expected price range to the current bid price. A record of each users bids (past and present, including absentee bids) is kept so that users can view what they have bid on. Users can also watch lots for upcoming auctions. During the auction, each bid amount is recorded and available to other bidders (this includes the bidder number). Once the hammer falls, the last bid and amount is recorded. If there is a reserve price that is not met, the bid details are recorded to allow a negotiation process to take place between the vendor/seller and bidder. This process may result in the vendor/seller settling for a lower price or negotiating a slightly higher price than the final bid. Very rarely there is no negotiation or agreement reached and the lot remains unsold regardless of the closing bid. After a lot is auctioned, the auction price is recorded, the winning bidder's details.

On completion of the auction, successful bidders can pay for their auction items. Bidders are provided with a paper/digital invoice that lists the items they have won from each auction, the sale price, and the total cost (Buyers pay a 16.5% inc. GST premium on all purchases). Bidders can pay for their items via bank transfer or credit card. If completed via bank transfer, the funds must be cleared, and the date recorded before they can collect the items. Once all the necessary details are in place, bidders can collect their items and they are marked off upon removal from the auction house. It should be noted that some items can be quite bulky so bidders may elect to have these collected at a later date/day by a removalist. Sellers pay a commission of 20% (inc. GST) and a lotting fee of $3 per lot. These fees are deducted from the sold amount only after the bidder funds have cleared. Where a bidder does not pay within 3 days, the items are re-listed in the next available weekly auction at no additional cost to the seller. For our purposes we don't need to worry about how the seller invoices are paid to sellers, just the total amount on the invoice and the date.

Part A - UML Design A Traditional domain model diagram using UML standards (no Foreign Keys in classes, use of association classes, weak entities, inheritance etc where appropriate) Each class should contain a list of the applicable attributes that meet the design requirements The diagram should highlight ALL associations between classes including their multiplicities and include appropriate roles/names to describe the purpose of the association The domain model diagram should be drawn up in UMLet or some other UML standard design tool o Multiplicities must be included and can be represented using numerical or crow's foot notation o Any Primary and Candidate Keys should be depicted using (pk), (ck) etc. o Save the UML as an image to be included in your final word document (make sure it is still readable!) o If you include foreign key attributes their names will be checked between diagrams so watch out! o You need to provide any written assumptions that may justify why you chose certain attributes/association types over another. These should be short descriptive dot points.

Part B - CREATE TABLE Statements at least 4 table schemas covering the following: o The classes involved in listing each lot in an auction and images associated with each lot. o The classes involved in registering on-floor and online bidders Your schemes should be in the format: TableName(attribute1, attribute2, ..., attribute n) PK(attribute1) FK(attribute2) -> Table2Name(attribute1) Part C - CREATE TABLE Statements A working table creation statement for each of the above table schemas.