Exercise: auto repair

You are designing a database for an automobile repair shop. When a customer brings in a vehicle, a service advisor will write up a repair order. This order will identify the customer and the vehicle, along with the date of service and the name of the advisor.

A vehicle might need several different types of service in a single visit. These could include oil change, lubrication, rotate tires, and so on. Each type of service is billed at a pre-determined number of work hours, regardless of the actual time spent by the technician. Each type of service also has a flat “book rate” of dollars-per-hour that is charged.

Design a database that shows the information described by completing the steps below.

  1. Start by identifying and describing each class in English.
  2. Continue by drawing the UML class diagram by drawing the classes, but avoid filling in the attributes for now, just name the classes.
  3. Next, determine what relationships exist between the classes you identified. Draw each association in the diagram including the multiplicity constraints and describe in English (both directions) to make sure you've correctly modeled the information given.
  4. Now complete each class by filling in the attributes and their corresponding data type.
  5. Map the UML class diagram to the relational model by drawing the relation scheme diagram. Be sure to model all constraints such as any non-null attributes, primary key, and foreign key.

The solution to this exercise may be discussed in class by your instructor or posted online at a later date.