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.

PLEASEPROVIDE ME BELOW ANSWERS IN GIVEN POINTS

1 Data modeling design

In this step, you'll model the data requirements of your case study system. You deliverable for this step will be the

entity types and relationship types that describe your case study system. You must include an ER diagram (using the

UML format) drawn using the Dia drawing application as part of your deliverable for this step. If you have

constraints that cannot be expressed in the ER diagram, you may have an accompanying text file for your ER diagram.

In the past, some students have found it useful to have multiple ER diagrams if their model has complex relationships

that can be hard to draw in one big diagram. One strategy is to have a separate ER diagram for each entity that shows

the details for that entity (name, attributes, keys, etc) and to have one or more ER diagrams showing the relationships,

with only the entity names and keys.

2 Relational Database Schema Development

In this step, you'll produce a set of relational database schema from your ER diagram. Your schema must identify the

the tables required with the table schema: the table attributes along with their domains, primary key constraints,

alternate key constraints, foreign key constraints, and any general column or table constraints. Be sure also to specify

whether attributes are nullable. Remember that any many-to-many relationships in your ER diagram must become

tables in your relational

In this step, you will also ensure that your tables are in 3NF.

Your deliverable for this step is an English language description of the database schema. You should include the

functional dependencies that you identified for your tables as evidence that your tables in 3NF. You are not to

implement the tables using SQL for this deliverable.

The following are suggested formats for this deliverable:

• table_schema_name ( col1 : type1, ..., coln: typen)

◦ you need to identify the primary and foreign key constraints

◦ you need to identify any other constraints (alternate keys, not null, constraints that will require

check clauses in SQL etc)

•

table_schema_name

col1

type1 ... coln

typen

◦ you need to identify the primary and foreign key constraints

◦ you need to identify any other constraints (alternate keys, not null, constraints that will require

check clauses in SQL etc)

3 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.

4 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.

5 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.

The second submission will be your table creation script for your database.

The third submission will be your table population script for your database.

The fourth submission will be your operational testing script for your database.

Answer 1

Below is the script for creating the airline database. Description of all the tables is given alongwith.

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

CREATE TABLE Employees

(

               Ssn                                       VARCHAR(10),

               Name                                  VARCHAR(15),

               Street                                  VARCHAR(20),

               City                                      VARCHAR(10),

               State                                    VARCHAR(10),

               Postalcode                         VARCHAR(5),

               Phone                                  VARCHAR(10),

               Salary                                  CURRENCY,

               Dob                                      DATE,

               Is_pilot_attendant           CHAR(1) CHECK IN (‘Pilot, ’Attendant’),

               Rank                                    VARCHAR(5) ,

               Flying_hours                      VARCHAR(5),

               Home_airport                   VARCHAR(15),

               Phone2                               VARCHAR(10),

               PRIMARY KEY (ssn),

               FOREIGN KEY (home_airport) REFERENCES

               FOREIGN KEY (rank) REFERENCES Employee_rank (rank_id)

);

/* PilotAircraft keeps information of pilot and expertise of aircraft */

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)

);

/* Aircraft table keeps the information of all the aircrafts*/

CREATE TABLE Aircraft

(

               Id                                          VARCHAR(10);

               Name                                  VARCHAR(15);

               Type                                    VARCHAR(10),

               Capacity                             INTEGER,

               mfgBY                                 VARCHAR(15),

               mfgOn                                 DATE,

               PRIMARY KEY (id)

);

/* Employee_rank keeps the relation of employee and rank */

CREATE TABLE Employee_rank

(

               Rank_id                               VARCHAR(5),

               Rank_name                        VARCHAR(15),

               PRIMARY KEY (rank_id)

);

/* Airport table contains the detail of all the airport*/

CREATE TABLE Airport

(

               Id                                          VARCHAR(3),

               City                                      VARCHAR(10) NOT NULL,

               State                                    VARCHAR(10) NOT NULL,

               noOfGate                           INTEGER NOT NULL,

               PRIMARY KEY (id)

);

/* AirlineRoute keeps the information of all the routes */

CREATE TABLE AirlineRoute

(

               Id                                          VARCHAR(5),

               routeCode                          VARCHAR(10),

               Origin                                  VARCHAR(3),

               Destination                        VARCHAR(3),                    

               PRIMARY KEY (id)

               FOREIGN KEY (origin) REFERENCES Airport (Id),

               FOREIGN KEY (destination) REFERENCES Airport (Id)

);

/* Contains information about all scheduled flights between airports*/

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)

);

/* FlightPrice keeps information about the prices of the specific route*/

CREATE TABLE FlightPrice

(

               Id                                          VARCHAR(10),

               Route                                  VARCHAR(10),

               Class                                    VARCHAR(5),

               Base_fare                           CURRENCY,

               Surcharge                           CURRENCY,

               PRIMARY KEY (id),

               FOREIGN KEY (route) REFERENCES AirlineRoute (routeCode),

               FOREIGN KEY (class) REFERENCES flightClass (id)

);

/* flightClass keeps information about the available classes in flight */

CREATE TABLE flightClass

(

               Id                                          VARCHAR(5),

               Class_name                       VARCHAR(10),

               Class_price                         CURRENCY,

               Price_date                          DATE,

               PRIMARY KEY (id)

);

/* Passenger table keeps the information about the passenger who is booking flight */

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)

);

/* flightBooking keeps the status of booking of each individual flight*/

CREATE TABLE flightBooking

(

               Id                                          VARCHAR(5),

               Passenger                           VARCHAR(id),

               Route                                  VARCHAR(5),

               Price                                    CURRENCY,

               bookingDate                      DATE,

               scheduled                           VARCHAR(5),

               PRIMARY KEY (id),

               FOREIGN KEY (passenger) REFERENCES Passenger (id),

               FOREIGN KEY (route) REFERENCES AirlineRoute (routeCode),

               FOREIGN KEY (scheduled) REFERENCES ScheduledFlight (id)

);

Below are the insert statements for inserting values to created tables.

INSERT INTO Employees VALUES (‘emp-101’, ‘BOB’, ‘Park’, ‘Dallas’, ‘ND’, ‘54321’, ‘9999999999’, ‘6000’, ‘1992-03-12’, ‘Pilot’, ‘R-1’, ‘23’, ‘AIR-1’, ‘2222222222’);

INSERT INTO VALUES (‘emp-101’, ‘AC-11’);

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

INSERT INTO Airport VALUES (‘AIR-12’, ‘Dallas’, ‘ND’, ‘4’);

INSERT INTO AirlineRoute VALUES (‘AR-101’ , ‘DA-FAR-1’, ‘Dallas’, ‘Fargo’);

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

INSERT INTO FlightPrice Values (‘FP-1’, ‘DA-FAR-1’, ‘class-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’);