Database Systems: Design and Implementation for EasyTravel Group

This report details the design and implementation of a database system for EasyTravel Group, a UK-based travel platform. It begins with an introduction outlining the project's goal: to enhance the company's booking system using a relational database management system (RDBMS) approach. The report then specifies the software and hardware requirements, including XAMPP and MySQL Workbench, and hardware specifications such as Windows 7 and 2GB RAM. A key component is the Entity-Relationship (ER) diagram, visually representing the relationships between entities like customers, airlines, bookings, and flights. Business rules are defined to govern the system's functionality, followed by the database implementation, which includes Data Definition Language (DDL) statements for creating tables (customer, airlines, booking, flights, payment) with appropriate data types and constraints. Foreign key constraints are implemented to ensure data integrity. The report also includes Data Manipulation Language (DML) statements for inserting sample data into the tables. Furthermore, the report provides a series of SQL queries to generate business reports, such as customer details, flight listings, airline performance, and revenue analysis. The conclusion summarizes the successful DBMS implementation, highlighting the efficiency in data storage and retrieval, and recommends future enhancements like integrating additional entities and implementing a registration/login system, along with security measures such as data encryption. References to relevant literature are also provided.

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');