INTERMEDIATE II – DATABASE - DRIVING SCHOOL

A Driving School stores the details of their instructors and their customers on a database. The database is used to find the most suitable driver for the customer, and to record the details of the next lesson the customer is having.

The information stored by the Driving school is as follows:

Instructor NameCustomer Name

M/FCustomer Address

Instructor AddressCustomer Town

Instructor TownLesson Date

Instructor Phone NumberTime of Lesson

Car MakeLength of lesson (hrs)

Car Model

Transmission Type

USE LINED PAPER TO COMPLETE TASKS 1-3.

Task 1

Write down formally the ENTITIY and attributes.

Normalise the Entity to 1NF. Note the Primary Key and any Foreign key fields.

Remember you will have to make sure there is a field that links both tables together so that you can form a relationship between them.

Check your solution with the teacher before continuing.

Task 2

Create a Data Dictionary for both tables showing the field names, data types and any validation.

Check your solution with the teacher before continuing.

Task 3

Create an E-R Diagram showing the relationship between the Entities.

TASK 4

(a) Create the database using your software package.

(b) Create the relationship between the tables.

(c) Create a user-friendly interface to enter the data – make sure that the all names are surname first followed by initial eg: Lambergini A

Data for Driving Instructor’s Table

Instructor Name / M/F / Address / Town / Phone Number / Car Make / Car Model / Transmission Type
A Lambergini / M / 5 Carnival Crescent / Cookton / 0331549976 / Kissan / Kuddle / Automatic
A Smith / M / 12 The Smithy / Blairton / 0221443567 / Fjord / Arran / Manual
D Funk / F / 55 Daisy Lane / Cookton / 0331559178 / Vauxord / Calastra / Manual
D Hill / M / 78 Poppy Lane / Hagueton / 0331556123 / ALV / 3 Series / Manual
H Singh / F / 4 Brown Street / Cookton / 0331559978 / Vauxord / Cavacort / Manual
L Jones / M / 99 Elgar Road / Cookton / 0331442567 / ALV / 15 Series / Automatic
L MacKenzie / F / 34 Jubilee Crescent / Blairton / 022112266 / Vauxord / Cavacort / Manual
N Ahmed / M / 12 Pansy Close / Haugeton / 0331566677 / Kissan / Huddle / Automatic
P Destrian / F / 15 Mozart Avenue / Haugeton / 0331556677 / Fjord / Wight / Manual
S Fazlani / M / 39 Sunflower Street / Haugeton / 0331573567 / Vauxord / Calastra / Manual

Data for Customer’s Details Table

Name / Address / Town / Lesson Date / Time of lesson / Length of lesson (hrs) / Instructor
A Bell / 12 Robb Court / Haugeton / Today’s date / 08:30:00 / 1.00 / N Ahmed
A Doughnut / 1 Rubbani Way / Haugeton / Today’s date / 09:30:00 / 1.00 / N Ahmed
A Scott / 44 Fish Lane / Haugeton / Today’s date / 10:00:00 / 2.00 / P Destrian
A Sellerator / 6 The Highway / Blairton / Tomorrow’s date / 15:00:00 / 1.50 / A Smith
A Sump / 18 East End Drive / Haugeton / Today’s date / 12:00:00 / 0.75 / S Fazlani
A Teen / 9 Milton Way / Blairton / Tomorrow’s date / 09:00:00 / 1.50 / L MacKenzie
AN Island / 88 Ramsay Bay / Haugeton / Tomorrow’s date / 14:00:00 / 0.75 / S Fazlani
B Bailey / 13 Summer Street / Blairton / Today’s date / 11:0:00 / 1.00 / A Smith
B Clinton / 1 Washington Drive / Blairton / Tomorrow’s date / 18:00:00 / 1.00 / A Smith
C Lutche / 33 Academy Road / Haugeton / Tomorrow’s date / 16:00:00 / 1.00 / P Destrian
|INA Bakk-Seat / 11 Ramsay Bay / Haugeton / Today’s date / 17:00:00 / 1.00 / S Fazlani
K Patterson / 5 Washington Drive / Blairton / Tomorrow’s date / 18:00:00 / 1.00 / L MacKenzie
L Robb / 23 South Street / Blairton / Today’s date / 09:00:00 / 1.00 / A Smith
M Brakk-P’Dal / 20 Fish Lane / Cookton / Today’s date / 20:00:00 / 1.00 / L Jones
M Lewinski / 16 Press Lane / Cookton / Today’s date / 09:00:00 / 1.00 / L Jones
M M’ouse / 38 South Street / Haugeton / Tomorrow’s date / 11:30:00 / 0.75 / S Fazlani
N Rafy / 3 Waterloo Avenue / Cookton / Today’s date / 13:00:00 / 1.00 / H Singh
T Bahagg / 1 Ramsay Bay / Haugeton / Tomorrow’s date / 16:30:00 / 1.00 / N Ahmed

Create a Report for both tables showing all fields. Choose a suitable layout for your report, eg we do not wish to print one record on each page so try to fit as many records as possible onto one page. Give your report a suitable heading and put your name on as a footer. Print.

Queries and Manipulations

Create new reports for each search result, remember to give them appropriate names and include your name as a footer.

a)A customer from Cookton wishes a first lesson. Obtain a list of the instructors who live in Cookton. Sort the results by surname.

b)The instructor N Ahmed, has fallen ill. His customers need to be contacted in order to reschedule the lesson. Obtain a list of these customers. Sort the results by surname.

c)A lady from Haugeton calls. She wishes to be taught by a female instructor. Find a list of suitable instructors. Sort the results by surname.

d)N Ahmed’s illness means he has to give up his job. Remove his name from the instructor’s file. His customers have been passed to P Destrian. Amend the customer file to show this.

Two requests have been received for lessons.

e)Mrs Jones needs an instructor with a Vauxord or a Fjord car. Find out the instructors she could use. Sort the results by surname.

INT II DATABASEDRIVING SCHOOL PAGE 1