Bernards Tool Rental needs to keep track of its members and the tools it rents. Each tool
Question:
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
Expert Answer:
The question appears to be asking for the creation of a database schema to model the data and relationships for Bernards Tool Rental based on the prov... View the full answer
Modern Database Management
ISBN: 978-0133544619
12th edition
Authors: Jeff Hoffer, Ramesh Venkataraman, Heikki Topi
