Question

Airline Company Case Study This document describes the data requirements for a fictional airline company, Anchor Air. In this case study, the company's key information requirements are identified. This information primarily deals with the assets the airline must use and manage to operate: airports, maintenance flight routes, and scheduled flights onto which customers book seats along with information about the passengers themselves. Employees The company needs to keep the following information regarding its employee. In the US, all employees have a unique social security number. Other information on employees that the airline might need include facts such as the employee's name; the employee's home street address and the employees city, statement and zipcode; the employee's hone phone number; the employee's salary; and the employee's brithdate. Additionally, some employees are pilot crew, while others are attendant crew. For pilots, the company need to keep the following information:the pilot's rank (probationary, junior, regular, senior); a list of the aircraft the pilot is rated to fly; should reference only aircraft the company owns; the number of flight hours the pilots has flown; the pilots home airport (this should be an airport that the airline is allowed to use); and an additional contact number for the pilot. For attendants, the company needs to keep the following information: the attendant's rank (probationary, crew, chief); the attendant's home airport (again it should be one the airline is allowed to use); and an additional contact number for the attendant Airports An airline is only allowed to fly to specific airports. This airline is a US company and is allowed to fly between US domestic airports only. The company needs to maintain information about the airports it is allowed to use. Airports are identified in the US with a three letter code (upper case)m which are unique. Other facts about airports include which city and state they are in, and how many gates are available for boarding and debarking the aircraft customers. The number of gates must be a positive integer. All of this information is required. Airline Flight Routes The airline has many aircraft flying every day to provide transport for its customers. By regulation, the airline is assigned certain routes between a origin airport and a destination airport. Each route (which is identified by a unique code), is schedule for the same time on the same day every week. While the origin and destination airports are the same every time the route is flown, the gates at the origin and destination airports may change from week to week. The origin and destination airports must each refer to one of the airports to which the airline flies. All of this information is required. Scheduled Flights The airline needs to keep track of the flight routes as they are schedule each day. The schedule simply needs to track which route is being flown (see the previous section on Airline Flight Routes) on which date and what are the departure and arrival gates at the origin and destiation airport, respectively. Flight Prices The seats available on each flight can have varying prices, depending on the class of the seat and these prices can vary from day to day. The database must track the ticket price for the following seat categories: first-class; business class; coach class; and economy class. The ticket price is for a specific seat category for a specific flight route id of the airline on a specific date. Ticket prices must be positive monetary amounts (two decimal places). All of this information is required. Passengers The airline is required to keep certain information about passengers who have booked flights with the airline. The database includes the last name, first name, middle initial, the street address, the city, the state, the zip code, and the phone numbers of the of the passenger and the passengers' email. The required information for the database is the first name, the last name, the street address, the city, the state, the zip code. The the other passenger information is optional. A passenger may give a number of phone numbers or none at all. Flight Bookings The airline must keep information that represents a passenger's booking for one of the airline's flights. The information must show which passenger is booked on a which route on which date and for what price. All of this information is required. Flight Crews The company has to assign crew members (pilots and flight attendants) for each date that one of its routes is flown. For each occurrence of a route being flown on a particular date, the company needs to know which pilots and flight attendants are assigned for that flight on that date. There can be different numbers of crew members assigned, but assume that all flights have cetween two and four pilot crew and four and eight attendant crew.

Relational Database Implementation

In this step, you will implement the table schema developed in step 2, using the Postgres SQL DDL language. Your

deliverable will be a Postgres SQL DDL script which when run in Postgres creates the tables for your case study.

Table Population

In this step, you'll create a data set for your database. Your deliverable will be a Postgres SQL DML script which

when run, inserts your data set into the database tables and also a text document with the data presented in a tabular

format.

Database Operational testing

In this final step, you'll create a Postgres SQL DML script which performs a set of queries on your populated database.

Each case study has a set of sample queries. Choose any ten of these queries and implement them. Your deliverable

