Question 4 of 8

Question 4 (20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Given. Members (unique member number, email, name, password, home address, phone) may be a buyer (has a shipping address), a seller (has a bank account/routing number), or both. Sellers list items (unique item number, title, description, starting bid, bid increment, start/end dates) under a fixed classification hierarchy (e.g. Class: Computer → Subclass: Hardware → Description: Modem). Buyers place bids (price, timestamp) on items; the highest bidder wins when the auction ends, and buyer/seller may each leave feedback (a 1–10 rating and a comment) about the other on the completed transaction.

Find. A complete ER model with entities, keys, relationship cardinalities/participation, and any constraint the diagram cannot capture.

Approach. Model Member as a superclass with an EER specialization into Buyer and Seller — overlapping, not disjoint, since "a member may be a buyer or a seller, or both." Model Category with a self-referencing (recursive) relationship, since "Class > Subclass > Description" is a hierarchy of arbitrary depth built from the SAME entity type, not three different entity types. Model Bid as a weak entity with TWO identifying owners, Buyer and Item, because a plain M:N relationship allows only one relationship instance per (Buyer, Item) pair, while a buyer may legitimately bid on the same item several times at different timestamps — Bid needs its own identity (partial key: bid time) precisely to allow that. The "highest bid wins" rule is a comparison ACROSS all of an item's bids, which is exactly the kind of constraint no ER diagram can express (see below).

Entities. Member (MemberNo key, Email, Name, Password, HomeAddress, Phone), specialized (overlapping, partial — a member need not be either) into Buyer (ShippingAddress) and Seller (BankAccountNo, RoutingNo). Category (CategoryName key) with a recursive SubcategoryOf relationship (1 parent : N children). Item (ItemNo key, Title, Description, StartingBid, BidIncrement, StartDate, EndDate). Bid (weak; partial key BidTime; attribute BidPrice). Transaction (TransactionID key).

Relationships. SubcategoryOf (recursive on Category, 1:N, partial — a top-level category has no parent). ClassifiedAs: Category (1) – Item (N, total — every item needs a leaf category). Lists: Seller (1) – Item (N, total on Item, partial on Seller). PlacedBy (identifying): Buyer (1) – Bid (N, total). For (identifying): Item (1) – Bid (N, total). Sells: Item (1) – Transaction (1, partial on Item — only items that actually sold get a transaction). Feedback: Transaction (N) – Member (M) — connected to the MEMBER superclass rather than to Buyer or Seller alone, since either party to a completed transaction may leave feedback ABOUT the other, and each transaction can carry up to two Feedback entries (buyer-about-seller, seller-about-buyer), each with its own Rating and Comment attributes.

Constraint the ER diagram cannot capture. "The bidder with the highest bid price is declared the winner" requires COMPARING every Bid an item has received and selecting the maximum — an aggregate, cross-instance computation over an entire relationship set. ER cardinality and participation constraints only describe how many instances of one entity type can relate to another; they have no notation for "pick the extreme value among a set of related instances." This is exactly why "winner" is NOT drawn as its own attribute or relationship above: which Bid is the winning one is derived data (computable at query time from Bid.BidPrice grouped by Item), not a fact the schema needs to store or can constrain structurally. The Transaction entity captures the CONSEQUENCE of that computation (a sale occurred) without the ER diagram itself expressing the "highest wins" rule.