solution
Draw a 3NF ERD for the following business case. You may make reasonable assumptions about cardinalities with clear textual clarifications. You need to label primary keys and foreign keys in your diagram.
Car Rental is a car rental company which has been founded recently in London. It currently has four branches opened in London, Manchester, Glasgow, and Edinburgh. Car Rental is planning to expand to Liverpool
in the future. A branch has an address and a telephone number. Each rental car must be associated with exactly one Car Rental branch. A rental car can be identified by its registration number and has the information about the model and the production year. Each employee
working at Car Rental has a unique tax file number (TFN), a name and an address. Car Rental employees consist of managers, mechanics, and customer service representatives. Every employee must work at exactly
one branch. A customer of Car Rental should provide driver license number, name, and a contact number. A customer can rent a car from a Car Rental branch and details about the rental such as the rental period and price should be recorded. If a customer is not satisfied
with the rental service provided, this customer may submit a complaint to a customer service representative who is identified by a unique service representative ID and thus a distinct reference number and detailed description of the complaint should be recorded. The manager, associated with a manager ID, of a branch will handle all the complaints with respect to this branch and has an urgent contact number in case of emergency. Mechanics working at a branch inspect the cars associated with the branch and record the condition and
inspection date. A car must be inspected at least two times per year.
Each mechanic has a certificate ID and a skill level, and might have another mechanic as the supervisor. The supervisor must have a higher skill level compared to the mechanics supervised by this supervisor.
Car Rental is a car rental company which has been founded recently in London. It currently has four branches opened in London, Manchester, Glasgow, and Edinburgh. Car Rental is planning to expand to Liverpool
in the future. A branch has an address and a telephone number. Each rental car must be associated with exactly one Car Rental branch. A rental car can be identified by its registration number and has the information about the model and the production year. Each employee
working at Car Rental has a unique tax file number (TFN), a name and an address. Car Rental employees consist of managers, mechanics, and customer service representatives. Every employee must work at exactly
one branch. A customer of Car Rental should provide driver license number, name, and a contact number. A customer can rent a car from a Car Rental branch and details about the rental such as the rental period and price should be recorded. If a customer is not satisfied
with the rental service provided, this customer may submit a complaint to a customer service representative who is identified by a unique service representative ID and thus a distinct reference number and detailed description of the complaint should be recorded. The manager, associated with a manager ID, of a branch will handle all the complaints with respect to this branch and has an urgent contact number in case of emergency. Mechanics working at a branch inspect the cars associated with the branch and record the condition and
inspection date. A car must be inspected at least two times per year.
Each mechanic has a certificate ID and a skill level, and might have another mechanic as the supervisor. The supervisor must have a higher skill level compared to the mechanics supervised by this supervisor.
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"
