COIT20247 T3 2018: Database Design and Development for Flight Booking

This project outlines the design and development of a database system for flight booking, addressing the requirements of a travel metasearch engine platform. The project includes an Entity Relationship (ER) diagram detailing entities such as Customer, Booking, Flight, and Payment, along with their respective attributes. Assumptions and business rules are defined to guide the database structure, followed by a logical design that maps the ER diagram to specific attributes within entities like Customer and Flight. The project concludes with a discussion of 3NF relations, providing examples of normalization to avoid data redundancy. This comprehensive database design ensures efficient recording and tracking of flight information, customer details, and booking data.

Database Design and
Development

Table of Contents
1 Project Objective.................................................................................................................3
2 Entity Relationship Diagram..............................................................................................3
2.1 Entities.........................................................................................................................3
2.2 Attributes.....................................................................................................................4
2.3 Associative Entities.....................................................................................................7
2.4 Weak Entities...............................................................................................................7
3 Assumptions and Business rules.........................................................................................8
4 Logical Design..................................................................................................................10
5 3NF Relations...................................................................................................................12
References...........................................................................................................................................14

1. Project Objective
The main objective of this project is to design and implement a database system for
managing the business of flight booking. It is assumed that the IT service company is hiring
you to design and implement the database system for managing the business of flight
booking. So, it is required to design the relational database and it can be served as an
important software component for a travel Meta search engine platform. The relational
database requires the relevant business data, which are recorded and processed efficiently.
The proposed database designs requires tracking and recording all the information of flights
such as, airlines, flight number, flight name, customers details, ticket booking, airports, and
so on. The flight information consists of the following attributes such as, flight number,
departure and arrival airports, departure and arrival time, price, type of aircraft and airline
code. The proposed database system also needs to record the customer information such as,
customer full name, address and phone number. The customers are divided into two types
like, individual and business customers. Any individual customer will have a personal travel
insurance cover, which is recorded. The business customers’ details are also recorded like,
organization name, special deal and Australian business number. The proposed data base
system is allowing the customer to book one or more flights. The Flight booking details are
recorded in the proposed database system such as, unique reservation code, booking data, and
total price. The booked flight details are recorded like, booked flight number, departure and
arrival time, airport, class, seat number and travel date. Finally, the customer makes the
payment and this information is also recorded in the proposed database system.
2. Entity Relationship Diagram
2.1 Entities
The flight booking database system has the following entities such as,
 Customer
 Booking
 Customer Type
 Payment
 Flight
 Individual customers
 Business Customers
 Invoice

2.2 Attributes
The Customer table contains the following attributes such as, Customer ID, Full
name, address and contact phone number. Here, the customer ID is, the Primary key (Pathak,
2011).
The Flight table contains the following attributes such as, flight number, airline code,
departure time, arrival time, departure airports, arrival airports, price and types of aircraft.

The payment table contains the following attributes such as, payment ID, customer ID,
booking ID and cash or cash payment. Here, the Payment ID is the primary key and the
Booking ID is the foreign key.
The customer Type contains the following attributes such as, customer ID, individual and
business customer. Here, the customer ID is the Primary key.
The individual customer table contains the following attributes such as, customer ID and
travel insurance.
The Business customer table contains the following attributes such as, customer ID,
organization name, ABN and special deal. Here, the customer ID is the primary key.

The booking table contains the following attributes such as, unique reservation code, booking
date, total price, booked flight ID, single flights, multiple flights, departure and arrival time,
departure and arrival time airports, class, seat number and travel date. Here, the booking ID is
the primary key and the flight ID is the foreign key (Silberschatz, Korth and Sudarshan,
2011).

The invoice table contains the following attributes such as, invoice number, payment ID,
invoice date and customer ID. Here, the invoice number is the primary key and the customer
ID and Payment IDs are the foreign keys.
2.3 Associative Entities
The Associate Entities are listed below:
 Customer Entity
 Booking Entity
 Flight Entity
 Payment Entity
 Invoice Entity
2.4 Weak Entities
The weak entities are listed below:
 Customer Type Entity
 Individual Customers Entity
 Business Customer Entity

3. Assumptions and Business rules
Assumptions
We are assuming the following entities for the proposed database system such as,
individual customer table, business customers and invoice entities.
The individual customer table contains the following attributes such as, customer ID
and travel insurance.

The Business customer table, contains the following attributes such as, customer ID,
organization name, ABN and special deal. Here, the customer ID is the primary key.
The invoice table, contains the following attributes such as, invoice number, payment ID,
invoice date and customer ID. Here, the invoice number is the primary key and the customer
ID and Payment IDs are the foreign keys.
Business Rules
Recognizing and reporting business rules are critical to the database structure.
Business rules enable the maker to create relationship participation rules and requirements
and to make right data demonstration. They likewise enable the makers to comprehend the
business forms, and the nature, job and extent of the information. It is essential to remember
that some business rules cannot be displayed. Business Rules give correct entities, attributes,
relationships, and constraints. The flight booking database business rules are listed below
(Silberschatz., 2019).

4. Logical Design
The logical design for the flight booking data system is shown below. The proposed
database design is used to track and record all the information of the flights such as, customer
details, ticket booking, airports, airlines, flights and more. The flight information consists of
the following attributes such as, flight number, departure and arrival airports, departure and
arrival time, price, type of aircraft and airline code. The proposed database system records the
customer information such as, customer full name, address, street, state, suburb, post code
and phone number. The customers are divided into two types like, individual and business
customers. Any individual customer will have a personal travel insurance cover, which is
recorded. The business customers’ details are also recorded like, organization name, special
deal and Australian business number. The proposed data base system is allowing the
customer to book one or more flights. The Flight booking details are recorded in the proposed
database system such as, unique reservation code, booking data, and total price. The booked
flight details are recorded like, booked flight number, departure and arrival time, airport,
class, seat number and travel date.
Customer Flight
View the Flight
Information
Flight Booking
Book a flight
Booking Payment
Make Payment
Payment Payment
Provide the invoice

When compared to the Entity relationship diagram, the logical design is used to
identify the created entity relationship diagram, which meets the business rules. So, here, we
are include the following attributes on the created entities. In customer entity table, record the
customer information such as, customer full name, address, street, state, suburb, Post code
and phone number. The other entities meet the business rules in the database system.

5. 3NF Relations
The organization of the data is called as normalization. Basically, normalization is
used to avoid the data redundancy in a database and it is classified into five types such as 1
NF, 2 NF, 3 NF, 4 NF and 5 NF.
For 1 NF,
Basically, the first normal form does not contain the repeated values.
Consider the below example,
Customer ID Full name phone number Street State Post Code
C01 Joseph 1254785963 MR Street US 1000250
C02 John 1254787859 West Street US 1000250
C03 Joyce 1254785025 South Street US 1000250
There are no repeated values. So, the above table satisfies the first normal form rule.
For 2 NF,
The 2NF follows the two rules such as, must use 1NF table and No non-prime
attributes are dependent on the subnet.
Consider the below example,
Customer ID Full name phone number
C01 Joseph 1254785963
C02 John 1254787859
C03 Joyce 1254785025
Customer ID Street State Post Code
C01 MR Street US 1000250
C02 West Street US 1000250
C03 South Street US 1000250
For 3 NF,
The 3 NF also uses the two rules such as, must use second normal form table and remove
transitive functional dependency.
Consider the below example,
Customer ID Full name phone number Post Code
C01 Joseph 1254785963 1000250