Airline Database Project: Design, Normalization, and Queries

This assignment presents a comprehensive solution for a database project focusing on the design and implementation of an airline database system. The project begins with the creation of an Entity Relationship Diagram (ERD) to represent the entities and relationships within the system. The solution then demonstrates the process of normalization, progressing from the first to the third normal form to optimize the database structure and eliminate data redundancy. The assignment includes dependency diagrams to illustrate the normalization steps. The project also covers the creation of tables and SQL queries to retrieve and manipulate data. The queries include examples of SELECT statements, JOIN operations, and aggregate functions. A report summarizing the findings and a bibliography of cited resources are also included. The project addresses the assessment brief's requirements for designing, building, and querying a relational database, demonstrating knowledge of data access and retrieval.

Running head: DATABASE MANAGEMENT SYSTEM
DATABASE MANAGEMENT SYSTEM
Name of the Student
Name of the University
Author Note

1DATABASE MANAGEMENT SYSTEM
Table of Contents
ERD.................................................................................................................................................2
Normalization..................................................................................................................................3
First Normal Form.......................................................................................................................3
Second Normal Form...................................................................................................................3
Third Normal Form......................................................................................................................4
Form.................................................................................................................................................6
Tables...............................................................................................................................................6
Queries.............................................................................................................................................9
Report............................................................................................................................................11
Bibliography..................................................................................................................................12

2DATABASE MANAGEMENT SYSTEM
ERD
Entity Relationship Diagram represents the real world objects as entities and relationship
between them. Every entity has some attributes related to the entity itself. The relationship used
in the diagram are either one to one, one to many, zero to many or many to many. The below
diagram represents the Entities of the Airline system. The ERD is normalized in the later section
of the Report up to Third normal form.

3DATABASE MANAGEMENT SYSTEM
Figure 1: Entity Relationship Diagram
Source: Created by author
Normalization
Normalization is the process of simplifying the Tables and relationship between them. It
starts from the first normal form to the second normal form.
First Normal Form
According to the first normal form of the normalization, the tables must have all atomic
values and there are no repeating values. All the attributes are related to their entities and
relationship has been established according to the scenario.
Second Normal Form
Second Normal form says that the relationship should be in the first normal form and all
the non-key attributes should be fully functionally dependent on the key attribute of the table. In
the current scenario, table airplanes, flights and reservation are not in second form as, the type
and capacity of the airplane does not depends on the airplane no. hence; it will require an extra
sets of attribute which will be a new entity. In flight table, the flight date does not depends on the
flight no and in reservation table ticket description and status does not depend on the booking id.
Similarly the breakups of the table have been made to normalize the relationship into second
normal form. Below an example of the dependency diagram for the second normalized from has
been shown:

4DATABASE MANAGEMENT SYSTEM
Figure 2: Dependency diagram for Ticket table
Source: Created by author
Figure 3: Dependency diagram for Type table
Source: Created by author
Figure 4: Dependency diagram for Reservation table
Source: Created by author
Third Normal Form
The Third normal form says that the table should satisfy the second normal form and it
should not have any transitive functional dependencies. In this scenario, the transitive functional
dependency is present in the reservation and flight leg table s where the Flight no. identified by
the leg no. and booking id both, which creates a loop in relationship between reservations, fight
and flight leg table. However, the correct relationship dependency will be that when the leg no. is
identified by the booking id and flight no. is identified by the leg no. where the flight no for a

5DATABASE MANAGEMENT SYSTEM
booking will be identified through the flight leg table. The final normalized up to third normal
form relationship has been shown in the figure 5.
Figure 5: Normalized Entity relationship Diagram
Source: created by author

6DATABASE MANAGEMENT SYSTEM
Form
Tables
Airplane Table
Airports Table

7DATABASE MANAGEMENT SYSTEM
Customer Table
FlightLeg Table
Flights Table

8DATABASE MANAGEMENT SYSTEM
Reservation Table
Ticket Table

9DATABASE MANAGEMENT SYSTEM
Type Table
Queries
SELECT
SELECT Airplanes.AirplaneNo, Airplanes.ManufacturingCompany, Airplanes.TypeName,
Type.Capacity
FROM Type INNER JOIN Airplanes ON Type.TypeName = Airplanes.TypeName;

10DATABASE MANAGEMENT SYSTEM
JOIN
SELECT Customer.CustomerID, Customer.Name, Customer.Title, Reservation.FlightDate,
Flights.FlightNo, Flights.Destination, FlightLeg.DepartureAirport
FROM Flights INNER JOIN (FlightLeg INNER JOIN (Customer INNER JOIN Reservation ON
Customer.CustomerID = Reservation.CustomerID) ON FlightLeg.LegNo = Reservation.LegID)
ON Flights.FlightNo = FlightLeg.FlightNo;
AGGREGATE
SELECT Flights.FlightNo, Flights.Destination, Count(FlightLeg.LegNo) AS CountOfLegNo
FROM Flights INNER JOIN FlightLeg ON Flights.FlightNo = FlightLeg.FlightNo
GROUP BY Flights.FlightNo, Flights.Destination;

11DATABASE MANAGEMENT SYSTEM
Report
All Customers Flight Report