Flight Booking Database System - Entity Relationship Diagram Essay.

The database system needs to record the customer information. For each customer, in addition to an identification number assigned, it is necessary to record their full name, address, and contact phone. In addition, the customers are generally categorized into two types: individual and business. For an individual customer, whether the customer has a personal travel insurance cover is recorded, while for a business customer the organization name, Australian Business Number (ABN) and whether the business travel special deal is applicable are recorded. A customer can book one or more flights.

To book a flight, a customer enters the system interface and selects the required flights and dates. The booking has a unique reservation code, booking date and total price. Each booking may contain a single flight or multiple-journey flights. For each booked flight, the travel date, departure and arrival time, departure and arrival airport, class, and seat number must be recorded. At the end of a booking, the payment must be made and recorded. A typical example of booking can be shown as below.

Passenger name: John Smith
Reservation code: ZYX209
VA 1234 10/12/2018 8:30am->9:40am ROK->BNE seat number: 5A
VA 1245 12/12/2018 3:20pm->4:35pm BNE->ROK seat number: 18B

Total price: $310 The above specification is not presented in every detail for entities or relationships explicitly you need
to model, because one of your tasks for this assignment is to analyze the user requirement and make assumptions on the data model and business rules. You may need to do some research by browsing some websites such as Qantas, Virgin Blue and Skyscanner etc.

In this assignment, you need to create an Entity Relationship (ER) diagram relevant to the above case study and optionally perform logical design to produce appropriate 3NF Relations.
The attributes for various entities have not been purposely described in the case study so that the students are encouraged to further research and list the pertinent attributes in addition to the required identifier for each and every entity in their ERD.
1 Draw an appropriate ER diagram
Use the symbols as prescribed in your unit-textbook to draw the ER diagram (ERD) for the above case study.
Your ERD must

• show all necessary entities, attributes and relationships
• show identifiers/identifying attributes as necessary
• show multi-valued attributes, if any
• show participation and cardinality
• show associative entities, if appropriate
• show weak entities, if any
• use the notation described in the set text
• use consistent and appropriate naming for entities and attributes as specified in the unit text book

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,

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,

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,

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.