Project Deliverable #2: Sample Entity-Relationship Model
This model is intended to capture the requirements for the Swapaholic.com website.
Refer to the project proposal document for the original client requirements.
Convert the Borrows relationship into an associative entity:
A future, current, or past loan of a single item to a single swapper. Future loans are known as "requests".
This entity is involved in the following relationships:
- An item may be borrowed many times, or none at all, but each borrow request must be for a single item.
- Each borrow must be for a single
swapper, and a swapper may make many borrows, or none at all.
- The "Returned" relationship reads as: "An item is returned in a specific condition, or has not yet been returned, and a returned condition may apply to many borrows, or none at all."
- requested_date: The date and time that the request
was first made. If the item is already loaned, then this value is used
to determine which borrower is next in line.
- loaned_date: The optional date that the item was loaned, if it has already been loaned to this swapper.
- returned_date: The optional date that the item was returned, if it has already been returned to the owner or the next borrower in line.
A reference entity that records the possible conditions in which an item may be returned.
- condition: No Damage, Minor Damage, Major Damage or Lost, Replaced.
- points: The number of points applied to the
borrowing swapper's rating for the condition in which the item was
returned: a negative value means a deduction.
A group that a swapper must join to swap items with the group's other members.
- is_invitation_only: True, if a swapper must be invited to this group before joining.
An item that can be swapped amongst members of a group.
The "Owns" relationship reads: "An item owned by a single swapper, and a swapper may own many items, or none at all."
The "ListedAs" relationship reads: "An item is of a
single media category, and a category may apply to many items, or none at all."
- media_id_no: The optional media-specific identification number of the item (e.g., ISBN for books, serial numbers for DVDs).
- must_return: True, if the owner of the item requires that it be returned to him or her after each loan.
- available_date: The date that the item is first available for swapping. An item cannot be borrowed before this date.
- unavailable_date: The optional date that an item
is no longer available for swapping. If specified, an item cannot be
borrowed on or after this date.
A reference entity that records the possible types of media categories.
- category: Book, VHS Video, DVD Video, Audio CD, etc.
The "Belongs" relationship reads: "A swapper may join many groups, or none at all, and a group must contain at least one swapper, and possibly many." The join_date attribute of this relationship specifies the date on which a swapper joined a specific group.
- email: The e-mail address of the swapper. Swappers must not share e-mail addresses.
- name: The swapper's full name.
- password: The hashed form of the swapper's password used to access swapaholic.com.