Database Systems Final Project: Aerozen Inc. Database Design

This project focuses on designing a database for Aerozen Inc., an IT services provider specializing in travel portals. The assignment involves creating an efficient and optimized database to support an airline reservation system. The project includes business scenarios, database design consideratio...Read More

Business scenario #1:
 Aerozen Inc., is in the business of providing IT support and services to some of the major
travel portals around the world. Their newest portal is a novel one and it requires
maintaining an active database for it’s customers. They need to develop a database that is
both efficient, fast and highly optimized so that it can support their client’s businesses.
 Aerozen Inc., is the name of our client. However, they in turn provide services to some of
the major tour operators around the world.
 They are into IT services and support and provide ITes to major travel operators around
the world.
 The head of IT outsourcing is handling the project.
 Their expectation is to deliver a working database design that would suit their
requirements in the given timeframe.
Business scenario #2:
 They are not facing any problem as such. Their new client wishes to have an advanced
airline reservation system built-in that would not only allow them to book airline tickets
but also want to record a trail of activities that their customer perform across their system
and then process that data and convert into actionable insights for them as well as provide
enhanced services for their customers. As a result, their newer system is a novel one and
would want a highly efficient database engine for it.
 They need the database so as to record their customer’s activities on the web and mobile
into one single profile.
 The database would help prove their business beneficial as it would help them make
sense of what their customers are doing on the web and record all the necessary activities
corresponding to their visit on the web.
Business Section # 3
The development of a database which can be accessed from any location and all data gets
stored at centralized position will make it easier for the customers to reserve flights without any
conflicts with one another.

Database Design # 1
Following features will be implemented while developing proposed system:
 Airline reservation – A customer will be able to book / reserve airline for the
travel by providing start and end dates.
 Customer registration – A customer will be able register oneself on the system to
reserve an airline.
 Facility allocation – The administrator should be able to assign facility to
different airlines.
 Marking prices of flights – The system should be able to mark prices for airlines
daily and also for a specific date.
Database Design # 2
Following are the assumptions:
 A customer is able to access the system and also register oneself.
 A customer can reserve an airline
 A customer is able to check for available airlines.
 An admin is able to modify the number of available seats.
 An admin is able to access the system to mark prices.

First ER diagram

Final ER diagram
Data dictionary
Table Description
T_AIRLINE This table contains all the information related to an airline.
Attribute Description Primary Key / Foreign
Key
airlineID Unique identifier given
to every airline.
Primary key
airlineName Full name given to an
airline
-

description Full description of an
airline
-
state State of the country to
which airline travels
-
country Country to which airline
travels
-
location Location of the airline -
aType Type of the airline i.e.
public or private owned
-
Number of records 5 minimum and maximum can be 1000+
T_PRICES This table contains all the information related to price of an airline
for a particular day/date.
Attribute Description Primary Key / Foreign
Key
airlineID Identifier of the airline. Primary key
price Price of the airline for
particular day/date.
-
date Date for which price is
recorded
-
Number of records 5 minimum and maximum can be 1000+
T_TODAY_PRICES This table contains all the information related to price of an airline
for current today date.
Attribute Description Primary Key / Foreign
Key
airlineID Identifier of the airline. Primary key and foreign
key
price Price of the airline for
current day/date.
-
date Current date for which
price is recorded
Primary key
availableSeats Number of seats -

available for current date
Number of records 5 minimum and maximum can be 1000+
T_SEAT This table contains all the information related to seat of a particular
airline.
Attribute Description Primary Key / Foreign
Key
seatID Unique identifier given
to every seat.
Primary key
airlineID Identifier of the airline. Foreign key
type Type of the seat i.e.
economy or business
class
-
Number of records 5 minimum and maximum can be 1000+
T_FACILITY This table contains all information pertaining to a particular facility.
Attribute Description Primary Key / Foreign
Key
facilityID Unique identifier given
to every facility.
Primary key
description Details regarding a
facility.
-
Number of records 5 minimum and maximum can be 1000+
T_CUSTOMER This table contains all the information related to a customer.
Attribute Description Primary Key / Foreign
Key
ssn Social Security Number
of the customer
Primary key
firstName First name of the
customer
-
lastName Last name of the
customer
-
phoneNo Phone number of the -

customer
email Email of the customer -
address Full address of the
customer
-
state State to which customer
belongs
-
Country Country to which
customer belongs
-
Number of records 5 minimum and maximum can be 1000+
T_RESERVATION This table contains all the information related to a reservation.
Attribute Description Primary Key / Foreign
Key
reservationID Unique identifier given
to every reservation.
Primary key
startDate Date on which journey
begins
-
endDate Date on which journey
ends
-
airlineID Airline ID for which
reservation has been
made
Foreign Key
customer User who had made the
reservation
Foreign Key
Number of records 5 minimum and maximum can be 1000+
T_AIRLINE_FACILIT
Y
Association table between AIRLINE and FACILITY tables
Attribute Description Primary Key / Foreign
Key
airlineID Identifier of the airline. Primary key and Foreign
key
facilityID Identifier of the facility. Primary key and Foreign

key
Number of records 5 minimum and maximum can be 1000+
Set of reports
GROUP BY
Query 1: Number of customers from each state.

Query 2: Number of flights of each location
JOIN
Query 3 – Select first name and last name of customer who have made reservations.
Query 4 – Names of airlines having set of facilities

Regular
Query 5 – select * from t_facility
Query 6 –
select count(*) from t_customer
Sub-query
Query 7 – Customers who have not booked reservation

Query 8 – List of seats of flights which have reservation