Unit 6 exercises. Relationships


If Access is not open, open it in order to be able to carry out the following exercises.

 

Exercise 1: Cars

1 Open the Cars database from My exercises folder.

2 Add a Numeric field Client to the Sold cars table. This field will tell us which client has bought the car.

3 Add a Text field Car, Size 7, in the Services table which will tell us which car (of the Sold Cars) corresponds the service.

4 Create appropriate relationships between tables.

5 Introduce the following data into Sold cars:

License Make Model Colour Price Extras Client
V2360OX Chevrolet Cobalt Blue 12990 Electric aerial 100
V1010PBFord Focus White 13995  101
V4578OBFord Fusion Black 17900 Air conditioning 105
V7640OU Audi A4 Black 28960 Airbag225
V3543NCFord Taurus Red 21595  260
V7632NX Audi A3 Red 25600 Air conditioning, Airbag 289
V8018LJFord Fusion Blue 17850 Electric wipers 352
V2565NB Chevrolet Malibu White 16990  390
V7642OUFord Focus White 13995  810
V1234LCAudi A3 Green 27000 Air conditioning 822
V9834LH Chevrolet Impala Red 20990  860

 

6 Introduce the following data into the Services table:

Service Number Oil Change Filter Change

Revise
Brakes

Other Car
1 Yes NoNo Lights service V7632NX
2 Yes Yes No Change washers V7632NX
3No Yes Yes Repair alarm V4578OB
4No Yes Yes Adjust panel V2360OX
5 Yes Yes Yes Change washers, fix alarm V2565NB
6NoNo Yes Change interior light V7640OU
7 Yes Yes Yes  V2565NB
8NoNoNo V8018LJ
9 Yes No Yes Lights service V3543NC
10No Yes No Repair alarm V8018LJ
11NoNoNo V3543NC
12

Yes

Yes Yes  V1234LC
13No Yes No Change washers V9834LH
14No Yes No V1010PB

 

7 Close the database.

 

Exercise 2: Clinic IV.

1 Open the Clinic database from My exercises folder.

2 Add to the Entries table a numeric Patient field with Field Size Integer (this field will serve to show us which client corresponds the entry) and whitout Default value (remember what we said about numeric related fields!), and a text field (Size 5) named Doctor (this field will serve to know which doctor to entrust the entry to).

3 Create the appropriate relationship between the tables.

4 Introduce the following data into the Patients table.

 

Patient code Patient
Name
Patient
Surname
Patient
Address
Patient
City
Patient
Zip code
Patient
Birth
100 Joe Green 121 Cedar Ave Dallas 78600 03/31/75
102 David Smith 8 Main st. Austin 78767 10/30/47
103 David Buttom 34 Manor rd Austin 78767 06/11/87
110 Joe Beckam 8 Steel st. Dallas 78600 17/08/36
120 Sam Potter 1 Cedar Ave Dallas 78600 12/04/50
130 Joe Taylor 8 Steel st. Dallas 78600 01/23/32
131 Peter Green 45 Main st.. Austin 78767 12/08/90
140 Joe Williams 8 Main st. Dallas 78600 01/25/58
142 Joe Smith 14 Manor rd Austin 78767 03/25/58
200 David Lennon 18 Steel st. Dallas 78600 12/01/73
201 Sam Buttom 8 Steel st. Dallas 78600 05/05/55
207 Joe Weasley 11 Cedar Ave Dallas 78600 12/07/90
220 Johnny Granger 38 Steel st. Dallas 78600 07/19/5
231 Joe Sun 89 Main st. Dallas 78600

6/13/40

240 Andrew Smith 25 Main st. Austin 78767 02/07/85
300 Sam Beckam 64 Manor rd Austin 78767 05/05/77

 

5 Introduce the following data to the Doctors table.

Doctor code Doctor
Name
Doctor
Surname
Doctor
Speciality
ASP Anthony Smith Pediatric
RTP Rose Taylor Psychiatry
SSG Sam Sanchez General
PMP Peter Martin Pediatric
ASR Anne Smith Radiology
RMA Rose Martin Analysis
BTI Bart Taylor Intensive
API Anne Pons Intensive
SFS Sam Flanders Ophtamology
BSO Bart Smith Ophtamology
RRG Rose Red Gynecology

6 Introduce the following data to the Admissions table.

 

Entry
Number

Entry
Room

Entry
Bed
Entry
Date
Patient Doctor
1101A 04/23/98 300 RTP
2105A 05/24/98 103 RTP
3125B 06/15/98 300 RTP
4204B12/09/98120 SSG
5205B12/10/98100 SSG
6204A04/01/99102 SSG
7201A01/02/99240 SSG
8201A02/04/00110 SSG
9305A03/05/00 131 API
10304B12/05/00201 BTI
11306A13/05/00 201 API
12303B 06/15/00 220 BTI
13302A 06/16/00 131 BTI
14504B 06/30/00 130 BSO
15504B02/07/00231 SFS
16405B05/07/00 207 PMP
17401A08/08/00 220 PMP
18408B10/08/00 207 ASP
19504A12/08/00120 BSO
20509B 08/20/00 240 SFS

 

7 Close the database.

 

If you are unsure of how to perform any of the previous exercises, Here we will explain them to you.

 



   

Page. e.6

Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.