Question

Smart Driving School (SDS) was established recently. SDS has five branches in Canberra. SDS requires your team to design a database system to enable more smooth operation of the centre. The details of the branches of SDS are:

 

Branch name	Address
SDS Dickson	150 Camilla Way, Dickson, 2662, ACT
SDS Page	12 Page St, Page, 2614, Page, ACT
SDS Franklin	12 Franklin St, Franklin, 2912, Franklin, ACT
SDS City	12 Bunda St, 2601, City, ACT
SDS Bruce	12 Norman Fisher Crt, 2617, Bruce, ACT

 

SDS database will record and store the data about each of its clients and instructors including their first and last name, postal address and home address (street number, street name, suburb, post-code, city), sex and type of driving classes a client is enrolled in. The name (first and last name), contact telephone number and address (street number, street name, suburb, post-code, city) of client’s next-of-kin is also stored in database.

 

SDS offers several types of driving classes. Driving classes are managed under course development section of SDS. There exist several types of driving classes. These are namely Class A (Trucks), Class B (Mini Trucks) and Class C (normal passenger vehicles). A client can be enrolled in up to two different classes at any time. Each class type has different fees. SDS database will record and store the data about each of its classes.

 

Clients need to book for each class up to two weeks beforehand. The timetable and availability of each instructor is provided on SDS website.

 

When a client enrols at SDS he or she is assigned a client number and his/her details are recorded. Every client is provided with a client number and an ID-card as well as the rules of SDS and charges. Each class type has its own terms and conditions. A client can use his/her client number to book a class (using the online booking facilities).

 

SDS has several staff members in each branch. For each staff member the following data is stored in the database system: staff first and last name, staff number, position, sex, date of birth, name of the section and branch number he/she works in as well as each staff internal telephone number. Each branch has a manager. Detail of each manager for each branch is also stored in SDS database.

 

SDS has also several instructors in each branch. For each instructor the following data is stored in the SDS database system: staff first and last name, instructor number, position, sex, date of birth, type of driving classes that an instructor can teach (i.e. Class A, B or C), internal telephone number and branch number.

 

SDS consists of four sections. These sections are: instructor section, information and course development section, assessment and enrolment section, administration section. The information about each section is stored in the SDS database. The information about each section includes the section name, location, telephone and fax number. SDS clients can browse the SDS website to find out the details of all classes available for each week.

 

Instructors belong to the instructor section. Each instructor is limited to teach a maximum of two types of classes at any day. The instructor’s identity is established with their ID number and ID-card and it is used to retrieve the details of the classes that they teach. The details of each class (class number, Instructor ID number, date and time, details of the clients for each instructor) are stored in the SDS database. If a staff, instructor or a client loses his/her ID-card, he/she then contacts the SDS at the following telephone number: 1800800800. The staff at administration section once notified will deactivate the lost ID-card and a new ID-card is issued later. Issue of a new ID-card may take up to 24 hours. When a new card is issued the details of the staff in administration section that issued the card is recorded in SDS.

 

The details of all lost card stored in SDS database is: first name and last name of staff who issued the new ID-card, date and time that the card was issued and staff first and last name of the staff that has lost his/her card.

 

When a client is enrolled in a driving class, then the client should make a payment for his/her classes before he/she can take that class. The amount of payment depends on the type of class. The payment can be performed via the SDS website using a credit card. After each class, the instructor records the progress made by the client and notes the mileage of the car used for the driving class before and after the class.

 

Vehicle details used for each class and their millage is stored in SDS database. These details are: vehicle number, registration number, type, make and mode of vehicle, class number, client and instructor first and last name. SDS has a pool of cars and trucks that are adapted for teaching.

 

All payment details made by all clients for each class enrolment is stored in SDS database. The details of payments stored are: payment number, client first and last name, date of payment, amount paid, class number, class date, class time, instructor number.

 

For each payment made by a client a receipt is issued to the client. Receipt details are, client first and last name, receipt number, date of payment, amount paid, payment number. Receipt details are stored in SDS database.

 

SDS website also provides a class timetable and instructor availability facilities where a client can check the availability of a certain class with the details of the instructor teaching the class. The timetable of classes is stored in SDS database.