Part 1: Please Read The Business Statement Below And Drawer
Part 1 Please Read The Business Statement Below And Drawer Ner And
Part 1 Please Read The Business Statement Below And Drawer Ner And
Part 1: Please read the business statement below and draw ER, NER, and Table Schema diagrams for it. Business Statement: The project is about developing an auction Web site. The details are as follows: BA is an online auction Web site. People can buy and sell items in this Web site. Buyers are people who like to buy items, and sellers are people who like to sell items. Each seller can sell items. Each item has a bidding start time, an end time, and an owner. Sellers are owners of their item. The start time and end time include the date as well. Each seller has a name, contact information, and credit card information. They also have a user name and a password. Contact information consists of an address, an email, and a telephone. An address consists of a street number and name, city, state, and zip code. Credit card information consists of owner name, card number, and expiration date. Each item has a name, condition, an initial price, a description, quantity, one or more pictures, and an owner. The condition could be New, Refurbished, or Explained. If the condition of an item is set to Explained, the seller should explain about the item condition in the item description. Each buyer has a name, contact information, and credit card information. They also have a user name and a password. When buyers login to Web site, they can go to the list of all available items and then go to the item detail page. Buyers can also search for an item. The application will search through item names for the search phrase. Buyers can bid on items. Once a bid is made, buyers are accountable for their bid. In other words, buyers cannot simply remove their bid. If they change their mind, all they can do is to update their bid with the price of zero. Of course, they can do that before the auction expires. After an auction expires, the buyer with the highest bid is the winner.
Part 2: Please write SQL Query for each statement below. • BA likes to have a set of statistics about the system as follows: The most active seller (the one who has offered the most number of items) The most active buyer (the one who has bought the most number of items) The most expensive item sold ever The most expensive item available The cheapest item sold ever The cheapest item available
Paper For Above instruction
The development of an online auction website involves complex data interactions among buyers, sellers, items, bids, and associated contact and credit information. To model this system, we begin by identifying key entities and their relationships, represented through Entity-Relationship (ER) diagrams and normalized table schemas. This paper will detail the ER model, normalize the schema, and provide SQL queries to extract system statistics such as the most active sellers and buyers, as well as the highest and lowest priced items, both sold and available.
Entity-Relationship (ER) Diagram
The core entities in this auction system include Seller, Buyer, Item, Bid, ContactInformation, and CreditCardInformation. The relationships among these entities are as follows:
- Seller owns zero or more Items.
- Buyer places zero or more Bids on Items.
- Item can have multiple Pictures.
- Seller and Buyer each have one associated ContactInformation and CreditCardInformation.
The ER diagram demonstrates relationships such as:
- Seller (1) — (M) Item
- Buyer (1) — (M) Bid
- Bid (M) — (1) Item
- Seller (1) — (1) ContactInformation
- Seller (1) — (1) CreditCardInformation
- Buyer (1) — (1) ContactInformation
- Buyer (1) — (1) CreditCardInformation
Normalized Table Schema
Based on the ER model, the database schema consists of the following tables:
- Sellers
- SellerID (PK)
- Name
- UserName
- Password
- ContactInfoID (FK)
- CreditCardID (FK)
- BuyerID (PK)
- Name
- UserName
- Password
- ContactInfoID (FK)
- CreditCardID (FK)
- ContactInfoID (PK)
- StreetNumber
- StreetName
- City
- State
- ZipCode
- Telephone
- CreditCardID (PK)
- OwnerName
- CardNumber
- ExpirationDate
- ItemID (PK)
- Name
- Condition (ENUM: New, Refurbished, Explained)
- InitialPrice
- Description
- Quantity
- OwnerID (FK to Sellers)
- BiddingStartTime
- BiddingEndTime
- PictureID (PK)
- ItemID (FK)
- PictureURL
- BidID (PK)
- ItemID (FK)
- BuyerID (FK)
- BidPrice
- BidTime
SQL Queries for System Statistics
The following SQL queries retrieve the requested statistics:
1. Most Active Seller - Seller with the Most Number of Items Offered
SELECT s.SellerID, s.Name, COUNT(i.ItemID) AS ItemCount
FROM Sellers s
LEFT JOIN Items i ON s.SellerID = i.OwnerID
GROUP BY s.SellerID, s.Name
ORDER BY ItemCount DESC
LIMIT 1;
2. Most Active Buyer - Buyer who has Bought the Most Number of Items
SELECT b.BuyerID, b.Name, COUNT(bb.ItemID) AS PurchaseCount
FROM Buyers b
JOIN Bids bb ON b.BuyerID = bb.BuyerID
GROUP BY b.BuyerID, b.Name
ORDER BY PurchaseCount DESC
LIMIT 1;
3. Most Expensive Item Sold Ever
SELECT i.ItemID, i.Name, MAX(bb.BidPrice) AS MaxBidPrice
FROM Items i
JOIN Bids bb ON i.ItemID = bb.ItemID
GROUP BY i.ItemID, i.Name
ORDER BY MaxBidPrice DESC
LIMIT 1;
4. Most Expensive Item Available (Not Sold, or Highest Current Bid If Sold)
SELECT i.ItemID, i.Name, i.InitialPrice
FROM Items i
LEFT JOIN (
SELECT ItemID, MAX(BidPrice) AS HighestBid
FROM Bids
GROUP BY ItemID
) b ON i.ItemID = b.ItemID
ORDER BY COALESCE(b.HighestBid, i.InitialPrice) DESC
LIMIT 1;
5. Cheapest Item Sold Ever
SELECT i.ItemID, i.Name, MIN(bb.BidPrice) AS MinBidPrice
FROM Items i
JOIN Bids bb ON i.ItemID = bb.ItemID
GROUP BY i.ItemID, i.Name
ORDER BY MinBidPrice ASC
LIMIT 1;
6. Cheapest Item Available (Not Sold or Lowest Current Bid)
SELECT i.ItemID, i.Name, i.InitialPrice
FROM Items i
LEFT JOIN (
SELECT ItemID, MIN(BidPrice) AS LowestBid
FROM Bids
GROUP BY ItemID
) b ON i.ItemID = b.ItemID
ORDER BY COALESCE(b.LowestBid, i.InitialPrice) ASC
LIMIT 1;
Conclusion
The above ER diagram, schema, and SQL queries formulate a comprehensive foundation for managing an online auction platform. The schema ensures data normalization, efficiency, and integrity, while the SQL queries facilitate effective retrieval of critical system statistics. Such data insights are essential for assessing system activity, understanding user engagement, and making strategic decisions to enhance platform performance.
References
- Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
- Intro to Database Design.