Lesson 4	The course database
Objective	Understand the design for the course project.

Data Model for Course Project Database

Creating an ERD diagram using the Edraw tool from EdrawSoft involves a few straightforward steps.
Here’s a summary based on the information I found:

Installation and Setup: First, ensure that EdrawMax is installed and enabled on your computer. Start the application and navigate to the "Database Modeling" section to access ER Diagram templates .
Choosing a Template: You can select a pre-designed ERD template from the gallery or start with a blank template. EdrawMax offers various ERD symbols and templates to cater to different needs.
Creating the ER Diagram: If you're starting from scratch, you can add symbols from the Symbol Library. Look for the "Database Modeling" section within the library to find relevant symbols. For pre-designed templates, you can customize them by adding or modifying symbols as needed
Customization and Editing: EdrawMax provides a range of editing tools to customize your ERD. You can adjust the size, shape, color, and alignment of the components to fit your requirements. The tool allows for detailed customization to ensure your ERD accurately represents the database structure.
Saving and Sharing: Once your ER diagram is complete, you can save and export it in various formats, such as JPG, PNG, PDF, Visio vsdx, and more, for easy sharing and integration into other documents. EdrawMax also supports sharing the diagram through social media and publishing it on the EdrawMax template gallery.

Additionally, for those interested in creating a Chen ER Diagram specifically, Edraw provides detailed steps to choose a Chen ERD template, customize it with the necessary symbols, and export or share the final diagram. These steps are designed to be user-friendly, allowing both beginners and experienced users to create comprehensive ER diagrams efficiently. For detailed guidance, EdrawSoft offers tutorials and examples on their website.

Half-Eagle Rarities Auction House

Throughout the course, you will be working on a course project which consists of a database that you create and maintain. This lets you apply the skills you are learning 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 everyone else, 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:

This diagram represents an Entity-Relationship Diagram (ERD) for a database system involving auctions, lots, coins, bids, and clients. Below is an analysis of the tables in the data model:

**`LOT` Table**

**Primary Key:** `lot_id`

**Attributes:**

`min_price`: The minimum price of the lot.

`auction_id`: References the associated auction ( `AUCTION` table).

`winning_bid`: The winning bid amount.

`winning_bidder`: References the winning bidder ( `CLIENT` table).

**`COIN` Table**

**Primary Key:** `coin_id`

**Attributes:**

`date`: The minting date of the coin.

`mint_mark`: The mark indicating the mint location.

`series`: The coin's series.

`denomination`: The coin's denomination (e.g., penny, dollar).

`variety`: The variety/type of coin.

`grade`: The quality/grade of the coin.

`owner_client_id`: References the owner (`CLIENT` table).

`country`: The country of origin of the coin.

**`AUCTION` Table**

**Primary Key:** `auction_id`

**Attributes:**

`start_time`: The starting time of the auction.

`stop_time`: The ending time of the auction.

**`BID` Table**

**Primary Key:** Composite key (`auction_id`, `lot_id`, `bid_id`)

**Attributes:**

`bid_amount`: The amount of the bid.

`bidder_client_id`: References the client placing the bid ( `CLIENT` table).

**`CLIENT` Table**

**Primary Key:** `client_id`

**Attributes:**

`client_name`: The name of the client.

`client_phone`: The client's phone number.

`client_email`: The client's email address.

`client_password`: The password for the client account.

**`COINS_IN_LOT` Table**

**Primary Key:** Composite key (`auction_id`, `lot_id`, `coin_id`)

**Attributes:** None (only foreign keys).

**Relationships:** Acts as a junction table between `LOT` and `COIN`, representing which coins are included in a particular lot.

**Relationships Between Tables**

**`LOT` ↔ `AUCTION`**:

Each lot belongs to one auction (`auction_id` is a foreign key in `LOT`).

**`LOT` ↔ `CLIENT`**:

The `winning_bidder` in `LOT` references a client ( `CLIENT` table).

**`LOT` ↔ `COIN`**:

Relationship modeled via the `COINS_IN_LOT` table.

**`COIN` ↔ `CLIENT`**:

The `owner_client_id` in `COIN` references the owner in the `CLIENT` table.

**`BID` ↔ `LOT`**:

Bids are associated with specific lots.

**`BID` ↔ `CLIENT`**:

`bidder_client_id` references a client who placed the bid.

**Overall Description**
This ERD models an auction system where:

Clients own coins and participate in auctions by bidding on lots containing coins.

Auctions are organized into lots, and each lot contains one or more coins.

The relationships ensure detailed tracking of ownership, bidding, and auction outcomes.

Database ERD for the Eagle Rarities Project

Create Database and Tables: 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. You will start on this once you begin learning the tasks involved.

Oracle Cloud Infrastructure

Real-world aspects of this Project Scenario

Here are some of the real-world aspects of this project scenario:

There really is a company, Teletrade™, that conducts coin auctions online. You can view its Web page at www.teletrade.com
The design includes one potentially very important 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 is crucial 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.