Bernard’s Tool Rental needs to keep track of its members and the tools it rents. Each tool has a unique serial number that is used for tracking. Basic member information is stored when a person becomes a member of the tool rental club. There is no fee charged to become a tool rental member, however, members are assessed a fine if tools are not returned on time (a running total on fines is stored in the member record). When members bring tools to the counter, a rental record is created. Members may rent out as many tools as they like in one Rental#. Certain tools may never be rented. Obviously, a particular tool, over time, maybe rented out multiple times by different members or even the same member. It is also possible that certain members have not yet rented out any tools. Only one member can be associated with a particular rental.

When the member’s card is swiped at the beginning of rental the computer checks for outstanding fines. If there are outstanding fines, the member must first pay the total fines outstanding, and the amount of the cash collected is recorded in the rental record (there is no separate cash collection event). The cash paid is later deposited in one of four different bank accounts maintained by Bernard’s Tool Rental. For each tool included in the Rental#, the return date of the tool is recorded. Most tools are due five days later, but some tools that are in high demand are due one day later. The member is issued a receipt showing the tools that have been rented out to the member. Each rental is performed by a single employee, and it is necessary to keep track of who that person is. An employee can perform many such transactions, but newly hired clerks may not yet have performed any.

Members return tools periodically. They do so by simply dropping off tools at the tool return desk or outside via a locker. Members may be returning tools that were rented on different days; therefore a different return record is created for each tool returned. Employees check in returned tools and it is again necessary to keep track of the clerk who performed the check-in. The employee performing the check-in may not be the same as the one who performed the initial rental.

Returned tools are checked in with a scanning device, which then allows the system to immediately retrieve the tool’s Rental Number and to trace the date that the tool was rented out. If a tool is returned after its return date, the fine is calculated and added to the total fines in the member record. Total fines

are charged at the next rental by that member. Reports on tools checked out, tools checked in, and fines paid need to be generated on a daily basis.

The following is an explanation of fields that the database must capture. Make sure that all fields are placed in the final database schema. Each secondary field should be placed in the schema only once.

• Member# ‐ unique identifier of the member record

• MemberName

• MemberAddress

• FineTotal – fine amount that the member currently owes

• Emp# ‐ unique identifier of the employee record

• EmpName

• EmpAddress

• SerialNo# ‐ unique identifier of the tool rented

• Manufacturer

• Type – the type of tool rented

• Account# ‐ unique identifier of the cash account record

• AccountType – cash account type (i.e. “Return#g” or “operating”)

• BankName

• Balance – cash account balance

• Rental# ‐ unique identifier of the checkout record

• RentalDate – date of the Rental#

• CashAmtReceived – the amount of cash collected, if the member had outstanding fines

• RentalDueDate – due date of an individual tool

• Return# ‐ unique identifier of the return record

• return date – date of return

 

CASH 1,N TOOLS 1,1 1,1 O,N O, N 0,N RENTAL 1,1 O,N RETURN 0,N O,N O,N O,N 1,1 1,1 MEMBERS 1,1 1,1 EMPLOYEE