Database

Administration &
Management

Project

Submitted By:
Esha Alamgir 20101003-137
Minahil Sajid 20101003-155
Nimra Azmat 20101003-179
Hinata Adil 20101003-208
Department:
BS-IT
Semester:
8th
Section:
Purple
Submitted To:
Sir Faraz Baig
Topic:
Airline Reservation System
Submission Date: 24th May,2024.

1
Table of Contents
Airline Reservation System .................................................................................................................. 3
1- Introduction .................................................................................................................................. 3
2- Methodology .................................................................................................................................. 3
2.1 Entity-Relationship Diagram (ERD) & Extended Entity-Relationship Diagram (EERD): . 3
2.1.1 Entities: ................................................................................................................................. 4
2.1.2 Attributes: ............................................................................................................................. 4
2.1.3 Relationship: ......................................................................................................................... 4
2.1.4 Entity-Relationship Diagram (ERD): ................................................................................. 5
2.1.5 Extended Entity-Relationship Diagram (EERD): ............................................................. 6
2.2 Applying Normalization: ............................................................................................................ 6
2.2.1 Potential Issues and Resolutions:........................................................................................ 7
2.3 SQL Schema: ............................................................................................................................... 7
2.3.1 Referential integrity: ............................................................................................................ 9
References ............................................................................................................................................ 10

Figure 1.1 Entity Relationship Diagram ERD ........................................................................................ 5

Figure 1.2 Extended Entity Relationship Diagram (EERD) ................................................................... 6

2
 Airline Reservation System
1- Introduction
In the modern aviation industry, managing flight operations efficiently is crucial for
airline companies. An Airline Reservation System (ARS) plays a vital role in handling flights,
passengers, reservations, and crew assignments. This system ensures that flights are scheduled
efficiently, and changes or cancellations are managed smoothly. This project aims to design a
robust database for an ARS that supports these operations effectively.

The database design will incorporate an Entity-Relationship Diagram (ERD) and an

Extended Entity-Relationship Diagram (EERD) to capture all relevant entities and their
relationships. The design will undergo normalization to eliminate redundancy and prevent
update anomalies, ensuring data integrity and consistency. Finally, an SQL schema will be
created to define the structure of the database, including primary keys, foreign keys, and
constraints to enforce referential integrity.

2- Methodology
The methodology for designing the Airline Reservation System (ARS) database
involves several key steps to ensure efficiency and data integrity.

• First, we identify the primary entities: Flights, Passengers, Reservations, Airline,

Aircraft, CrewMembers, and CrewAssignments, along with their attributes and
relationships.

• Using Crow’s Foot notation, we construct both an ERD and an EERD to visually
represent these entities and their interactions, including cardinality and participation
constraints.

• Next, we apply normalization techniques up to the Third Normal Form (3NF) to

eliminate redundancy and prevent update anomalies. This involves ensuring that all
tables have primary keys, removing partial dependencies, and eliminating transitive
dependencies.

• Any potential issues, such as redundant data in reservations or complexities in crew

assignments, are resolved through strategic table separation and the use of junction
tables.

• Finally, we create an SQL schema defining the structure of the database with primary
keys, foreign keys, and constraints to enforce referential integrity and maintain
consistent, valid data entries.

• This comprehensive approach ensures that the ARS database supports efficient flight
scheduling, flexible management of changes and cancellations, and robust data
integrity.

2.1 Entity-Relationship Diagram (ERD) & Extended Entity-Relationship

Diagram (EERD):
The Entity-Relationship Diagram (ERD) for the Airline Reservation System (ARS)
visually represents entities, their attributes, and relationships, ensuring efficient flight
management. The Extended Entity-Relationship Diagram (EERD) further details these

3
elements with additional attributes, constraints, and specialized relationships for enhanced data
integrity and operational flexibility.

2.1.1 Entities:
• Airports

• Flights

• Aircrafts

• Airlines

• Reservations

• Passengers

• Crew

• Crew Assignments

2.1.2 Attributes:
1- Airports: Airport ID <PK>, Name, Country, Code, City.

2- Flights: Flight ID <PK>, Flight Number, Departure Date/ Time, Arrival

Date/ Time, Departure Airport ID <FK>, Arrival Airport ID
<FK>, Aircraft ID <FK>, Airline ID <FK>.

