The problem scenario

You are asked to develop a database for a local driving school. It is to store information about its clients, instructors, cars and bookings for driving lessons.

Each client has a unique ID, name, gender, date of birth, address, contact phone number, and a provisional driving license number. A client may book individual lessons with a particular instructor. Each lesson lasts for an hour. A client may change to a different instructor at any stage.

A client may apply for a driving test at the nearest test centre. Each centre has a unique ID, name, address and contact phone number. A client may make one or more attempts to pass the test. For each test, the instructor should be available to drop off and pick up the client before and after the test at the centre. If a client fails a test, the instructor must record the reasons for failure.

An instructor has a unique ID, name, gender, contact phone number, address and a valid driving license number.

Each car has a numeric identifier, a registration number and model. A car is allocated to a specific instructor by the school.

Task

1. Design a conceptual data model for the given scenario.

a) Construct an ER diagram in UML.

The diagram should show all entities, attributes of each entity, and relationships between the entities with appropriate multiplicity constraints.

b) State any assumptions necessary to support your design.

2. Produce a logical data model for a relational database.

a) Map your conceptual data model (Task 1) to a set of relational schema, indicating clearly the primary keys and any foreign keys introduced.

b) For each foreign key, specify whether a null value would be allowed. Justify your choice.

c) State any other required constraints for your logical data model.

Academic Honesty!
It is not our intention to break the school's academic policy. Projects posted are only used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly fill out the form. Please provide a valid email address and we'll get back to you in less than 24 hours. We will be sending an invoice through PayPal upon confirmation. We are a non profit organization however we need an amount to keep this organization running, and to be able to complete our research and development.