will be a Postgres SQL DML script, which when run, performs the queries on your database as well as a text

document with the query results presented in a tabular form. Note, you can capture the query results from the result

window of pgAdminIII.

Answer 1

Part 1)

Below is the schema of the fictional airline company.

Aircraft table keeps the information of all the aircrafts

Employees table keeps information about all the employee (Pilot and attendant)

PilotAircraft keeps information of pilot and expertise of aircraft

Employee_rank keeps the relation of employee and rank

Airport table contains the detail of all the airport

AirlineRoute keeps the information of all the routes

ScheduledFlight Contains information about all scheduled flights between airports

Table FlightPrice keeps the information about the prices of the flight of a specific route

Table flightClass keeps information available classes in the flight

Table Passengers keep the information of all the passengers who booked the flight

Table flightBooking keeps information of the booking of the flight.

CREATE TABLE Aircraft

(

               id                                          VARCHAR(10),

               aircraftName                     VARCHAR(15),

               aircraftType                       VARCHAR(10),

               capacity                              INTEGER,

               mfgBY                                 VARCHAR(15),

               mfgOn                                 DATE,

               PRIMARY KEY (id)

);

CREATE TABLE Employee_rank

(

               rank_id                                VARCHAR(5),

               rank_name                         VARCHAR(15),

               PRIMARY KEY (rank_id)

);

CREATE TABLE Airport

(

               id                                          VARCHAR(3),

               city                                       VARCHAR(10) NOT NULL,

               state                                    VARCHAR(10) NOT NULL,

               noOfGate                           INTEGER NOT NULL,

               PRIMARY KEY (id)

);

CREATE TABLE flightClass

(

               id                                          VARCHAR(5),

               class_name                        VARCHAR(10),

               class_price                         INTEGER,

               price_date                          DATE,

               PRIMARY KEY (id)

);

CREATE TABLE Employees

(

               ssn                                       VARCHAR(10),

               empName                                         VARCHAR(15),

               street                                  VARCHAR(20),

               city                                       VARCHAR(10),

               state                                    VARCHAR(10),

               postalcode                         VARCHAR(5),

               phone                                  VARCHAR(10),

               salary                                  INTEGER,

               dob                                      DATE,

               is_pilot_attendant            VARCHAR(10),

               rank                                     VARCHAR(5) ,

               flying_hours                       VARCHAR(5),

               home_airport                    VARCHAR(15),

               phone2                               VARCHAR(10),

               PRIMARY KEY (ssn),

               FOREIGN KEY (home_airport) REFERENCES Airport (id),

               FOREIGN KEY (rank) REFERENCES Employee_rank (rank_id)

);

CREATE TABLE PilotAircraft

(

               ssn                                       VARCHAR(10),

               aircraft_id                          VARCHAR(10),

               PRIMARY KEY (ssn, aircraft_id),

               FOREIGN KEY (ssn) REFERENCES Employees (ssn),

               FOREIGN KEY (aircraft_id) REFERENCES Aircraft (id)

);

CREATE TABLE AirlineRoute

(

               id                                          VARCHAR(5),

               routeCode                          VARCHAR(10) UNIQUE NOT NULL,

               origin                                   VARCHAR(3),

               destination                         VARCHAR(3),                    

               PRIMARY KEY (id),

               FOREIGN KEY (origin) REFERENCES Airport (id),

               FOREIGN KEY (destination) REFERENCES Airport (id)

);

CREATE TABLE ScheduledFlight

(

               Id                                          VARCHAR(5),

               routeCode                          VARCHAR(10),

               departGate                        VARCHAR(3),

               arriveGate                          VARCHAR(3),

               scheduleDate                    DATE,

               PRIMARY KEY (id),

               FOREIGN KEY (routeCode) REFERENCES AirlineRoute (routeCode)

);

CREATE TABLE FlightPrice

