I'm designing a database for a Motoring School and I've been given the following information:
• Pupils book either a single lesson or a course of lessons. Pupils are allocated a particular instructor when they register with the school. Sometimes, pupils ask for their instructor to be changed. A record of this should be kept.
• Occasionally a different instructor may actually take a particular lesson.
• Pupils also book mock driving tests. These are booked with local examiners, who may or may not also be instructors. Booking and paying for mock tests is treated the same as for lessons.
• Tests may be practical or theoretical. Many pupils can attend a particular theory lesson or test, while a practical session is restricted for an individual pupil. Sometimes pupils need more than one mock test which can be considered as lessons.
• Usually, the pupils pay in advance. Lessons, payments and pupils' details are entered onto a Pupil Record Sheet, when they first register. This sheet is kept at the office, and continually updated. A sample copy is attached.
• Pupils sometimes miss a lesson they have booked. If they miss the lesson with good cause and give a 48hr notification, the lesson is deferred until a later date and no payment is due. If the pupil simply fails to notify 48hr before the lesson or attend a lesson, the payment is due.
I'm stuck and confused in defining the entities in order to design the ERD and the Schema. Can someone guide me what Entities should be included and maybe little help on ERD and Schema.
I've decided to use MS-Access to create the database.
I really appreciate you help.