Database Design and Implementation of Hong Kong Airlines Management System

This assignment involves designing and implementing a database management system for Hong Kong Airlines, including creating an Entity-Relationship (ER) diagram, data normalization, and a user interface using Microsoft Access. The project consists of several tables, including booking, flight, passenger, staff member, admin, and more. The ER diagram shows the relationships between these entities and helps to identify potential issues with data redundancy and anomalies. The implementation phase involves creating the database schema, populating the tables with sample data, and designing a user interface for querying and manipulating the data.

INTRODUCTION
Hong Kong Airlines management system is designed for busy city of Hong kong as there is enormous
amount of travellers in and out of the city. It manages the passenger capacity of more than 70
million and the runway capacity of the airport is 400 thousand. This system is setup in order to help
manage this enormous amount of data that flows in and out everyday. It enables and helps in
efficient storage and management of flight details, passenger details, schedule details, booking
details of flights, routes of flights, airport details, pilot details, etc. It helps in booking airline tickets
online with bill payment. Customers may use the system to check schedule of flights and book
tickets. The customer may also get his or her booking details.
The management system also helps staff members of airlines by easing their job. The airline staff
members can view and update flight schedules when required. The staff members can also get and
update route details for flights. Staff members can also view airport details but cannot update
airport details. Flight schedules can also be updated by staff members. Staff member can also access
passenger details from the system. The passenger details can also be updated by staff members if
required. The airline staff members may assign different pilots to each flight. The staff members may
also view pilot details.
The software manages the data by assigning each passenger and flights a unique ids with help of
which the bookings made in the system are tracked. The system also keeps tracks of each pilot
assigned to flights. All the routes details for every flight is managed efficiently.
The system is powerful, easy to use and flexible which is specifically designed to deliver real
conceivable benefits to Hong kong airlines management system.
BUSINESS RULES
Business rules are certain aspects of a business that defines the functioning of business. These rules
specify how the business works and the structure of the various aspects of business is. All the
constraints, definitions and operations that are applied to the business are included in the business
rules. These resolve to either true or false.
The business rules for Hong kong airlines database management system are stated as follows –
Rule 1 : A passenger can book flight. A passenger may have one or more flight bookings.
Rule 2 : A flight will have many seats and a flight can have one or more than one bookings.
Rule 3 : A pilot may be assigned to more than one flights.
Rule 4 : A flight may have one or more than one pilots.
Rule 5 : A route will be assigned to single flight.
Rule 6 : A route will have two airports assigned to it, one departure airport and one arrival airport.

Rule 7 : An airport may be arrival airport or departure airport.
The above business rules defines the functioning of the airlines system. The flight, booking, airport,
pilot, passenger and routes data is managed with help of these rules. The system will be used by the
staff members of airlines to manage the fight details and by passengers to book flights tickets.
CONCEPTUAL MODEL
The entire system manages the airlines database by dividing the system into seven records. These
records are solely responsible for the management of flights, airports, and passenger data. The
concept behind the development of this model is on the cardinality between records. As we know,
there an be multiple airports in the hong kong city. The airport can have multiple flights. This is one
to many relationship.
A single city can have many airport. So one to many relation. In similar manner, a single airport can
have many flights, so here alos the same relation that is one to many.
Flights and Passenger have a many to may relation, as there can be many passenger on same flight,
and one passenger can go on multiple filghts.
The overall E-R diagram which is concluded over the conceptual thinking is shown below-
Figure-1 : E-R Model
The above shown diagram shows the relation between the seven tables. These tables follow the
normalizations rules, and are deployed in Microsoft access. These tables are implemented in the

software to check the queries and see the implementation. Also the user interface is made for all
the tables in the Microsoft access, which will be shown in the implementation part.
LOGICAL DESIGN
Conceptual design for this system gave use the entity relation diagram, of how are tables going to
be. But to execute anything, or to know how our system is going to work, we must fix the use case.
In this section use case is developed to make sure the logical use of the database, and how queries
are going to work.
On observing the use of functionalities required by the airline system, the following use case
diagram is obtained.
Figure-2 : Use Case Model
This use case diagram shows how data can be retrieved and who can retrieve. Admin can find
different types of data from this view. We will see three queries, one from admin, one from
passenger, and one from staff member.

Admin – Admin wants to get the idea of flights whose in total passengers are more than 10. Admin
wants to find out the flight ids,
Query 1-
select flightID, count(ticketID) as PassengerCount from booking group by flightID having
PassengerCount>=10;
The above query when run produces the following output
Figure-3 : Query-1 Result
These are the flights numbers required by the admin.
Passenger – A passenger simply want to know about the flights on a particular route (say 49017).
Query 2–
SELECT flightID,arrivalTime, arrivalDate, departureTime, departureDate
FROM Flight where routeID = 49017
The above query when executes gives the flight id with their arrival and departure information for
the route 49017.
Figure-4 : Query-2 Result

Staff Members – A staff member can find out the total number of passenger who will be travelling in
some filght, having class Economy.
Query 3–
SELECT Count(ticketID) as Total_Passenger FROM Booking where flightID = 2 and class = 'Economy';
The above query gives the number of total passengers travelling in the flight having id 2, and
passenger travelling with class economy.
The output is shown as
Figure-5 : Query-3 Result
In this manner the data can be retrieved by the different actors in out use case system. This system
fulfils all the logical vies of the data, and supports multiple links in the database.
IMPLEMENTATION
In implementation stage, the logical view will be converted to practical implementation. Microsoft
Access will be used as database for the system. The user interface for the tables will be developed
using the access forms.
Creation of tables – As studied in the E-R diagram, there are total seven tables to be implemented in
the system. These tables are –
1. Airports
2. Flights
3. Routes
4. Pilot
5. Flight_Pilot
6. Passenger
7. Booking

Each table will be implemented one by one.
1. Airports
This tables consists of the fields – AirportId, name, city, country, code. AirportId is the primary
key of the table, as all airports will have a unique ID, so airport Id can identity every airport
uniquely. To create this table we have-
CREATE TABLE `airports` (
`airportID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(70) DEFAULT NULL,
`city` varchar(45) DEFAULT NULL,
`country` varchar(45) DEFAULT NULL,
`code` varchar(10) DEFAULT NULL,
PRIMARY KEY (`airportID`)
) ENGINE=InnoDB AUTO_INCREMENT=9542 DEFAULT CHARSET=latin1;
The above query creates the table name airports. To insert the data we will be using the following
query-
INSERT INTO `airports` VALUES
(1,'Goroka','Goroka','Papua New Guinea','GKA'),
(2,'Madang','Madang','Papua New Guinea','MAG'),
(3,'Mount Hagen','Mount Hagen','Papua New Guinea','HGU'),
(4,'Nadzab','Nadzab','Papua New Guinea','LAE'),
(5,'Port Moresby Jacksons Intl','Port Moresby','Papua New Guinea','POM');
On using insert queries we get the tables as shown below-
Figure-6 : Airport Table

The user interface made in access for the same is given as-
Figure-7 : Airport Table interface
2. Flights
This tables consists of the fields – flightId departureTime, departureDate, arrivalTime,
arrivalDate, routeId, updatedAt. flightId is the primary key of the table, as all flights will have a
unique ID, so flight Id can identity every flight uniquely. To create this table we have-
CREATE TABLE `flight` (
`flightID` int(11) NOT NULL AUTO_INCREMENT,
`departureTime` time DEFAULT '00:00:00',
`departureDate` date DEFAULT '0000-00-00',
`arrivalTime` time DEFAULT '00:00:00',
`arrivalDate` date DEFAULT '0000-00-00',
`routeID` int(11) DEFAULT NULL,
`updatedAT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`flightID`),
KEY `routeID` (`routeID`),
CONSTRAINT `flight_ibfk_1` FOREIGN KEY (`routeID`) REFERENCES `route` (`routeID`) ON
DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
The above query creates the table name flight. To insert the data we will be using the following
query-
INSERT INTO `flight` VALUES
(1,'09:25:00','2015-12-25','10:35:00','2015-12-25',49017,'0000-00-00 00:00:00'),
(2,'10:55:00','2015-12-25','12:10:00','2015-12-25',49017,'0000-00-00 00:00:00'),
(3,'12:35:00','2015-12-25','13:55:00','2015-12-25',60206,'0000-00-00 00:00:00'),
(4,'14:00:00','2015-12-25','15:10:00','2015-12-25',60206,'0000-00-00 00:00:00'),
(5,'15:40:00','2015-12-25','16:50:00','2015-12-25',16530,'0000-00-00 00:00:00');

