Question

You were requested to design a database to store the data concerning a large mechanic shop. Your database has to store the following: For each car, you want to store the make, model, year, color, engine info, license plate, VIN (vehicle identification number), and a field for general notes. Additionally, for each car, you need to keep information about the owner. For each owner, you need to store the client ID, the name, address information, phone number, and email. An owner may have multiple cars. For simplicity, suppose that a car has only one owner. The database also has to keep information about the mechanics that work on the shop. For each mechanic, you need to store his or her mechanic ID (assigned by the shop and which is unique), name, address information, and phone number. A car goes to the shop multiple times, and the database needs to store all relevant information about each service. For each service, you need to store the date, the car’s mileage, number of hours the service took, and a field for general comments (like the kind of work was done in the car, etc.). For each service, the database also needs to store the mechanic that performed the service. Suppose that only one mechanic is responsible for each service. Assume you do wish to use a separate zip code table in the database.

a) Design a database to hold all this information. You may create synthetic keys as necessary or convenient. Hand in a database design outline and an ER diagram. Advice: First, forget the zip code table: create the tables and the relationships assuming you do not have a zip code table. Then, when you think what you have is correct, bring the zip code table: for every table that needs an address, “link it” to the zip code table with a relationship.

Answer 1

Below is the ER diagram of the given scenario. It consist of 5 entities connected via 5 relationships. Each entity has certain set of attributes as mentioned in the description and primary key of each entity is mentioned as underlined attribute.

The zipcode table is created separately and is lined with Owner and Mechanic table.

Cardinality is shown using crow's foot notation in ERD.