3- Aircrafts: Aircraft ID <PK>, Aircraft Model, Total Seats, Airline ID <FK>.

4- Airline: Airline ID <PK>, Airline Name, Airline Code.

5- Reservations: Reservation ID <PK>, Flight ID <FK>, Passenger ID <FK>,

Seat Number, Reservation Status.

6- Passengers: Passenger ID <PK>, First Name, Last Name, Phone Number,

Email, DOB.

7- Crew: Crew ID <PK>, First Name, Last Name, Role, Airline ID <FK>.

8- Crew Assignment: Duty ID<PK>, Crew ID <PK>, Role.

2.1.3 Relationship:
➢ The Airlines entity has a one-to-many relationship with Aircrafts and Flights,
indicating that one airline can have multiple aircrafts and flights.
➢ The Airports entity has a one-to-many relationship with Flights, indicating that one
airport can have multiple departing and arriving flights.
➢ The Aircrafts entity has a one-to-many relationship with Flights, indicating that one
aircraft can be assigned to multiple flights.
➢ The Flights entity has a one-to-many relationship with Reservations, indicating that
one flight can have multiple reservations.

4
 ➢ The Passengers entity has a one-to-many relationship with Reservations, indicating
that one passenger can have multiple reservations.
➢ A Crew member can be assigned to multiple flights and roles.
➢ A Crew assignment is associated with one flight, one crew member, and one role.

2.1.4 Entity-Relationship Diagram (ERD):

The Entity-Relationship Diagram (ERD) for the Airline Reservation System (ARS)
visually represents entities, their attributes, and relationships, ensuring efficient flight
management.

Figure 1.1
1 Entity
EntityRelationship
RelationshipDiagram
Diagram(ERD)
ERD

5
2.1.5 Extended Entity-Relationship Diagram (EERD):
The Extended Entity-Relationship Diagram (EERD) further details these elements with
additional attributes, constraints, and specialized relationships for enhanced data integrity and
operational flexibility.

Figure 1.2 Extended Entity Relationship Diagram (EERD)

2.2 Applying Normalization:

Normalization is a process used in database design to organize data efficiently, reducing
redundancy and minimizing update anomalies.

To ensure that the database design for the airline reservation system is free of
redundancy and update anomalies, I applied normalization techniques to achieve at least the
Third Normal Form (3NF).

Here's a detailed explanation of the normalization process:

1. First Normal Form (1NF): The first step in the normalization process is to eliminate
repeating groups and ensure that each cell in a table contains a single value. In the
initial design, all entities were already in 1NF, as there were no repeating groups or
multi-valued attributes.
2. Second Normal Form (2NF): To achieve 2NF, the table must be in 1NF, and all
non-key attributes must be fully dependent on the entire primary key. In the initial

6
 design, all entities were already in 2NF, as there were no partial dependencies on the
primary keys.
3. Third Normal Form (3NF): To achieve 3NF, the table must be in 2NF, and there
should be no transitive dependencies, meaning that all non-key attributes must depend
directly on the primary key and not on any other non-key attributes. During the
normalization process to 3NF, I identified a potential transitive dependency issue in
the Flights entity. The AirlineID attribute was dependent on the AircraftID attribute,
which in turn was dependent on the primary key FlightID.

To resolve this, I created a new entity called AircraftAssignments with the following
structure:

The AircraftAssignments entity serves as an associative entity between Flights and

Aircrafts, eliminating the transitive dependency. The Flights entity was modified as
follows:

The AircraftID attribute was removed from the Flights entity, as it is now represented
through the AircraftAssignments entity. After making this change, the database
design achieved the Third Normal Form (3NF), ensuring that there are no transitive
dependencies and that all non-key attributes depend directly on the primary key.

2.2.1 Potential Issues and Resolutions:

• Issue: Transitive dependency in the Flights entity, where the AirlineID attribute was
dependent on the AircraftID attribute, which in turn was dependent on the primary
key FlightID.
• Resolution: Created a new associative entity AircraftAssignments to remove the
transitive dependency and maintain the relationship between Flights and Aircrafts.
The AircraftID attribute was removed from the Flights entity, and the relationship
was represented through the AircraftAssignments entity.

By applying the normalization techniques up to the Third Normal Form (3NF),

