I am doing the ERD of the following case. But I am confuse about the maintenance and parts problems.
Can anyone do the ERD for me ?
Thanks A LOT
Happy Car Rental (HCR) is a company which rent cars to long term customers. The customers do not pay each time they rent a car; monthly bills will be sent to them.
Using reservation forms, customers may reserve vehicles by fax. The reservation form includes the customer number, customer name, contact information, expected departure date, expected return date, vehicle type required, and destination. After checking the availability of the required vehicle, the reservation information will be recorded if vehicle is available for the requested period. When the customer arrives to pick up the vehicle, (s)he must fill in a usage form to log the vehicle out. The usage form includes the customer number, customer name, the vehicle's identification, the odometer readings at the start and end of the trip, maintenance complaints, if any. The data in the usage form will be filled in when the customer picks up the vehicle, as well as when (s)he returns it.
Customers are billed at a mileage rate based on the vehicle type (sedan, station wagon, panel truck, minivan, minibus) used. At the end of each month, a bill will be prepared and sent to the customers. The bill contains the usage and charge detail of the vehicles used for that month, the date issued, and the due date of the payment. The customers pay the bill to the accounting department. The payment processing is out of the scope of this analysis. However, after a payment is received for a bill, the status of the bill will be changed to 'paid'.
All vehicle maintenance is performed by HCR itself. Each time a vehicle requires maintenance, a maintenance log entry is completed on a pre-numbered maintenance log form. The maintenance log form includes the vehicle identification, the initial log entry date, the date on which the maintenance was completed, and the identification of the mechanic who released the vehicle back into service. (Only mechanics who have an inspection authorization may release the vehicle back into service.)
Each maintenance log form contains separate lines for each maintenance item performed, the parts used, the identification of the mechanic who performed the maintenance item, and the date that the maintenance item is completed. Some maintenance items may use many parts, but some maintenance items may not require any part. One maintenance request may contain many maintenance items. For example, a maintenance request may require two maintenance items to be performed, one for changing the oil filter and the other for replacing the windshield wiper. When all the maintenance items have been completed, the maintenance log form's completion date is filled out, and the mechanic who releases the vehicle back to service is recorded.
HRC maintains a parts inventory, including oil, oil filters, air filters, belts of various types, windshield wiper and so on. The parts inventory is monitored to monitor parts usage and to re-order parts that reach the "minimum quantity on hand" level. To track parts usage, the parts manager requires each mechanic to sign out the parts that are used to perform each vehicle's maintenance; the parts manager records the maintenance log number under which the part is used.
A detailed parts usage report, which list out the quantity used and the remaining inventory for each part, is prepared at the end of each month. Moreover, an on-demand vehicle maintenance summary, which list out all the maintenances received and parts used within a specific period for a specific vehicle, is prepared upon the request of the HCR manager.