Flight Booking Database System – Entity Relationship Diagram

Entities

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.

1. Entity Relationship Diagram

1.1 Entities

The flight booking database system has the following entities such as,

• Customer
• Booking
• Customer Type
• Payment
• Flight
• Individual customers
• Business Customers
• Invoice

1.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).

Attributes

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.

1.3 Associative Entities

The Associate Entities are listed below:

• Customer Entity
• Booking Entity
• Flight Entity
• Payment Entity
• Invoice Entity

1.4 Weak Entities

The weak entities are listed below:

• Customer Type Entity
• Individual Customers Entity
• Business Customer Entity

2. 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).

 

 3. 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.

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.

4. 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
C02	John	1254787859	1000250
C03	Joyce	1254785025	1000250

Post Code	Street	State
1000250	MR Street	US
1000250	West Street	US
1000250	South Street	US

References

Pathak, N. (2011). Database Management System. [S.L.]: Himalaya Publishing House.

Silberschatz, A., Korth, H. and Sudarshan, S. (2011). Database system concepts. New York: McGraw-Hill.

Silberschatz. (2019). Database System Concepts. [S.L.]: Mcgraw-Hill Education.