ER Modelling Question

Question

I have the following question:

Using only binary relationships, construct an entity relationship diagram for the following description. Include entity labels, primary key fields, relationship labels, and the multiplicities on relationships.

"A company runs several car repair and service garages, each having its own unique number (gargNo). When a car owner contacts a garage, their details are recorded and they are assigned an ownerNo. Their car is also registered with that garage and is assigned a reference number (carNo). An owner may own one or more cars, but a car can only be registered with one garage. When a car is booked into a garage, a service plan is drawn up for it. The service plan may be unique for a particular car (eg, cure squeaking windscreen wipers) or it may be one used for many cars (eg, a standard 60000 mile service). Any service plan can consist of one or more operations (change the oil, dismantle the wiper motor, etc). Each type of operation on a service plan has a unique number (operationNo).

This is my answer:

For all Database veterans, does this look ok to you?

Also any other comments feedback would be appreciated...

NOT HOMEWORK

EDIT - Why do people keep editing posts but making no changes?

Answer 1

Based on the problem statement, I have come up with the following:

I used generic fields like Address, OwnerDetails, etc. for the sake of simplicity.

Edit: Many to Many between Service plan and Operation explained:

The Operation "Change Oil", is a part of the Service Plans "30K maintenance", "60K Maintenance" and "Oil change".

Of course, a Service plan for "30K maintenance" and "60K Maintenance" has multiple Operations (Change Oil, Refill Brake fluid, Check Tire Pressure, Balance and Rotate Tires).

Therefore, the relationship between Service Plan and Operation is a many-to-many relationship.

This structure is a template that can then be applied to a VehicleService instance.

Answer 2

Based solely and entirely on the requirements given, disregarding all possible real-world complications (such as what happens when an owner moves their car service from one garage to another):

I would leave out company. There’s only the one ever mentioned, and no indication that we are recording data for multiple companies.

The relationship between car owner and garage is through the car. There is no direct relationship between car owner and garage. (Given multiple garages, ensuring a given multiple-car owner appears once in the system is going to be tricky to enforce.)

The relationship between car and garage should perhaps be “registered at”. A strict reading implies that the car gets associated with the garage upon owner contact, not upon bringing in for service.

You need entity ServicePlanType [SPT]. Most SPTs are pre-defined, and multiple cars would use a given SPT (60,000 mile tuneup). Additional SPTs would be added if, when, and as required. An argument could be made for “standard” vs. “ad hoc” subtyping, but I think they’d be so similar (based on Operations) that this isn’t called for. Then:

• Service plan relates to one car, and to one service plan type
• Service plan relates to one service plan type
• Service plan type relates to zero or more service plans (list of standard plans)
• Service plan type relates to one or more operations (all operations must be defined)

Operation may relate to zero or more service plan types. Given the need for ad hoc service plans, there may be a need for operations that do not initially belong to any given set service plan. (That or they get added as needed, which may be acceptable. My sister’s gerbil got escaped once on the way home from school, and they had to disassemble part of the car to get it out. No charge, maybe they didn’t have “extract gerbil” it in their database.) (I am so not making that up.)

I would not relate sevice plan type or operation with garage. Presumably if one garage of the company can do it, they should all be able to do it, even the ad hoc ones.

You do not need to relate service plan with garage, as the car the service plan is for is related with garage. With that said, it might be good to do so when it comes time for physical implementation. Also, if a car is later brought into a second garage the car-to-garage relationship changes, and without the service plan to garage relationship, you lose track of who did the earlier work. Properly, I’d think you’d want to model owenr to car to service plan to garage, but they specifically spelled out “car to garage”. Raise these questions, see what the business owner says.