Question

Database Management and Design

Homework assignment

Directions: Create an ERD for the following case. Make sure you use the proper cardinalities.

Sweeping Bulldog (SB) is an online textbook exchange portal. It allows an individual or customer (student, faculty, staff, etc.) to sell their used textbooks directly to other people without going through the formal UTC book store. The portal allows the individual to drop off their books anytime, most probably at the end of one semester. The individual then receives payments after the books are sold, most likely at the beginning of the next semester. While textbook is the main item traded on Sweeping Bulldog, SB also sells several different types of items, i.e., DVDs, VCDs, and VHSs. Remember, SB will not actually buy used items from people to resell. It just stores those items in a big warehouse, which is in walk-distance from the UTC library. The warehouse is very big, about 2,500 square feet. SB will charge a small amount of money for each successful deal.

An individual or customer may be a student, a faculty member, a staff member, or any other people on and off UTC campus. SB also aims to draw attentions from people at other colleges and universities. SB needs to maintain an individual’s information, which includes name (first, middle, last), address (street, city, state, zip code), phones (work phones, home phones, or maybe cell phones). If the individual is a UTC student, the major information (e.g., MIS, ACCT, FIN) is required. The expected graduation date is also important. If the individual is a professor, the department name needs to be recorded. A student may be from another school (such as Twin City, UWS), so that the name of the college needs to be maintained.

An individual may have two different roles at SB, a seller or a buyer. When an individual sells an item, the name, description, asking price which is the price that the seller desires, notification price which is the price at which the seller should be notified, conditions (from 1 to 5, 1 means ‘really new’, 5 means ‘really old’), posting date and time, and expiration date and time. SB also allows a seller to upload a picture file (such as jpg, gif) associated with the item. If the item is a book, the ISBN number and author information needs to be kept. A customer may or may not post many items and a particular item can be only placed by one customer.

To keep the status of the item in transaction, SB often needs to update the status with ‘In-process’, ’Pending’, ‘Sold’, and ‘Shipped’. When a seller places an item, the status is ‘In-process’ SB also needs to keep a history record of selling activities, which basically includes seller’s identification number, number of listed items, number of sold items, number of items that are currently for sale, and the date and time of the most recent transaction. A customer can have only one history record and a selling history only belongs to one customer.

To better manage customers, SB needs to keep track of their email addresses so that any successful transaction will be notified. SB also needs to create a password for a customer together with account information so that he can log in later. A customer only has one email address and password. An email address and password only belongs to one customer.

When a customer wants to buy an item, they can place a bid on that item. A bid is an intention to buy an item at a specified price. This bid must be higher than any previous bid prices for the same item (also called English Auction). There are some generally rules about the new biding price, i.e., 10% more of the previous price if the previous price is no more than $50, and 5% more if the previous price is more than $50. The information required for bidding includes an automatic identification, date and time, bidding price, and the difference between current bid and previous bid. A customer can place many bids on an item and an item can be bid by many customers.

A seller of an item can review the various bids that have been made on that item. When the seller finds an acceptable bid, they can accept that bid so that an actual sale is started. The sale process involves recording information about the winning bid as a sale and notifying the buyer who places that bid. A bid is ‘pending’ until the bidder acknowledges the purchase of the item. A customer can sell many items and an item can only be sold by one customer. For the sale information, the item information, buyer information, winning price, completion date need to be kept.

If a buyer is off campus or from other colleges, SB needs to record their shipping addresses, if the address is not the same as that in the individual information. SB will handle the shipping issues and usually ships a book after the buyer sends in a check, which is the only transaction method.

Warehouse pickers are responsible for packaging the items for each successful bid. They need to know basic customer information (shipping information). No money information should be shown to these pickers. Upon completion of the shipment package the status of the items is upgraded to "shipped" and the shipment is sent out to the member. Because a customer may have several successful bids, several sold items can be put together on a single shipment ticket

Corresponding item information needs to be printed on a shipping ticket. The shipment ticket carries a shipment date besides the member's name and shipping address. A return form (with a running sequence number) is also enclosed to help members return products.

SB also sells old items by themselves. SB collects these old items from some small distributors. SB maintains information about the distributors themselves. Each distributor has a unique name and address. All the distributors come from three states, i.e., MN, WI, and IA. There is also a primary contact person at each distributor for SB, whose name, and contact phone number form part of the information about distributors. When SB realizes a particular item is in demand, it generates a purchase order for that item and sends it to one of many distributors for that item. The order point for a book cannot be over 20. SB has learnt that it is a lot more efficient to generate a purchase order that covers more than just one item so long as the associated distributor carries all of items in the purchase order. The purchase order information includes order number, order date, a description for each item, and the order quantity for each. A staff from SB would call the distributor to obtain the price quote and availability for each item before forwarding the purchase order. All the distributors for books must be from MN. An expected delivery date (for the entire order, not on an item basis) from the supplier then becomes part of the purchase order information. The expected delivery date must be within 2 weeks after the order date.

Distributors package all of the items in a single purchase order into one shipment when shipping to SB. The three carriers for SB (i.e., FedEx, UPS, USPS) impose some restrictions on packages sizes and weights. Each distributor generates an invoice against each purchase order. The invoice includes the shipment date, items shipped with quantities of each and bottom bidding price. This information is very important for SB to be able to inform their staff members at the time of receiving orders. SB makes payments to distributors only after the items are sold.

Answer 1

This Solution of E-R diagram is shown in figure, however for clarification the steps are mentioned below:

1. It starts with customer, the customer can behave as buyer as well as seller.

• Every customer can login to the SB online store by using the credentials maintained by Admin.
• If the customer wants to behave as buyer, he needs to bid for the item. one customer can bid for multiple items considering the restriction on previous bid value. So there exists relationship between buyer and item.
• If the buyer finds suitable bid, finds the seller and the same starts. So there exists relationship between buyer and seller.

2. Customer Behaving as Seller

• Every Seller can login to the SB online store by using the credentials maintained by Admin.
• Seller places an item to sell with essential attributes one seller can place multiple items however for one item only one customer so there is a relationship.
• Seller can review various bids of buyer and find a suitable buyer then communication between buyer and seller starts.

3. Sale Process

• Only the buyer and seller information are exchanged, then the sale process from SB comes into play.
• Finding the distributor from SB warehouse and then preparing invoice and shipment.