INTERMEDIATE INTERMEDIATE II – DATABASE - DRIVING SCHOOL A Driving School stores the details of their instructors and their customers on a database. The database is used used to find the most suitable driver 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 Name M/F Instructor Aress Instructor To!n Instructor "#one Num$er Car Ma'e Car Moe) Transm%ss%on T,e
Customer Name Customer Aress Customer To!n Lesson Date T%me o& Lesson Len(t# o& )esson *#rs+
USE !"ED #A#E$ T% &%'#ETE TAS(S )*+.
TAS( ) rite down formally the E"T!T!- and attributes. "ormalise the Entity to )". "ote the #rimary (ey and any oreign /ey /ey fields. Remem$er ,ou !%)) #a.e to ma'e sure t#ere %s a &%e) t#at )%n's $ot# ta$)es to(et#er so t#at ,ou can &orm a re)at%ons#%- $et!een t#em/
&hec/ your solution with the teacher before continuing.
TAS( 0 &reate a Data Dictionary for both tables showing the field names, data ty1es and any validation. &hec/ your solution with the teacher before continuing.
TAS( + &reate an E*$ Diagram showing the relationshi1 between the Entities.
INT II DATABASE
DRIVING SCHOOL
PAGE 1
TAS( 2 3a4 &reate the database using your software 1ac/age. 3b4 &reate the relationshi1 between the tables. 3c4 &reate a user*friendly interface to enter the data 5 ma'e sure t#at t#e a)) names are surname &%rst &o))o!e $, %n%t%a) eg: ambergini A Data &or Dr%.%n( Instructor0s Ta$)e !nstructor "ame A ambergini
'6
Address
Town &oo/ton
#hone &ar "umber 'a/e 8++)7299; (issan
&ar 'odel (uddle
Transmission Ty1e Automatic
'
A Smith
'
D un/ D @ill @ Singh
'
ones 'ac(enBie
'
" Ahmed # Destrian
'
S aBlani
'
7 &arnival &rescent )0 The Smithy 77 Daisy ane > #o11y ane 2
800)22+7;
=ord
Arran
'anual
&oo/ton @agueton &oo/ton
8++)779)> 8++)77;)0+ 8++)7799>
?auxord &alastra A? + Series ?auxord &avacort
&oo/ton
8++)2207; A? )7 Series 800))00;; ?auxord &avacort
Automatic 'anual
@augeton @augeton
8++)7;;; 8++)77;;
(issan =ord
Automatic 'anual
@augeton
8++)7+7;
?auxord &alastra
@uddle ight
'anual 'anual 'anual
'anual
Data &or Customer0s Deta%)s Ta$)e "ame
Address
Town
esson Date
A
)0 $obb &ourt ) $ubbani ay 22 ish ane ; The @ighway
@augeton @augeton @augeton
A Sum1
@augeton
A Teen
)> East End Drive 9 'ilton ay
TodayCs date TodayCs date TodayCs date TomorrowCs date TodayCs date
A" !sland
>> $amsay
@augeton
INT II DATABASE
Time of lesson 8>:+8:88 89:+8:88 )8:88:88 )7:88:88
ength of lesson 3hrs4 ).88 ).88 0.88 ).78
!nstructor
)0:88:88
8.7
S aBlani
TomorrowCs date
89:88:88
).78
'ac(enBie
TomorrowCs date
)2:88:88
8.7
S aBlani
DRIVING SCHOOL
PAGE 2
" Ahmed " Ahmed # Destrian A Smith
<
)+ Summer Street ) ashington Drive ++ Academy $oad )) $amsay
TodayCs date
)):8:88
).88
A Smith
TomorrowCs date TomorrowCs date TodayCs date
)>:88:88
).88
A Smith
);:88:88
).88
# Destrian
):88:88
).88
S aBlani
7 ashington Drive 0+ South Street 08 ish ane
); #ress ane +> South Street + aterloo Avenue ) $amsay
@augeton @augeton
)>:88:88
).88
'ac(enBie
TomorrowCs date TodayCs date
89:88:88
).88
A Smith
&oo/ton
TodayCs date
08:88:88
).88
ones
&oo/ton @augeton
TodayCs date TomorrowCs date TodayCs date
89:88:88 )):+8:88
).88 8.7
ones S aBlani
)+:88:88
).88
@ Singh
TomorrowCs date
);:+8:88
).88
" Ahmed
&oo/ton @augeton
&reate a $e1ort for both tables showing all fields. &hoose a suitable layout for your re1ort, eg we do not wish to 1rint one record on each 1age so try to fit as many records as 1ossible onto one 1age. ive your re1ort a suitable heading and 1ut your name on as a footer. #rint. 1uer%es an Man%u)at%ons
&reate new re1orts for each search result, remember to give them a11ro1riate names and include your name as a footer. a4
A customer from &oo/ton wishes a first lesson. %btain a list of the instructors who live in &oo/ton. Sort the results by surname.
b4
The instructor " Ahmed, has fallen ill. @is customers need to be contacted in order to reschedule the lesson. %btain a list of these customers. Sort the results by surname.
c4
A lady from @augeton calls. She wishes to be taught by a female instructor. ind a list of suitable instructors. Sort the results by surname.
INT II DATABASE
DRIVING SCHOOL
PAGE 3
d4
" AhmedCs illness means he has to give u1 his =ob. $emove his name from the instructorCs file. @is customers have been 1assed to # Destrian. Amend the customer file to show this.
Two reFuests have been received for lessons. e4
'rs ones needs an instructor with a ?auxord or a =ord car. ind out the instructors she could use. Sort the results by surname.
INT II DATABASE
DRIVING SCHOOL
PAGE 4
