Let's say that you own a vast collection of items or things, such as books, magazines, DVDs, yard tools etc., that your friends often borrow. It has been quite difficult to keep track of things that you own and remember who has borrowed what stuff. You call your friends and sometimes they either deny or don't "recall" that they have ever borrowed the item from you. You would like to create an app that can keep track of your things/items and who has borrowed what items. You would also like to store some personal information about the borrowers so that you could contact them when necessary (e.g. an expensive Blu-ray disc not returned for a month). This would include the Name, Address, email address and Phone number of the borrower.

For the items, it would be useful to store the Item Description, Item Category (e.g. Book/DVD/Blue-Ray/Magazine/Tools), Price Paid, Item Condition, and Item Acquired Date. For the borrowed items, it would be important to log the date the item was borrowed (Date Borrowed) and (if ever!) the date it was returned (Date Returned), along with the condition (Item Condition) in which the item was returned (hopefully the same condition it was when it was borrowed!). If an item was returned late, a fine will be imposed (Item Fine). The fine depends on the item category (e.g. some types of items have a higher fine). If the total fine (Total Fine) associated with a borrower exceeded certain amount you will suspend their borrowing privileges until the fine is paid off.

The app will support the following business process:

Borrowing an item

Returning an item

When a new item is acquired, the app should support entering the data for this item. For the borrowers, the app can help remind them when an item is due or if it is past the due date. Borrowers can also check if they have any fines and make payments using an online payment system.

As a user you would also like to run analysis to identify which items have not been returned past the due date, which items have been borrowed frequently, which items have never been borrowed, what is the total fine for each borrower, who routinely returns items late, the number of items that are in excellent (or bad) condition and so forth.

Background

While an app needs to be developed, for this assignment you will focus only on the database portion as the app will need to interact with this database. While this database is primarily supporting the operational aspects (borrowing, returning items), it could also providing some support for the analytical aspects (helping perform some basic analysis mentioned above) of running your "business".

Before building this database, you need to have a good understanding of what types of data this database should be storing and how the user(s) will use the database e.g. what types of queries (questions) they may have and how the data provided will meet those needs.

Your Task

You are going to begin by creating an ER Diagram (conceptual data model) which can

Identify the relevant entities and their attributes

Show the relationship between the relevant entities along with appropriate cardinality constraints (e.g. one-many, mandatory/optional).