CSC 4480: Principles of Database Systems
Sample Deliverable for Phase 1: Project Proposal

A new internet startup company, Swapaholic.com, intends to set up a website that allows small groups of people to exchange books, videos, and other such things. Swappers can list the items they're willing to exchange, and can sign up to take any item from their group in return. The geniuses behind Swapaholic.com eventually expect to make money from this by--you guessed it--displaying targeted advertising.

Business Rules:

  1. A swapper joins Swapaholic.com by registering with his or her e-mail address and a password. Swappers cannot share e-mail addresses.
  2. A swapper may create a new group, or join an existing one. Some groups are open to anyone, but most are by invitation only. The date that a swapper joins a group is recorded. A swapper's items are available to all groups that he or she has joined.
  3. Each swapper lists the items that he or she is willing to swap. Each item is listed with a media category, title, a short description and, optionally, a media-specific identification number (e.g., ISBN for books, serial numbers for DVDs). The date that an item becomes available for swapping is also recorded.
  4. The categories of media will grow over time, but for now include books, videotapes, DVDs, audio CDs, and video game console cartridges and disks.
  5. To make things simple, each item is considered equal in value to any other item.
  6. Each swapper chooses from all the items listed by the group as a whole that he or she would like to borrow.
  7. The order in which a swapper registers a borrow request is recorded so that, once the item becomes available, the next swapper in line will receive it.
  8. Each swapper may borrow up to ten items, and no more than five items from a single lender, at a time.
  9. A borrowing swapper must return an item to its owner.
  10. The time that an item is in possession of each borrower is recorded by date, as is the condition that the item was returned: same as when loaned, minor damage, or major damage or lost.
  11. Swappers earn rating points for short borrowing periods and for not damaging the items. Each borrow starts out worth 3 points and loses one point for each week it is kept beyond the first two-week period and also loses 1 point for minor damage, and three for major damage or a lost item. The minimum number of points for a single swap is zero.
  12. A swapper's rating is group-specific. That is, a swapper that belongs to three groups could have three different ratings.
  13. A swapper may view a brief history of all swaps (borrows and loans) by another member of the group.
  14. A swapper is not to make copies of any item that he or she borrows.

Exceptions:

  1. If the owner of an item agrees, a borrower may "return" an item directly to the next swapper that has requested it, rather than back to its owner. The owner may specify that some items must always be returned to him or her.
  2. A swapper may request to borrow one of his or her own items that has been loaned, and that means it must be returned to him or her before it is passed on to any other swapper. An item cannot be directly "returned" to a swapper that does not belong to the same group as the current borrower--it must be returned to the owner in these cases.
  3. A swapper may replace a damaged or lost item and return it to the original owner. Replaced items earn the borrower an extra rating point.
  4. A swapper may decide not to lend an item anymore (or it may have been lost) by specifying a date beyond which the item is unavailable for swapping, and can cancel it from the list (without losing the history of that item's previous swaps).

Questions:

  1. What groups are there, and who belongs to each?
  2. How many items, of each media category, are available to borrow in each group?
  3. For each group, which items are currently borrowed, and by whom?
  4. For each group, which items are currently available to be borrowed (i.e., they're not already loaned-out.)?
  5. For a given swapper, what is the complete history of his or her swaps?
  6. For each swapper in each group, what is each swapper's rating point total?
  7. For each group, what are the most requested items? (Advertisers will want to know this so that they can sell copies of those particular items to swappers.)


Courtesy to Professor Dave Rogers. Original source of this project.