the database design for the airline reservation system is free of redundancy and update
anomalies. This ensures data integrity, reduces redundant data storage, and minimizes
the risk of inconsistencies or anomalies during data manipulation operations.

2.3 SQL Schema:

CREATE TABLE Airlines ( AirlineID INT PRIMARY KEY,

AirlineName VARCHAR(50) NOT NULL, AirlineCode CHAR(3) NOT
NULL UNIQUE);

CREATE TABLE Airports (AirportID INT PRIMARY KEY, AirportName

VARCHAR(100) NOT NULL, AirportCode CHAR(3) NOT NULL UNIQUE,
City VARCHAR(50) NOT NULL, Country VARCHAR(50) NOT NULL);

CREATE TABLE Aircrafts ( AircraftID INT PRIMARY KEY,

AircraftModel VARCHAR(50) NOT NULL, TotalSeats INT NOT NULL,

7
AirlineID INT NOT NULL, FOREIGN KEY (AirlineID) REFERENCES
Airlines(AirlineID));

CREATE TABLE Flights ( FlightID INT PRIMARY KEY, FlightNumber

VARCHAR(10) NOT, DepartureDateTime DATETIME NOT NULL,
ArrivalDateTime DATETIME NOT NULL, DepartureAirportID INT NOT
NULL, ArrivalAirportID INT NOT NULL, AirlineID INT NOT NULL,
FOREIGN KEY (DepartureAirportID) REFERENCES
Airports(AirportID), FOREIGN KEY (ArrivalAirportID)
REFERENCES Airports(AirportID), FOREIGN KEY (AirlineID)
REFERENCES Airlines(AirlineID));

CREATE TABLE Passengers ( PassengerID INT PRIMARY KEY,

FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT
NULL, Email VARCHAR(100) NOT NULL UNIQUE, PhoneNumber
VARCHAR(20) NOT NULL);

CREATE TABLE Reservations ( ReservationID INT PRIMARY KEY,

FlightID INT NOT NULL, PassengerID INT NOT NULL, SeatNumber
VARCHAR(10) NOT NULL, ReservationStatus VARCHAR(20) NOT NULL,
FOREIGN KEY (FlightID) REFERENCES Flights(FlightID), FOREIGN
KEY (PassengerID) REFERENCES Passengers(PassengerID));

CREATE TABLE Crew ( CrewID INT PRIMARY KEY, FirstName

VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Role
VARCHAR(50) NOT NULL, AirlineID INT NOT NULL, FOREIGN KEY
(AirlineID) REFERENCES Airlines(AirlineID));

CREATE TABLE CrewAssignments ( DutyID INT PRIMARY KEY,

FlightID INT NOT NULL, CrewID INT NOT NULL, Role VARCHAR(50)
NOT NULL, FOREIGN KEY (FlightID) REFERENCES
Flights(FlightID), FOREIGN KEY (CrewID) REFERENCES
Crew(CrewID));

CREATE TABLE AircraftAssignments ( AircraftAssignmentID INT

PRIMARY KEY, FlightID INT NOT NULL, AircraftID INT NOT NULL,
FOREIGN KEY (FlightID) REFERENCES Flights(FlightID), FOREIGN
KEY (AircraftID) REFERENCES Aircrafts(AircraftID));

• Each CREATE TABLE statement defines a table with its respective columns and data
types.
• Primary keys are defined using the PRIMARY KEY constraint for each table.
• Foreign key constraints are defined using the FOREIGN KEY constraint, referencing
the primary key of the related table.

8
 • Appropriate constraints like NOT NULL and UNIQUE are applied to ensure data
integrity.
• The AircraftAssignments table is introduced to remove the transitive dependency and
maintain the relationship between Flights and Aircrafts.

2.3.1 Referential integrity:

With this SQL schema, the database enforces referential integrity by ensuring that:

• An airline must exist before assigning it to an aircraft or a crew member.

• An airport must exist before assigning it as a departure or arrival airport for a flight.
• A passenger must exist before creating a reservation.
• A flight must exist before creating a reservation or crew assignment.
• An aircraft must exist before assigning it to a flight.
• A crew member must exist before assigning them to a flight.

9
References
(n.d.). Retrieved from [Link]

(n.d.). Retrieved from [Link]

(n.d.). Retrieved from [Link]

10