SQL2

Carl H. Lindner College of Business

University of Cincinnati

IS-3030 Database Design

Conceptual Data Modeling Workshop

E-Auction Database

You and your classmates would like to set up your own business by developing an Electronic Auction Web site (e.g., for the staff, faculty and students of University of Cincinnati). According to your understanding of database concepts, you would like to build a database to support the operations of this auction web site. To run your business, you need to keep track of the transactions, specifically, the items that are offered to be auctioned, the person offering the item, and bids and ask prices. In order for any customer (a buyer or a seller) to be able to conduct business on your web site, he or she must set up an account with payment methods and personal details (such as, name, email address, phone, gender, birthday, address, etc.). After the account is established, he or she can offer items for auction or buy auction items. A customer can offer many items for auction.

However, everyone can offer bids along with his or her name, telephone number and email without creating an account. For each auction item, you want to keep certain information such as the ask price, the bids, the final sale price, and the categories that it belong to. The seller must assign each offered item to at least one category, and up to five categories.

Your database should answer the following queries in order to run the auction business. You should consider the information needs as data requirements in your conceptual data modeling.

1. What is the name of the buyer who bought Item#123456789, on what date, and at what price?

2. List the Item# for all items which has been registered on July 4 9, 2016.

3. List the names, phone and account numbers of sellers who have registered item with ask price less than $100.

4. List the account opened-date, name, email, address, and number of items under auction by the customer whose name is Nancy Wilson.

5. List the names, emails and bids for the Item#123456789.

6. List the dates that the auction opens and closes for Item#123456789.

7. List the name, telephone umber, and email for all the bidders that offer the bid as the ask price for item#598979975.

8. List the number of items that under each category of items for auction.

9. List the Item# and description for all items that under the category with type ‘custom textbook’.

10. List the names and emails of the buyers who bought items listed in the ‘Exercise equipment’ category.

Assignment:

One of the challenges for database designers is to represent and communicate their understanding of data requirements to end users. On a single sheet of paper, draw an Entity-Relationship (ER) diagram that you will use to represent and communicate your understanding of the data requirements of this E-Auction.

1. What entities are relevant?

2. What attributes are relevant (indicate if its an identifier, derived, etcetera)?

3. What relationships are relevant? Indicate degrees and whether its an “Is-A” or “Has-A.”

4. Draw the entire model on the back.