Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    2

    Talking ERD Of Car Rental

    Hi~
    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    HOMEWORK!!

    no, we will not do it for you

    you must attempt it yourself, post your best effort, and then we might -- might -- offer suggestions for improvement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2006
    Posts
    2
    Sorry for not post my solution for this.
    here is my answer, but I am confusing about the "mechannic", and the parts related to maintenance
    Attached Thumbnails Attached Thumbnails dbans.gif  

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that diagram is fabulous!

    i really like it

    how did you scan the picture?

    it looks okay to me, in fact it looks good enough to attempt to populate with sample data

    nice job

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •