Database Systems for EasyTravel Group: Design, Implementation and Queries

This report discusses the technical requirements, database implementation, business reports, recommendations, and user documentation for the booking system developed for EasyTravel Group using the relational database management system. It includes software and hardware requirements, ER-diagram, business rules, all tables, constraints, insert data, and queries.

Running head: DATABASE SYSTEMS
DATABASE SYSTEMS
Name of the Student
Name of the University
Author Note

1DATABASE SYSTEMS
Table of Contents
Introduction......................................................................................................................................2
Software and Hardware Requirements............................................................................................2
Software requirements.................................................................................................................2
Hardware requirements................................................................................................................2
Database design...............................................................................................................................3
ER-Diagram.................................................................................................................................3
Business Rules.............................................................................................................................4
Database Implementation................................................................................................................4
All Tables.....................................................................................................................................5
Constraints...................................................................................................................................9
Insert Data....................................................................................................................................9
Queries...........................................................................................................................................14
Conclusion and Recommendation.................................................................................................18
References......................................................................................................................................20

2DATABASE SYSTEMS
Introduction
EasyTravel Group is one of UK’s largest travel platforms. The main goal of this project is to
design and develop a booking system to enhance the business process of the company. The
approach to develop the booking system for the EasyTravel Company is based on the relational
database management system (Laudon and Laudon 2015). This report discuses, the technical
requirements, database implementation, business reports, recommendations etc. The report also
shows the user documentation to explain the implementations.
Software and Hardware Requirements
Software requirements
After analyzing the business process for the travel company EasyTravel, the development is done
with the Relational database management system. The software selected form the database
system is MySQL (DuBois 2014). The software requirements for the project is given below:
i. Xampp: It provides the SQL database software to implement and develop database
system including, apache server (West and Prettyman 2018). The version of the used
software is 3.2.4
ii. MySQL Workbench: It is a MySQL developer tool which provides the modelling,
reverse engineering, forward engineering etc. for a database administrator. The Entity
Relationship diagram is developed using the Workbench software.
Hardware requirements
The hardware requirements to run the above mentioned software and implement database are
given below:

3DATABASE SYSTEMS
i. Windows 7 64 bit
ii. Storage: 10 GB (min)
iii. Web Browser
iv. Ram: 2 GB
Database design
ER-Diagram
Entity relationship diagram represents the Real-world objects as entities and relationship
between them. The data category are called attributes (Elmasri and Navathe 2017). An attribute
can be non-key or key attribute. Key attributes identifies each tuple uniquely in a Database
management system. Below the Entity relationship diagram represents the business process of
the EasyTravel Company.

4DATABASE SYSTEMS
Figure 1: Entity Relationship Diagram
Source: created by author
Business Rules
Business rules identifies and represents the each process of the business in order to achieve the
main goal of the system. The business rules defines the process and it defines the functionality of
the system (Isakh and Widagdo 2016). It helps in modelling the ERD and vice-versa. Business
rules for the EasyTravel can be listed below:
i. Each customer can book zero or multiple bookings for a journey.
ii. Each booking is associated with one and only one customer.
iii. Each booking is related to one and only one flight.
iv. Each flight can have one or many bookings.

5DATABASE SYSTEMS
v. Each flight is associated with one and only one Airline.
vi. Each Airline provides one or many flights.
vii. Each payment is related to one and only one booking.
viii. Each booking can have zero or one payment.
Database Implementation
By running the DDL (data definition language) statements, the database schema can be created
with the required indexes and constraints. For each tables the DDL are given below respective to
their tables. It defines the structure of the tables, data types and data size for the attributes along
with defining the key attributes.
All Tables
Customer Table
CREATE TABLE `customer` (

6DATABASE SYSTEMS
`CustID` varchar(5) NOT NULL,
`Firstname` varchar(45) DEFAULT NULL,
`Lastname` varchar(45) DEFAULT NULL,
`DOB` date DEFAULT NULL,
`Gender` varchar(10) DEFAULT NULL,
`Contact` int(10) DEFAULT NULL,
`Email` varchar(45) DEFAULT NULL,
`Address` varchar(100) DEFAULT NULL
) ;
ALTER TABLE `customer`
ADD PRIMARY KEY (`CustID`);
Airlines Table
CREATE TABLE `airlines` (
`AirlinesName` varchar(100) NOT NULL,
`PhoneNumber` int(10) DEFAULT NULL,
`ContactPerson` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `airlines`
ADD PRIMARY KEY (`AirlinesName`);

7DATABASE SYSTEMS
Booking Table
CREATE TABLE `booking` (
`BookingID` varchar(5) NOT NULL,
`Date` date DEFAULT NULL,
`FlightNumber` varchar(5) NOT NULL,
`FlightDate` date DEFAULT NULL,
`CustID` varchar(5) NOT NULL
) ;
ALTER TABLE `booking`
ADD PRIMARY KEY (`BookingID`,`FlightNumber`),
ADD KEY `fk_Booking_Customer1_idx` (`CustID`),
ADD KEY `fk_Booking_Flights1_idx` (`FlightNumber`);

8DATABASE SYSTEMS
Flights Table
CREATE TABLE `flights` (
`FlightNumber` varchar(5) NOT NULL,
`AirlinesName` varchar(100) NOT NULL,
`Source` varchar(45) DEFAULT NULL,
`Destination` varchar(45) DEFAULT NULL,
`Fare` decimal(6,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `flights`
ADD PRIMARY KEY (`FlightNumber`,`AirlinesName`),
ADD KEY `fk_Flights_Airlines1_idx` (`AirlinesName`);

9DATABASE SYSTEMS
Payment Table
CREATE TABLE `payment` (
`PaymentID` varchar(5) NOT NULL,
`PaymentMode` varchar(45) DEFAULT NULL,
`BookingID` varchar(5) NOT NULL
) ;
ALTER TABLE `payment`
ADD PRIMARY KEY (`PaymentID`),
ADD KEY `fk_Payment_Booking1_idx` (`BookingID`);

10DATABASE SYSTEMS
Constraints
The foreign key constraints are required to establish the relationship between the tables. It helps
in data integrity and reducing redundancy (Korotkevitch 2014). Foreign key is an attribute which
is referred from the (parent) table where the attribute is a primary key. Foreign key constraint
implementations for the required relationship are given below:
Constraints for table booking
ALTER TABLE `booking`
ADD CONSTRAINT `fk_Booking_Customer1` FOREIGN KEY (`CustID`)
REFERENCES `customer` (`CustID`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
ADD CONSTRAINT `fk_Booking_Flights1` FOREIGN KEY (`FlightNumber`)
REFERENCES `flights` (`FlightNumber`) ON DELETE NO ACTION ON UPDATE
NO ACTION;
Constraints for table flights
ALTER TABLE `flights`
ADD CONSTRAINT `fk_Flights_Airlines1` FOREIGN KEY (`AirlinesName`)
REFERENCES `airlines` (`AirlinesName`) ON DELETE NO ACTION ON UPDATE
NO ACTION;
Constraints for table payment
ALTER TABLE `payment`
ADD CONSTRAINT `fk_Payment_Booking1` FOREIGN KEY (`BookingID`)
REFERENCES `booking` (`BookingID`) ON DELETE NO ACTION ON UPDATE NO
ACTION;
Insert Data
The DML (data manipulation language) statements for the insertion of the data in their respective
table is given below.
Customer Table

11DATABASE SYSTEMS
INSERT INTO `customer` (`CustID`, `Firstname`, `Lastname`, `DOB`,
`Gender`, `Contact`, `Email`, `Address`) VALUES
('C101', 'Marie', 'Patt', '2001-02-11', 'Female', 787558849,
'[e-mail]', '10 Downing Street, London'),
('C102', 'Anne', 'Marie', '1997-10-30', 'Female', 56789865,
'[e-mail]', '44-46 Morningside Road\r\nEdinburgh\r\nScotland'),
('C103', 'Charlie', 'Paul', '1999-08-06', 'Male', 86789868,
'[e-mail]', '27 Colmore Row\r\nBirmingham'),
('C104', 'Mathew', 'Perry', '2000-05-14', 'Male', 88,
'[e-mail]', '91 Western Road\r\nBrighton\r\nEast Sussex'),
('C105', 'Joana', 'Perks', '1999-11-13', 'Female', 767645645,
'[e-mail]', 'George Street\r\nBath'),
('C106', 'Kane', 'Sebastian', '1992-10-01', 'Male', 568768677,
'[e-mail]', '61 Wellfield Road\r\nRoath\r\nCardiff'),
('C107', 'Sam', 'Wilson', '1993-11-05', 'Male', 587876786,
'[e-mail]', '14 Tottenham Court Road\r\nLondon');
Airlines Table
INSERT INTO `airlines` (`AirlinesName`, `PhoneNumber`,
`ContactPerson`) VALUES
('British Airways', 86876786, 'Harry Pratt'),
('EasyJet', 78678678, 'Tim Sane'),
('Jet2', 86878766, 'Laura Paine'),
('Ryanair', 856786767, 'James cameron');

12DATABASE SYSTEMS
Booking Table
INSERT INTO `booking` (`BookingID`, `Date`, `FlightNumber`,
`FlightDate`, `CustID`) VALUES
('B101', '2019-11-12', 'BX098', '2020-02-21', 'C101'),
('B102', '2020-02-03', 'ES656', '2020-02-19', 'C102'),
('B103', '2020-02-02', 'BX187', '2020-02-22', 'C103'),
('B104', '2020-01-14', 'BX457', '2020-03-19', 'C104'),
('B105', '2020-02-03', 'ES232', '2020-02-22', 'C105'),
('B106', '2019-11-13', 'ES765', '2020-02-22', 'C106'),
('B107', '2019-12-18', 'JK54', '2020-02-15', 'C107'),
('B108', '2019-12-11', 'ES232', '2020-04-10', 'C101'),
('B109', '2020-01-07', 'RY63', '2020-02-14', 'C102'),
('B110', '2020-01-08', 'RY65', '2020-02-21', 'C103'),
('B111', '2020-02-02', 'JK54', '2020-02-25', 'C104'),
('B112', '2020-01-14', 'ES765', '2020-03-17', 'C105'),
('B113', '2020-01-07', 'ES765', '2020-02-15', 'C106'),
('B114', '2020-01-15', 'JK54', '2020-02-29', 'C107'),
('B115', '2019-12-19', 'BX457', '2020-05-14', 'C104');
Flights Table
INSERT INTO `flights` (`FlightNumber`, `AirlinesName`, `Source`,
`Destination`, `Fare`) VALUES
('BX098', 'British Airways', 'London', 'Singapore', '4587.00'),
('BX187', 'British Airways', 'London', 'Peru', '7458.00'),

13DATABASE SYSTEMS
('BX457', 'British Airways', 'London', 'New York', '8759.00'),
('ES232', 'EasyJet', 'London', 'Macau', '8785.00'),
('ES656', 'EasyJet', 'Bristol', 'Capetown', '7458.00'),
('ES765', 'EasyJet', 'Birminghum', 'Iran', '4587.00'),
('JK54', 'Jet2', 'Hawai', 'London', '4785.00'),
('JK98', 'Jet2', 'Cambridge', 'Sanghai', '5485.00'),
('RY63', 'Ryanair', 'Ohio', 'Trentbridge', '7485.00'),
('RY65', 'Ryanair', 'Edgbaston', 'Thailand', '2547.00');
Payment Table
INSERT INTO `payment` (`PaymentID`, `PaymentMode`, `BookingID`) VALUES
('P101', 'Credit Card', 'B101'),
('P102', 'Credit Card', 'B102'),
('P103', 'Debit Card', 'B103'),
('P104', 'Internet Banking', 'B104'),
('P105', 'Internet Banking', 'B105'),
('P106', 'Debit Card', 'B106'),
('P107', 'Internet Banking', 'B107'),
('P108', 'Credit Card', 'B108'),
('P109', 'Debit Card', 'B109'),
('P110', 'Internet Banking', 'B110'),
('P111', 'Debit Card', 'B111'),
('P112', 'Credit Card', 'B112'),
('P113', 'Internet Banking', 'B113'),
('P114', 'Debit Card', 'B114');

14DATABASE SYSTEMS
Queries
Business reports can be obtained by running specific queries in a database. It helps in analyzing
the business in terms of cost-benefit, customer-behavior analysis, and future predictions. Some
of the important query for the EasyTravels are given along with the snapshot of the outputs.
1. List customers and their contact details.
select custid, firstname, lastname, contact, email, address from
customer;

15DATABASE SYSTEMS
2. List Airlines and their flights according to price from high to low.
select a.airlinesname, f.flightnumber, f.source, f.destination,
f.fare FROM
airlines a inner join flights f on f.AirlinesName=a.AirlinesName
order BY
f.fare desc;
3. List the Airlines who provide flights from London.
select a.airlinesname, f.flightnumber, f.fare FROM
airlines a inner join flights f on f.AirlinesName=a.AirlinesName
and f.Source='london' order BY
f.fare desc;

16DATABASE SYSTEMS
4. List the customers and their upcoming flights.
select c.firstname, c.lastname,b.flightnumber, b.flightdate from
booking b
inner join customer c on c.CustID=b.CustID and
b.FlightDate>DATE(NOW())
order by b.flightdate asc;
5. List the Airlines and number of flights they provide.
select a.AirlinesName, count(f.flightnumber) as Total_flights
from flights f
inner join airlines a on a.AirlinesName=f.AirlinesName
GROUP BY a.AirlinesName order by count(f.flightnumber) desc;
6. List the customer names and the number of flights they have booked.
select CONCAT(c.firstname,' ',c.lastname) as customers,
count(b.flightnumber) as Total_flights, b.flightdate from booking
b
inner join customer c on c.CustID=b.CustID group by
c.Firstname,c.Lastname;

17DATABASE SYSTEMS
7. List the customers who have not paid for their booking.
select CONCAT(c.firstname,' ',c.lastname) as customers,
b.flightnumber, b.flightdate FROM
booking b inner join customer c on c.CustID=b.CustID and
b.BookingID not in (select BookingID from payment);
8. List the Revenue from the bookings. Group the amount for the airlines.
select a.airlinesname, sum(f.fare) as Total from flights f inner
join airlines a
on a.AirlinesName=f.AirlinesName group by a.AirlinesName order by
sum(f.fare) desc;
9. List the bookings which has been paid with internet banking mode.
select b.bookingid, b.flightnumber, b.flightdate from booking b
inner JOIN
payment p on p.BookingID=b.BookingID and p.PaymentMode='Internet
banking';
10. List the customer name and their phone numbers who are travelling to Peru in
Britih Airways.
select CONCAT(c.firstname,' ',c.lastname) as customers, c.contact
from customer c
inner join booking b on b.CustID=c.CustID inner join flights f
on f.FlightNumber=b.FlightNumber inner join airlines a on
a.AirlinesName=f.AirlinesName
and a.AirlinesName='British Airways' and f.Destination='Peru';

18DATABASE SYSTEMS
Conclusion and Recommendation
Database management system is much efficient for storing, organizing and retrieving
data. It can be concluded that it is more than a database. In this scenario of the EasyTravel, a
database management system is successfully created to enhance the booking process and
reservation management system for the company. The entity and business process identification
is done by assuming for a conventional travelling agency’s business processes. Lastly, the
business reports for the company has been done with the help of quires. It can be said that the
DBMS implementation for the EasyTravel Company is accurately and successfully done.
However, for the entire business process it is recommended to integrate all of the
valuable entities for a travel agency. The more the structure of the database will get detailed
information, it will allow the system to produce more effective business reports. A registration
and login system can be implementation for future purpose. In terms of security, the sensitive
information, such as customer address and contact can be encrypted.

19DATABASE SYSTEMS
References
DuBois, P., 2014. MySQL Cookbook: Solutions for Database Developers and Administrators. "
O'Reilly Media, Inc.".
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems (Vol. 7). Pearson.
Isakh, R.R. and Widagdo, T.E., 2016, October. Business rules handling in relational databases.
In 2016 International Conference on Data and Software Engineering (ICoDSE) (pp. 1-6). IEEE.
Korotkevitch, D., 2014. Constraints. In Pro SQL Server Internals (pp. 151-163). Apress,
Berkeley, CA.
Laudon, K.C. and Laudon, J.P., 2015. Management information systems (p. 143). Upper Saddle
River: Pearson.
West, A.W. and Prettyman, S., 2018. Create and test a database and table. In Practical PHP 7,
MySQL 8, and MariaDB Website Databases (pp. 1-31). Apress, Berkeley, CA.