Consider the following scenario for a garage selling and servicing cars and answer the questions in parts (a) and (b) below.

Question

The following rules describe the garage in more detail. A customer engages with the garage either as a buyer or by having their car(s) serviced (or both).
i) A salesperson may sell many cars, but each car is sold by only one salesperson.

ii) Each salesperson or mechanic has a name and last name. Cars have a number plate, make and model as well as registration year. Customers have a first and last name as well as an address.

iii) A customer may buy many cars, but each car is bought by only one customer. A sale takes place on a specific date. iv) A salesperson writes a single invoice for each car he or she sells.

v) A customer receives an invoice for each car he or she buys.

vi) When a customer takes one or more cars in for repair or service, one service ticket is written for each car. A service takes place on a specific date and customers receive comments on what has been done.

vii) A car brought in for service can be worked on by many mechanics, and each mechanic may work on many cars. Each mechanic spends a certain amount of time on a job, called an assignment, and has an associated rate.
a) Draw an entity relationship diagram for the Car Dealership scenario using a suitable notation. Your answer must show the entities and their relationships. Cardinalities and optionality between entities MUST be shown. State any additional assumptions you are making.
b) Design a set of tables derived from the Entity-Relationship Diagram in part (a). Highlight ALL primary and foreign keys and show a few rows of sample data (no more than four rows per table).

Leave an answer

Sorry, you do not have permission to answer to this question .