In this schema, users can sell items at auction, and can also place bids on auctions by other users. An auction has a start time, when bidding begins, and an end time (both of type datetime).
computer science
Description
In this schema, users can sell items at auction, and can also place bids on auctions by other users. An auction has a start time, when bidding begins, and an end time (both of type datetime). The auction is won by the highest bid made before the end time, provided the bid is higher than the minimum bid. The description of the item that is being sold in the auction is kept in a separate table ITEMTYPE, because several items of the same type (say, a certain book or model of sneakers) may be sold in different auctions by the same or different users, and this way we avoid storing the same information repeatedly. (Note that the condition of the item, such as ``new'', ``like new'', ``slightly damaged'' etc., is kept in the AUCTION table since the same type of item may come in different conditions.) Finally, a bid by a user is for a particular auction, and contains the time of the bid and the bid price (which should be higher than the minimum bid and all previous bids for this item, to have a chance to be successful, but this is not enforced by the schema).
(a) In the AUCTION table, (seller, iid, starttime) is the primary key. Could (seller, iid) be a primary key? Why or why not?
(b) For table BID, does bidtime have to be part of the key? What happens if we remove bidtime from the key?
(c) Write statements in SQL for the following queries.
I. For each user, output how many items they put up for auction in 2018. II. For each auction that was successfully sold, output the uids of the buyer and seller, the
iid, and the price at which it was old.
III. Output the highest bid that was made for an item with itemname “IPhone 12”. IV. Output the uid of any user who has placed more than ten bids but who did not win even a single auction.
V. For each itemname and condition, list the average winning bid for auctions involving such an item during 2018. (So, what is the average price for which such an item sold in 2018?)
VI. Output the uid of any user who placed a bid in his/her own auction.
VII. List any pair of users that placed bids in the same auction in more than ten auctions in 2018.
(Meaning, users that have often competed for the same item.)
