I have to make an ERD diagram using this scenario case and I'm not sure if I have all the required tables/fields in my ERD. Is my entities normalized up to 3NF? Are the relationships correct?
Background
The Brisbane Driving School was established in Brisbane in 1992. Since then, the School has grown steadily and now has several offices in most of the main suburbs of Brisbane. However, the School is now so large that more and more administrative staff members are being employed to cope with the ever-increasing amount of paperwork. Furthermore, the communication and sharing of information between offices is poor. The Director of the School, Dave Darcy, feels that too many mistakes are being made and that the success of the School will be short-lived if he does not do something to remedy the situation. He knows that a database could help in part to solve the problem and has approached you to help in creating a database system to support the running of the Brisbane Driving School. The Director has provided the following brief description of how the Brisbane Driving School operates.
The company has now employed over 120 staff. Around 100 of them are driving instructors. Over the years BDS has accumulated over 4000 customers. Around 100,000 lessons have been delivered and around 5000 driving tests arranged.
How the company operates
Each office has a Manager (who tends to also be a Senior Instructor), several Senior Instructors, Instructors, and administrative staff. All instructors should possess a valid Queensland Instructor licence. 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 first 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, a driving course is agreed and 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. The school will arrange to make the booking. To obtain a driving license the client must pass the road test. It is the responsibility of the Instructor to ensure that the client is best prepared for 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 Driving Test Centre. If a client fails to pass, the Instructor must record the reasons for the failure.
In this case study you have to design a database that incorporates all the above information and records, and design a user-friendly user interface for data entry staff and driving instructors to update information. Driving instructors are allowed to access and update data for those lessons and driving tests under his/her own name. They are allowed to see their learners’ details but not allow to change make any changes. The data entry staff and the branch manager can see all data and have all rights.Here's a screenshot of the ERD diagram http://www.lovelyanime.com/erd.gif
