The EasyDrive School of Motoring Case Study

The EasyDrive School of Motoring was established in Kuala Lumpur in 1992. Since then, the School has grown steadily and now has several offices in most of the main cities in Malaysia. However, the School is now so large that more and more administrative staffs are being employed to cope with the ever-increasing amount of paperwork. Furthermore, the communication and sharing of information between offices, even in the same city, is poor. The Director of the School, David Tao, feels that too many mistakes are being made and that the success of the School will be short-lives if he does not do something to remedy the situation. He knows that a database could help in creating a database system to support the running of the EasyDrive School of Motoring. The Director has provided the following brief description of how the EasyDrive School of Motoring operates.

Each office has a Manager (who tends to also be a Senior Instructor), several Senior Instructors, Instructors, and administrative staff. The Manager is responsible for the day-to-day running of the office. Clients must first register at an office and this requires that they complete an application form, which records their personal details.

Before the lesson, a client is requested to attend an interview with an Instructor to assess the needs of the client and to ensure that the client holds a valid provisional driving license. A client is free to ask for a particular Instructor or to request that an Instructor be changed at any stage throughout the process of learning to drive.

After the interview, the first lesson is booked. A client may request individual lessons or book a block of lessons for a reduced fee. An individual lesson is for one hour, which begins and ends at the office. A lesson is with a particular Instructor in a particular car at a given time. Lessons can start as early as 8am and as late as 8pm. After each lesson, the Instructor records the progress made by the client and notes the mileage used during the lesson.

The school has a pool of cars, which are adapted for the purposes of teaching. Each instructor is allocated to a particular car. As well as teaching, the Instructors are free to use the cars for personal use. The cars are inspected at regular intervals for faults.

Once ready, a client applies for a driving test date. To obtain a full driving license, the client must pass both the practical and theoretical parts of the test. It is the responsibility of the Instructor to ensure that the client is best prepared for all parts of the test. The Instructor is not responsible for testing the client and is not in the car during the test but should be available to drop off and pick up the client before and after the test at the Testing Center. If a client fails to pass, the Instructor must record the reasons for the failure.

The Director has provided some exs of typical queries that the database system for the EasyDrive School of Motoring must support.

(a) The names and the telephone numbers of the Managers at each office.
(b) The full address of all offices in Kuala Lumpur. 
© The names of all female Instructors based in the Kuala Lumpur office.
(d) The total number of staff at each office.
(e) The total number of client (past and present) in each city.
(f) The timetable of appointments for a given Instructor next week.
(g) The details of interviews conducted by a given Instructor.
(h) The total number of female and male clients (past and present) in the Kuala Lumpur office.
(i) The numbers and the name of staff who are Instructors and over 55 years old.
(j) The registration number of cars that have had no faults found.
(k) The registration number of the cars used by Instructors at Kuala Lumpur office.
(l) The names of clients who passed the driving test in January 2006.
(m) The names of clients who have sat the driving test more than three times and have still not passed.
(n) The average number of miles driven during a one hour lesson.
(o) The number of administrative staff located at each office.

1. Create an Entity-Relationship (ER) model of the data requirements for the EasyDrive School of Motoring case study. Note: if necessary, use the additional concepts of the Enhanced Entity-Relationship (EER) model. State any assumptions necessary to support your design. Specify the cardinality ratio and participation constraint of each relationship type