Relational Database System Design for Airport Transfer Company

Desklib provides past papers and solved assignments for students. This project details the design and implementation of a relational database for an airport transfer company.

Table of Contents
LO1 Use an appropriate design tool to design a relational database system for a substantial
problem............................................................................................................................................1
P1 Design a relational database system using appropriate design tools and techniques,
containing at least four interrelated tables, with clear statements of user and system
requirements................................................................................................................................1
LO2 Develop a fully functional relational database system, based on an existing system design..5
P2 Develop the database system with evidence of user interface, output, and data validations,
and querying across multiple tables.............................................................................................5
Table of Figures
Figure 1: ER diagram.......................................................................................................................2
Figure 2: Logical Diagram...............................................................................................................4
Figure 3: Conceptual Diagram.........................................................................................................6
Figure 4: Database Creation............................................................................................................7
Figure 5: select database for use......................................................................................................7
Figure 6: airport table......................................................................................................................8
Figure 7: manager table...................................................................................................................8
Figure 8: airline_company table......................................................................................................9
Figure 9: flight table......................................................................................................................10
Figure 10: pilot table......................................................................................................................10

Figure 11: customer table..............................................................................................................11
Figure 12: Ticket table...................................................................................................................12
LO1 Use an appropriate design tool to design a relational database system for
a substantial problem
P1 Design a relational database system using appropriate design tools and
techniques, containing at least four interrelated tables, with clear statements of user
and system requirements.
ER Diagram:
Figure 1: ER diagram

The given ER diagram is of airport Transfer Company which consists of seven different tables
and these tables have different columns or attributes. Every table is connected with each other
showing relationship among them. The table Airport contains three different attributes that are,
ID, Airport_name and City. This table explains that Airport will have a unique ID which will act
as a primary key that is why it is highlighted by underline and bold format and different airport
have different name similarly, the name of the city in which the airport is located. This table is
interconnected with other tables with the one-to-many relationship and these tables are table
manager and stable flight. Another table Manager is comprised of six different attributes and
these are Name, Contact, E-mail, Mng_id, Salary, and ID. In this table Mng_id will be primary
key as for every manager there is unique identification and ID attributes have shown in italic
which represents the foreign key. It is assigned as foreign key because it is taking reference from
table Airport. Moving forward, another table includes Airline_Company, this table shows that
for every flight there will be a company that controls it and this table has five different attributes
namely, A_ID, Name, No_of_flights, No_of_employees, and Head_Office. In this table, A_id is
assigned as a primary key because for every company there will be different identification. Next
table name is Flight which holds all the basic information of a flight and this table has five
different attributes namely, A_id, F_id, Name, Model and Destination. A_id is assigned as a
foreign key in this table as it is taking reference from another table and F_id is assigned as
foreign key as for every flight there will be unique identification. One will book tickets in-flight
obviously which lead to another table namely, Tickets, this table has again five different
attributes that are; Ticket_no which is assigned as the primary key as there will be a unique
identification for every ticket which is available as per the seat availability. C_ID and F_id are
assigned as foreign keys as they both are taking reference from the other two tables and other
attributes are type and price of the ticket. Now as tickets are ready so customers are also ready to
book their tickets already, here comes another table name Customer which will have four
different attributes containing their name, id, contact number and their destination where c_id is
assigned as primary key by highlighting it as every customer will have a unique identification.
The one who will drive the plane is Pilot, so here comes another table name Pilot which will hold
all information about the pilot and comprise of seven different attributes. These attributes are
P_id, Name, Salary, Experience, F_id, Contact and E-mail. Out of these attributes, P_id is
assigned as the primary key as for every pilot unique identification is important and F_id is

assigned as foreign key as it is important to take reference from table flight to know which pilot
will drive which flight. All these tables are interconnected with each other with the help of one-
to-many relationships.
LOGICAL DIAGRAM:
Figure 2: Logical Diagram
The logical diagram shows the relationship among all tables that are present in a database and in
this database there is a total of seven tables. These are named as; Airport, Airline_company,

Flight, Ticket, Manager, Customer, and Pilot. These tables are comprised of different attributes
and for every table, there are unique attributes which help in describing that particular table. The
table Airport contains three attributes and the ID attribute is assigned as primary here. This table
is connected with table Flight as every airport can contain multiple flights so a one-to-many
relationship is used. Similarly, table flight is also connected with a one-to-many relationship as
multiple tickets can be available for a single flight. This table has six different attributes from
which F_id is primary key and A_id is a foreign key. The table Airline_Company has six
different attributes containing A_id as the primary key and this table is interconnected through a
one-to-many relationship with the table flight which shows that one airline company can hold
multiple flights and controls them and tracking them. The table customer is also present,
displaying all basic information regarding a customer and this table has four different attributes
from which C_id is assigned as the primary key and it is connected through a one-to-many
relationship with the table tickets as a single customer can book more than one ticket. The table
Pilot has seven different attributes out of which P_id is the primary key and F_id is the foreign
key. This table is also connected through one too many relationships with the table flight as one
flight can have more than one pilots depending upon the flight. Finally comes the manager who
will manage all the system and keep track of every flight at the airport. This table Manager has
six different attributes from which Mng_id is the primary key and ID is the foreign key as it is
taking reference from the Airport table. This table is connected through One too many
relationships with the table Airport explaining that one manager can manage more than one
airport which is available in a particular city.
CONCEPTUAL Diagram:

Figure 3: Conceptual Diagram
LO2 Develop a fully functional relational database system, based on an
existing system design
P2 Develop the database system with evidence of user interface, output, and data
validations, and querying across multiple tables.
Database Creation

The above-designed database is created by Mysql Workbench. One needs to create a database
first.
Figure 4: Database Creation
The above code is used to create a database in the workbench.
Before the creation of tables, one need to use the already created database. The use of the
database is necessary so that one can store data in that database.
Figure 5: select database for use
The above figure is the code representing the use of the database.
Table creation
Queries for the creation of tables for the flight database are:

Figure 6: airport table
The above figure is the snapshot of the code used to create the airport table for the database.
Figure 7: manager table

The above figure is the snapshot of the code used to create a table of the manager. The manager
is the person who manages all the airport activity.
Figure 8: airline_company table
The above snapshot is the is taken from the workbench which is used to create a table for storing
data of the airline company.

Figure 9: flight table
The above snapshot is providing you the query used to create a table flight for storing the flights.
Figure 10: pilot table

The above figure is the snapshot taken from the MySQL workbench. This is used to create a
table named pilot which is used to store the detail about the pilot of the flight.
Figure 11: customer table
The above code is the snapshot from the workbench in which table for the customer is created.

Figure 12: Ticket table
The above figure is the snapshot taken from MySQL workbench which is used to create a table
of tickets in which all the detail of the ticket of the customer is provided.
Data Insertion
Insert data into airport table. This table store the data of the airport. Queries used to store values
are: -
insert into airport values (1, ’East Midlands Airport’, ’Castle Donington’);
insert into airport values (2, ’Derby Airfield’, ’Derby’);
insert into airport values (3, ’Gamston Airport’, ’Retford’);
insert into airport values (4, ’Sywell Aerodrome’, ’Sywell’);
insert into airport values (5, ’Bourn Airfield’, ’Bourn’);
Insert data into the manager table of the airport. Queries used to store these values are: -