You will be working on a course project which is a database that you create and maintain. This gives you the opportunity to apply the skills you learn to a real-world situation. For your project, you are a consultant working for Half-Eagle Rarities, an auction house specializing in rare coins from around the world. Like every other business, Half-Eagle Rarities wants to get on the Internet and establish a niche in the world of online commerce. Your client has just purchased a software package designed to manage coin auctions over the Internet using a Web-based interface. Your mission is to set up and create the database to support that software.
The logical design already exists, and looks like this:
The provided image represents an Entity-Relationship Diagram (ERD) for an auction system database. The ERD includes six main tables: `AUCTION`, `LOT`, `COIN`, `COINS_IN_LOT`, `BID`, and `CLIENT`. Here’s a breakdown of each table and its attributes:
AUCTION Table:
auction_id: A unique identifier for the auction.
start_time: The start time of the auction.
stop_time: The end time of the auction.
LOT Table:
auction_id: Foreign key linking to the AUCTION table.
lot_id: A unique identifier for a lot in the auction.
min_price: The minimum bid price for the lot.
auction_id: (appears to be repeated; likely meant to emphasize its foreign key status).
winning_bid: The bid amount of the winning bid.
winning_bidder: The ID of the client who placed the winning bid.
COIN Table:
coin_id: A unique identifier for a coin.
coin_date: The year or date related to the coin.
mint_mark: The mint mark on the coin.
series: The series name of the coin.
denomination: The denomination value of the coin.
variety: The variety classification of the coin.
grade: The grade or quality rating of the coin.
owner_client_id: The ID of the client who owns the coin.
country: The country of origin of the coin.
COINS_IN_LOT Table:
auction_id: Foreign key linking to the AUCTION table.
lot_id: Foreign key linking to the LOT table.
coin_id: Foreign key linking to the COIN table.
BID Table:
bid_id: A unique identifier for a bid.
auction_id: Foreign key linking to the AUCTION table.
lot_id: Foreign key linking to the LOT table.
bid_amount: The amount of the bid.
bidder_client_id: The ID of the client who placed the bid.
CLIENT Table:
client_id: A unique identifier for a client.
client_name: The name of the client.
client_phone: The phone number of the client.
client_email: The email address of the client.
client_password: The password for the client (likely stored in a secure, hashed form).
Relationships:
The LOT table is related to the AUCTION table through auction_id.
The COINS_IN_LOT table creates a many-to-many relationship between the LOT and COIN tables, associating multiple coins with a single lot.
The BID table associates bids with both the AUCTION and LOT tables and links to clients through bidder_client_id.
The CLIENT table holds information about the clients, which are related to the bids they make and coins they own.
This ERD models an auction system where clients can place bids on lots containing various coins, and all data is linked through appropriate relationships.
Half-Eagle Rarities consisting of tables LOT, COINS_IN_LOT, COIN, AUCTION, BID, CLIENT
Your job is to create the database, create the tables to match the design shown above, and work with the systems integrators to make minor changes as they customize the software. As we proceed, you will build this database incrementally through project-related exercises. Your project will begin as soon as you start learning the tasks involved. Some of the real-world aspects that apply to this project scenario are:
There really is a company, Teletrade™, that conducts coin auctions online.
The design includes one potentially frequently used table. This is the bid table, and it will likely sustain a high volume of inserts while auctions are taking place. Insert performance on this table will be critical to the success of the application.
Referential integrity needs to be enforced.
There is a many-to-many relationship between the Lot and Coin tables. This has been resolved into two one-to-many relationships through the use of the Coins_in_Lot table.
The previous lesson included a link to a ZIP file that contains scripts that automatically create the database and user schema to support this database. This course also provides the steps to build scripts that will create the tables, constraints, and indexes in this database.
