Assignment Financil Management
- 2076 Words
- 9 Pages
- Report
Assignment financil management
Assignment Financil Management
Icarus Airlines require a Database System to be set up to handle passengers booked onto flights. The ACCESS 2003 database will be used to record details of passengers and flights operated by the airline. It can also be used to record details of the bookings made by passengers onto flights. Identify three tables which need to be created.
Required:
a) Suggest and create fields (name, type and size) for these three tables using the ACCESS database package. Enter sample records (at least 5) for each table and print the results.
(15 marks)
b) Create two suitable queries, by linking two or more tables, which will be useful to the airline.
(15 marks)
c) Create a monthly report based on one of these queries, which will be useful to the management of the airline.
(5 marks)
(Total: 35 marks)
SOLUTION
The requirement of Icarus Airlines can be solved by constructing a database on MS Acess. The purpose of the database would be to enable the management of Icarus Airlines to analyze the information about its passengers and payments effectively and efficiently. The database constructed in MS Acess is explained step by step from here onwards.
The first task is creating the tables and setting the format for various fields. The fields have to be created in accordance with the field nature, for instance, a 'Name field' will be in the data type of 'text', while the data type of the amount earned on a ticket would be a currency. These data types enable a refined presentation and sorting of data, which will help the management in making useful decisions. Three tables were formed namely the “Passenger Information, Service Information and Payment Information. Following are the snapshots of the tables in the desin view:
Passenger Information
Service Information
Payment Information
The input masks of most of the fields have been applied in order to enable a specific entry into the field. This helps in ensuring that the required information is entered in to the fields exactly as expected. The 'Datasheet View' of the three tables is provided below with 10 records enter into each table.
Passenger Information
Passenger ID
Passenger Name
Father's Name
Gender
Age
Nationality
BG-91NGDU
Sarah
Roberts
Female
58
British
BU-83BGNJ
Michael
Jackson
Male
23
British
CE-86TFTF
James
Anderson
Male
45
British
DF-79DVRD
Michelle
Taylor
Female
29
American
GJ-76NKBU
Catherine
Jones
Female
29
British
HD-23WERD
Julia
Bullock
Female
48
American
JK-89IUYH
Paul
Walker
Male
51
British
OL-89NJFG
Angelina
Lawrence
Female
34
British
SD-62LGHJ
Fred
Cooper
Male
38
British
XD-23MLSX
Jim
Franklin
Male
31
British
Service Information
Service ID
Air Plane Model
Destination
Departure Time
Seat Booking
Ticket Number
B-2346
Boeing 757
Sheffield
12:30:00 PM
56SFGD
2497984
D-7856
Boeing 747
Plymouth
9:53:00 PM
54RTER
6426797
H-7867
Boeing 757
Westminster
1:30:00 AM
45SDFD
5965266
J-2344
Boeing 757
Bradford
3:03:00 AM
76KYDS
9792979
J-9283
Boeing 747
Derby
10:00:00 PM
12HMNO
6994674
L-3743
Boeing 777
Canterbury
11:53:00 PM
85GNKW
6429496
M-2943
Boeing 777
Leicester
9:01:00 AM
23CSND
2964564
Q-3264
Boeing 747
Nottingham
8:51:00 AM
45ILSD
6264646
S-6754
Boeing 737
Coventry
6:45:00 AM
75AEWM
9679767
Z-3246
Boeing 737
Bristol
12:50:00 AM
36DMIO
4642496
Payment Information
Payment ID
Passenger ID
Service ID
Ticket Charges
Mode of Payment
Cheque Number
Day of Payment
Month of Payment
B3L92
BG-91NGDU
L-3743
1200
Cash
26
January
D8F76
BU-83BGNJ
J-9283
1500
Cash
25
February
H2D90
XD-23MLSX
Q-3264
1100
Cash
15
February
J7J90
CE-86TFTF
M-2943
1250
Cash
24
January
K7D22
DF-79DVRD
H-7867
1050
By Cheque
24264646
29
January
K8S32
GJ-76NKBU
J-2344
2000
By Cheque
34143545
05
January
L7A54
HD-23WERD
S-6754
1450
Cash
08
February
P3D23
JK-89IUYH
Z-3246
1500
By Cheque
21020454
21
February
P7F76
OL-89NJFG
D-7856
1850
Cash
09
January
S4F45
SD-62LGHJ
B-2346
1900
Cash
01
January
Once the tables have been made and data has been fed into the table, querries are made in order to use the data for a meaningful purpose including report generation. Two basic querries were made in this regard namely the Passenger Information Querry and Revenue Generation Information Querry. The snapshots of the design view of these querries are provided below
Furthermore, the 'Datasheet view' of these two querries is provided in the tables below:
Passenger Information Query
Payment ID
Passenger Name
Father's Name
Gender
Age
Nationality
S4F45
Fred
Cooper
Male
38
British
L7A54
Julia
Bullock
Female
48
American
K7D22
Michelle
Taylor
Female
29
American
H2D90
Jim
Franklin
Male
31
British
P3D23
Paul
Walker
Male
51
British
D8F76
Michael
Jackson
Male
23
British
P7F76
Angelina
Lawrence
Female
34
British
K8S32
Catherine
Jones
Female
29
British
J7J90
James
Anderson
Male
45
British
B3L92
Sarah
Roberts
Female
58
British
Revenue Generation Information Query
Passenger ID
Passenger Name
Father's Name
Service ID
Destination
Ticket Number
Payment ID
Ticket Charges
Month of Payment
BG-91NGDU
Sarah
Roberts
L-3743
Canterbury
6429496
B3L92
1200
January
CE-86TFTF
James
Anderson
M-2943
Leicester
2964564
J7J90
1250
January
GJ-76NKBU
Catherine
Jones
J-2344
Bradford
9792979
K8S32
2000
January
OL-89NJFG
Angelina
Lawrence
D-7856
Plymouth
6426797
P7F76
1850
January
DF-79DVRD
Michelle
Taylor
H-7867
Westminster
5965266
K7D22
1050
January
SD-62LGHJ
Fred
Cooper
B-2346
Sheffield
2497984
S4F45
1900
January
BU-83BGNJ
Michael
Jackson
J-9283
Derby
6994674
D8F76
1500
February
JK-89IUYH
Paul
Walker
Z-3246
Bristol
4642496
P3D23
1500
February
XD-23MLSX
Jim
Franklin
Q-3264
Nottingham
6264646
H2D90
1100
February
HD-23WERD
Julia
Bullock
S-6754
Coventry
9679767
L7A54
1450
February
After the development of the querries, a report is constructed for the purpose of providing monthly information of the perforamance to the ...