Y You are setting up your own business by developing an Electronic Auction websi

Question

Y

You are setting up your own business by developing an Electronic Auction website for the staff, students, and faculty of your University. 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 offered 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 website, he/she must set up an account with payment methods and personal information (name, email, phone, gender, birthday, address, etc). After the account is established, he/she can offer items or buy items.

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

The database should answer the following queries in order to run the auction business:

1. What is the name of the buyer who bought item#12345 on what date and at what price?

2. List the Item# for all items which has been registered on Dec. 24th, 2016

3. List the names, phone and account #'s 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#12345

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

7. List the name, phone #, and email for all bidders that offer the bid as the ask price for item #598582

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

Explanation / Answer

Hi,

PFB the Database Structure and queries. Please comment for any queries/updates.
Thanks,
Anita

Tables:
ITEM
   ITEM_ID,ASK_PRICE,NOOFBIDS,FINAL_SALE_PRICE,CATEGORY,DESCRIPTION
PERSON
   PERSON_ID,NAME,EMAIL,PHONE,GENDER,BIRTHDAY,ADDRESS,ACCOUNT_ID, START_DATE
ACCOUNT
   ACCOUNT_ID,
BID
   NAME,EMAIL,PHON,ITEM_ID,PERSON_ID, START_DATE,END_DATE

1. What is the name of the buyer who bought item#12345 on what date and at what price?

SELECT B.NAME,B.DATE,I.FINAL_SALE_PRICE FROM BID B,ITEM I WHERE B.ITEM_ID = '12345' AND B.ITEM_ID = I.ITEM_ID

2. List the Item# for all items which has been registered on Dec. 24th, 2016
SELECT B.ITEM_ID FROM BID B WHERE B.START_DATE = '2016-12-24'

3. List the names, phone and account #'s of sellers who have registered item with ask price less than $100
SELECT B.NAME,B.PHONE,P.ACCOUNT_ID FROM BID B, PERSON P, ITEM I WHERE B.PERSON_ID= P.PERSON_ID AND B.ITEM_ID= I.ITEM_ID AND I.ASK_PRICE<100

4. List the account opened-date, name, email, address, and number of items under auction by the customer whose name is Nancy Wilson
SELECT P.START_DATE,P.NAME, P.EMAIL,P.ADDRESS , count(B.ITEM_ID) FROM PERSON P, BID B WHERE B.PERSON_ID =P.PERSON_ID AND P.NAME = 'Nancy Wilson' AND P.NAME=B.NAME

5. List the names, emails, and bids for the item#12345
SELECT B.NAME,B.EMAIL FROM BID B WHERE B.ITEM_ID = '12345'

6. List the dates that the auction opens and closes for Item#12345
SELECT B.START_DATE,.END_DATE FROM BID B WHERE B.ITEM_ID ='12345'

7.List the name, phone #, and email for all bidders that offer the bid as the ask price for item #598582
SELECT B.NAME, B.PHONE,B.EMAIL FROM BID B,ITEM I WHERE B.ITEM_ID = I.ITEM_ID AND I.FINAL_SALE_PRICE =
       (SELECT IT.ASK_PRICE RFOM ITEM IT WHERE IT.ITEM_ID = '598582')

8. List the number of items that under each category of items for auction
SELECT COUNT(B.ITEM_ID), I.CATEGORY FROM BID B, ITEM I WHERE B.ITEM_ID =I.ITEM_ID GROUP BY I.CATEGORY

9. list the item# and description for all items that under the category with type 'custom textbook'
SELECT I.ITEM_ID,I.DESCRIPTION FROM ITEM I WHERE I.CATEGORY = 'custom textbook'

10. List the names and emails of the buyers who bought items listed in the 'exercise equipment' category
SELECT B.NAME,B.EMAIL FROM BID B,ITEM I WHERE B.ITEM_ID= I.ITEM_ID AND I.CATEGORY='exercise equipment'

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.