Question

Project 4: ER-Diagram and DBDL (25 Points)

Database Design Language (DBDL)
Use Visio or Word to create the model. The correct syntax must be used. Remember to list out all alternate keys, secondary keys, and foreign keys.

Entity Relationship Diagram (ERD)
Draw the data model for the situation using LucidChart, Microsoft Visio, Word or other ERD Drawing Program. When drawing the objects, be sure to use the correct shapes, and syntax. Example: Entity is a rectangle box, and the name is a singular noun.

DUE DATE: NOVEMBER 20, 2018

Consider an ONLINE_AUCTION database system in which members (buyers and sellers) participate in the sale of items. The data requirements for this system are summarized as follows.

The online site has members, each of whom is identified by a unique member number. Other information kept on each member is the email address, name, password, username, home address, and phone number.

A member may be a seller or a buyer or both. A buyer has a shipping address. A seller has a bank accountant number, and routing number.

Items are placed by a seller for sale and are identified by a unique item number. Items are also described by an item title, a description, starting bid price, bidding increment, the start date of the auction and the end date of the auction.

Buyers make bids for items they are interested in. Bid price and time bid is recorded. The bidder at the end of the auction with the highest bid price is declared the winner and a transaction between buyer and seller may then proceed.

A buyer can place many bids (or none at all).

A seller can sell many items (or none at all).

Each item is sold by only one seller.

An item may have zero bids, or may have many bids.

The buyer and seller may record feedback regarding their completed transactions. Feedback contains a rating of the other party participating in the transaction (1-10) and a comment.

Answer 1

Hi,

Please find the below ER diagram for your question. I’ve added attributes to the entities Site, Members, Items , in the text format rather than in diagram, to make it look clean. You can add attributes to the mentioned entities similarly to one added for Buyer and Seller entities.

fig: ER Diagram.

Entity Member:

Attributes:

Member Number(Primary Key)

Email address

Name

Password.

Username

Home Address

Phone Number.

Entity Items:

Attributes:

Item Number(Primary Key)

Item Title

Description

starting bid price

bidding increment

auction start date

auction end date

fk_Buyer_id( Foreign Key Relation from Buyer to Item Entity)

Bid price

Time BID

Fk_Seller_id(( Foreign Key Relation from Seller to Item Entity)

Comments

FeedBack

Entity Site :

Attributes:

ID(Primary Key)

Entity Buyer:

Attributes:

Fk_member_id (Foreign Key from Member to Buyer Table).

Shipping address

Entity Seller

Fk_member_id (Foreign Key from Member to Buyer Table).

Bank Account

Routing Number.