On using insert queries we get the tables as shown below-
Figure-8 : Flights table
The user interface made in access for the same is given as-
Figure-9 : Flight Table Interface
3. Flight_pilot
This tables consists of the fields – flightId, pilotId. flightId and pilot Id are both the primary kes of
different tables, but the combine and work as a candidate key. The uniquely relateds the pilot
and flight relations..To create this table we have-
CREATE TABLE `flight_pilot` (
`flightID` int(11) DEFAULT NULL,
`pilotID` int(11) DEFAULT NULL,
KEY `flightID` (`flightID`),
KEY `pilotID` (`pilotID`),
CONSTRAINT `flight_pilot_ibfk_1` FOREIGN KEY (`flightID`) REFERENCES `flight`
(`flightID`) ON DELETE CASCADE,
CONSTRAINT `flight_pilot_ibfk_2` FOREIGN KEY (`pilotID`) REFERENCES `pilot` (`pilotID`)
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The above query creates the table name flight_pilot. To insert the data we will be using the
following query-
INSERT INTO `flight_pilot` VALUES
(1,1),
(1,2),
(2,3),
(2,4),
(3,5),
(3,6);
On using insert queries we get the tables as shown below-
Figure-10 : Flight_Pilot Table
The user interface made in access for the same is given as-
Figure-11 : Flight_Pilot table interface

4. Pilot
This tables consists of the fields – pilotId, firstName, lastName, experience. pilotId is the primary
key of the table, as all pilots will have a unique ID, so pilotId can identity every pilot uniquely. To
create this table we have-
CREATE TABLE `pilot` (
`pilotID` int(11) NOT NULL AUTO_INCREMENT,
`firstName` varchar(45) DEFAULT NULL,
`lastName` varchar(45) DEFAULT NULL,
`experience` int(11) DEFAULT NULL,
PRIMARY KEY (`pilotID`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=latin1;
The above query creates the table name routes. To insert the data we will be using the following
query-
INSERT INTO `pilot` VALUES
(1,'Jerry','Mcdonald',16),
(2,'Shirley','James',32),
(3,'Heather','Clark',17),
(4,'Maria','Moore',3),
(5,'Gloria','Anderson',33),
(6,'Diane','Hamilton',17);
On using insert queries we get the tables as shown below-
Figure-12 : Pilot Table
The user interface made in access for the same is given as-

Figure-13 : Pilot Table Interface
5. Routes
This tables consists of the fields – routeId, departureAirportId, arrivaleAirportId. routeId is the
primary key of the table, as all routes will have a route ID, so route Id can identify every route of
flight uniquely. To create this table we have-
CREATE TABLE `route` (
`routeID` int(11) NOT NULL AUTO_INCREMENT,
`departureAirportID` int(11) DEFAULT NULL,
`arrivalAirportID` int(11) DEFAULT NULL,
PRIMARY KEY (`routeID`),
KEY `departureAirportID` (`departureAirportID`),
KEY `arrivalAirportID` (`arrivalAirportID`),
CONSTRAINT `route_ibfk_2` FOREIGN KEY (`departureAirportID`) REFERENCES `airport`
(`airportID`),
CONSTRAINT `route_ibfk_3` FOREIGN KEY (`arrivalAirportID`) REFERENCES `airport`
(`airportID`)
) ENGINE=InnoDB AUTO_INCREMENT=61511 DEFAULT CHARSET=latin1;
The above query creates the table name routes. To insert the data we will be using the following
query-
INSERT INTO `route` VALUES
(1,3832,7242),
(2,7242,3832),
(3,1423,6492),
(4,1386,6492),
(5,1353,6492),
(6,1335,6492);
On using insert queries we get the tables as shown below-

Figure-14 : Routes Table
The user interface made in access for the same is given as-
Figure-15: Routes Table Interface
6. Passenger
This tables consists of the fields – passengerID, firstName lastName, email, age. passengerID is
the primary key of the table, as all passenger will have a unique ID, so passenger Id can identity
every passenger uniquely. To create this table we have-
CREATE TABLE `passenger` (
`passengerID` int(11) NOT NULL AUTO_INCREMENT,
`firstName` varchar(45) DEFAULT NULL,
`lastName` varchar(45) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`passengerID`)
) ENGINE=InnoDB AUTO_INCREMENT=1114 DEFAULT CHARSET=latin1;
The above query creates the table name passenger. To insert the data we will be using the following
query-
INSERT INTO `passenger` VALUES

(1,'Frank','Stanley','[e-mail]' ,59),
(2,'Lois','Thompson','[e-mail]' ,29),
(3,'Deborah','Bryant','[e-mail]' ,66),
(4,'Ruth','Ryan','[e-mail]' ,46),
(5,'Jennifer','Peterson','[e-mail]' ,49),
(6,'Annie','Hughes','[e-mail]' ,37),
(7,'Todd','Wheeler','[e-mail]' ,19),
(8,'Billy','Henry','[e-mail]' ,68);
On using insert queries we get the tables as shown below-
Figure-16: Passenger Table
The user interface made in access for the same is given as-
Figure-17: Passenger Table Interface

7. Booking
This tables consists of the fields – ticketId, flightId seatNum, class, userId, updatedAt. ticketId is
the primary key of the table, as all tickets will have a unique ID, so ticket Id can identity every
journey uniquely. To create this table we have-
CREATE TABLE `booking` (
`ticketID` int(11) NOT NULL AUTO_INCREMENT,
`flightID` int(11) DEFAULT NULL,
`seatNum` varchar(45) DEFAULT NULL,
`class` varchar(45) DEFAULT NULL,
`userID` int(11) DEFAULT NULL,
`updatedAT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ticketID`),
KEY `flightID` (`flightID`),
KEY `userID` (`userID`),
CONSTRAINT `booking_ibfk_1` FOREIGN KEY (`flightID`) REFERENCES `flight` (`flightID`) ON
DELETE CASCADE,
CONSTRAINT `booking_ibfk_2` FOREIGN KEY (`userID`) REFERENCES `user` (`userID`) ON DELETE
CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=303 DEFAULT CHARSET=latin1;
The above query creates the table name booking. To insert the data we will be using the following
query-
INSERT INTO `booking` VALUES
(1,1,'11A','Economy',23,'0000-00-00 00:00:00'),
(2,1,'11B','Economy',11,'0000-00-00 00:00:00'),
(3,7,'11C','Economy',53,'0000-00-00 00:00:00'),
(4,1,'11J','Economy',40,'0000-00-00 00:00:00'),
(5,1,'11K','Economy',8,'0000-00-00 00:00:00');
On using insert queries we get the tables as shown below-

Figure-18: Booking Table
The user interface made in access for the same is given as-
Figure-19: Booking Table Interface
DIFFICULTIES
Design phase :
The difficulties which came in designing the logical view, i.e. ER diagram and database tables, was
related to the redundancy in data. Any how we were getting the data duplicated, sometimes null
values were affecting the database. To counter such problem, we studied the normalisation rules,
anomalies. There were three kind of anomalies studied in this case. Updation anomaly, deletion
anomaly, and insertion anomaly. On keeping in mind all these anomalies, and applying the
normalization rules, we finally get the idea of database tables, and the E-R diagram. The E-R diagram
consists of 7 tables when finally designed. This diagram is complex to understand.
Implementation phase:
Access is a very powerful tool, for storing the data, and making the application work. This is a very
common tool, used by professionals. First, I was not getting how to import the sql data to access
data. Then I used the csv files to import them. We can directly import the csv files into the access
project. This was a new thing for me, and it was a good experience learning all these things.
Difficulty came in making the form UI, as I was totally unaware of that. Then I learnt this and made
the ui for all the tables. Access is a very powerful tool, as it not only helps in manipulating the data
records but also helps in viewing them and visualize them in a better way.
WORK DISTRIBUTION LIST

Task Name Start date Finish date Responsible
Task 1 Student 1
Task 2 Student 2
CONCLUSION
In this project, we have successfully used the conceptual and logical information to create ER diagram
and use case diagram for the system. We have also implemented all the data tables of the airlines
system and applied queries. We have seen the retrieval of data through admin, passenger and staff
member.The designed Hong Kong airlines management system efficiently stores airlines data without
redundancy. The usage of the system will increase the productivity and speed up the processing of
airlines data. The system is helpful for both the passengers as well as the staff members
REFERENCE
[1] https://www.e-education.psu.edu/spatialdb/l2_p5.html
[2] https://www.ipdsolution.com/ipdblog/how-to-write-business-rules
[3] https://www.visual-paradigm.com/guide/data-modeling/what-is-entity-relationship-diagram/
[4] https://docs.oracle.com/cd/A84870_01/doc/server.816/a76994/logical.htm
[5] https://www.e-education.psu.edu/spatialdb/l2_p5.html
[6] https://www.tutorialspoint.com/ms_access/ms_access_create_database.htm
[7] http://www.myreadingroom.co.in/notes-and-studymaterial/65-dbms/487-database-design-
challenges.html
[8] https://micoresolutions.com/top-database-management-challenges/
[9] https://www.guru99.com/database-normalization.html
[10] https://edu.gcfglobal.org/en/access2016/designing-a-simple-query/1/
[11] http://libguides.usc.edu/writingguide/groupprojects
[12] https://www.smartdraw.com/use-case-diagram/
[13] https://explore.easyprojects.net/blog/how-to-write-a-project-report
[14] https://www.lifewire.com/access-2013-tour-the-user-interface-1019522
[15] https://www.relationaldbdesign.com/relational-database-design/module5/entities-attributes-
conceptualModel.php