(

               id                                          VARCHAR(10),

               route                                   VARCHAR(10),

               flightClass                          VARCHAR(5),

               baseFare                             INTEGER,

               surcharge                           INTEGER,

               PRIMARY KEY (id),

               FOREIGN KEY (route) REFERENCES AirlineRoute (routeCode),

               FOREIGN KEY (flightClass) REFERENCES flightClass (id)

);

CREATE TABLE Passengers

(

               id                                          VARCHAR(10),

               last_name                          VARCHAR(15),

               first_name                         VARCHAR(15) NOT NULL,

               middle_name                    VARCHAR(15),

               dob                                      DATE NOT NULL,

               street                                  VARCHAR(20),

               city                                       VARCHAR(10),

               state                                    VARCHAR(10),

               postal                                  VARCHAR(10),

               email                                   VARCHAR(30) NOT NULL,

               phone                                  VARCHAR(10) NOT NULL,

               noOfPassenger                 INTEGER,

               otherName                        VARCHAR(20),

               otherPhone                        VARCHAR(20),

               PRIMARY KEY (id)

);

CREATE TABLE flightBooking

(

               id                                          VARCHAR(5),

               passenger                           VARCHAR(10),

               route                                   VARCHAR(10),

               price                                    INTEGER,

               bookingDate                      DATE,

               scheduled                           VARCHAR(5),

               PRIMARY KEY (id),

               FOREIGN KEY (passenger) REFERENCES Passengers (id),

               FOREIGN KEY (route) REFERENCES AirlineRoute (routeCode),

               FOREIGN KEY (scheduled) REFERENCES ScheduledFlight (id)

);

Part 2)

Below queries insert the records in the created tables.

INSERT INTO Aircraft VALUES ('AC-11', 'Boeing-77', 'Airbus', '180', 'XXXXXX', '2001-03-12');

INSERT INTO Employee_rank VALUES ('ER-1', 'Probationary');

INSERT INTO Airport VALUES ('A12', 'Dallas', 'ND', '4');

INSERT INTO Airport VALUES ('A15', 'FARGO', 'ND', '3');

INSERT INTO flightClass VALUES ('CL-1', 'Business', 200, '2017-10-03');

INSERT INTO Employees VALUES ('emp-101', 'BOB', 'Park', 'Dallas', 'ND', '54321', '9999999999', '6000', '1992-03-12', 'Pilot', 'ER-1', '23', 'A12', '2222222222');

INSERT INTO PilotAircraft VALUES ('emp-101', 'AC-11');

INSERT INTO AirlineRoute VALUES ('AR-11' , 'DA-FAR-1', 'A12', 'A15');

INSERT INTO ScheduledFlight VALUES ('SF-1', 'DA-FAR-1', '2' , '4', '2017-10-18');

INSERT INTO FlightPrice Values ('FP-1', 'DA-FAR-1', 'CL-1', 300, 50);

INSERT INTO Passengers VALUES ('P-1', 'Smith', 'Bob', NULL, '1997-02-09', 'Park', 'Dallas', 'ND', '53243', '[email protected]' , '9999999999', 1, NULL, NULL);

INSERT INTO flightBooking VALUES ('FB-1', 'P-1', 'DA-FAR-1', 400, '2017-10-10', 'SF-1');

PART 3)

This part contains the query for operational testing of the database.

Below query find the employee details.

SELECT e.ssn, empName, street, e.city, e.state, e.postalcode, e.phone,

e.salary, e.dob, is_pilot_attendant, rank_name, flying_hours,

a.city, aircraftName

FROM Employees AS e INNER JOIN PilotAircraft AS pa

ON e.ssn = pa.ssn

INNER JOIN Airport AS a

ON a.id = e.home_airport

INNER JOIN Employee_rank AS er

ON e.rank = er.rank_id

INNER JOIN Aircraft AS at

ON at.id = pa.aircraft_id;

Output of above SQL statement-

Note- All the SQL statement (DDL and DML) can be run on PostgresSQL.