Airport database is created to store the information about airport locations and airplanes. The information of flights, passengers, their bookings and baggage information has been stored in this database. Moreover, payment details has been stored in this database. The airport database has been created to generate reports based on the stored information. Such reports can be used for effective decision making.
We have created database using MYSQL. Different views have been created. We have structured SQL commands using Data Definition language (DDL) and Data manipulation language (DML).
Data Definition language (DDL):
Create Database:
CREATE DATABASE airport_database
Create Tables:
CREATE TABLE AIRPORT
(
airport_id int AUTO_INCREMENT,
airport_name varchar (100),
aiport_location varchar (255),
PRIMARY KEY (airport_id)
);
CREATE TABLE checkin
(
checkin_id int AUTO_INCREMENT,
Seat_number int,
row_number int,
empty_seats int,
PRIMARY KEY (checkin_id)
);
CREATE TABLE baggage
(
Baggage_id INT,
Baggage_Type VARCHAR (15),
Baggage_weight INT,
Baggage_number INT,
PRIMARY KEY (Baggage_id)
);
CREATE TABLE AIRPLANE
(
Airplane_id INT AUTO_INCREMENT,
Producer VARCHAR (20),
Type INT,
PRIMARY KEY (Airplane_id)
);
CREATE TABLE FLIGHT
(
Flight_number INT,
Arrival_Time Time,
Arrival_Date Date,
Departure_Time Time,
Departure_Date Date,
Destination VARCHAR (45),
Airplane_id INT,
Departure_City VARCHAR (45),
Arrival_City VARCHAR (45),
PRIMARY KEY (Flight_number),
FOREIGN KEY (Airplane_id) REFERENCES Airplane(Airplane_id)
);
CREATE TABLE Passenger
(
Passenger_id INT AUTO_INCREMENT,
First_name VARCHAR (45),
Last_name VARCHAR (45),
State VARCHAR (45),
City VARCHAR (45),
Street VARCHAR (45),
Age INT,
phone_number INT ,
Email VARCHAR (45),
PRIMARY KEY (Passenger_id)
);
CREATE TABLE booking(
Booking_id INT AUTO_INCREMENT,
Passanger_id INT,
Flight_number INT,
Seat_number INT,
Baggage VARCHAR (45),
Class VARCHAR (15),
PRIMARY KEY (Booking_id),
FOREIGN KEY(Passanger_id) REFERENCES passenger(Passenger_id),
FOREIGN KEY(Flight_number) REFERENCES flight(Flight_number)
);
CREATE TABLE Payment
(
Payment_id INT AUTO_INCREMENT,
Booking_id INT,
Payment_amount DATE,
Payment_date TIMESTAMP,
PRIMARY KEY(Payment_id),
FOREIGN KEY(Booking_id) REFERENCES booking(booking_id)
);
CREATE TABLE Flight_class
(
class_id INT AUTO_INCREMENT,
class_type VARCHAR (15),
flight_id int,
PRIMARY key (class_id),
FOREIGN KEY (flight_id) REFERENCES flight(Flight_number)
);
Data Manipulation Languages (DML):
Populate Database:
Table Airplane:
INSERT INTO `airplane` (`Airplane_id`, `Producer`, `Type`) VALUES (‘1’, ‘Airbus’, ’14’), (‘2’, ‘Boeing’, ‘5’);
INSERT INTO `airplane` (`Airplane_id`, `Producer`, `Type`) VALUES (‘3’, ‘Bombardian’, ’10’), (‘4’, ‘Embraer’, ‘9’), (‘5’, ‘Tupolav’, ‘6’);
Airport:
INSERT INTO `airport` (`airport_id`, `airport_name`, `aiport_location`) VALUES (‘1’, ‘Hartsfield–Jackson Atlanta International Airport’, ‘Atlanta’), (‘2’, ‘Los Angeles International Airport’, ”), (‘3’, ‘O\’Hare International Airport’, ‘Chicago’), (‘4’, ‘Dallas/Fort Worth International Airport’, ‘Dallas/Fort Worth’), (‘5’, ‘John F. Kennedy International Airport ‘, ‘New York’), (‘6’, ‘Denver International Airport’, ‘Denver’), (‘7’, ‘San Francisco International Airport’, ‘San Francisco’), (‘8’, ‘McCarran International Airport’, ‘Las Vegas’), (‘9’, ‘Charlotte Douglas International Airport ‘, ‘Charlotte’), (’10’, ‘Seattle–Tacoma International Airport’, ‘Seattle/Tacoma’);
Baggage:
INSERT INTO `baggage` (`Baggage_id`, `Baggage_Type`, `Baggage_weight`, `Baggage_number`) VALUES (‘1’, ‘Carry-on’, ’35’, ‘1’), (‘2’, ‘Carry-on’, ’25’, ‘2’), (‘9’, ‘checked-in’, ’30’, ’16’), (’10’, ‘checked’, ’50’, ‘2’), (‘3’, ‘checked’, ’40’, ‘3’), (‘4’, ‘carry-on’, ’22’, ‘5’), (‘5’, ‘carry-on’, ’23’, ’10’), (‘6’, ‘carry-on’, ’24’, ‘8’), (‘7’, ‘checked-in’, ’42’, ’20’), (‘8’, ‘checked-in’, ’41’, ’12’);
Passenger:
INSERT INTO `passenger` (`Passenger_id`, `First_name`, `Last_name`, `State`, `City`, `Street`, `Age`, `phone_number`, `Email`) VALUES (‘1’, ‘john’, ‘hanz’, ‘Alaska’, ‘Anchorage’, ‘abcd’, ’20’, ‘23145638’, ‘[email protected]’), (‘2’, ‘peter’, ‘dave’, ‘Arizona ‘, ‘Phoenix’, ‘efg’, ’23’, ‘36852149’, ‘[email protected]’), (‘3’, ‘daren’, ‘James’, ‘California’, ‘Los Angeles ‘, ‘street1′, ’24’, ‘32658974’, ‘[email protected]’), (‘4’, ‘sammy’, ‘benz’, ‘Colorado ‘, ‘Denver’, ‘street2′, ’32’, ‘32145236’, ‘[email protected]’), (‘5’, ‘steve’, ‘george’, ‘Florida’, ‘Miami’, ‘street4′, ’36’, ‘36524789’, ‘[email protected]’), (‘6’, ‘james’, ‘bind’, ‘Georgia’, ‘Atlanta’, ‘street5′, ’32’, ‘56985214’, ‘[email protected]’), (‘7’, ‘george’, ‘Michael’, ‘Illinois’, ‘Springfield’, ‘street1′, ’35’, ‘36541278’, ‘[email protected]’), (‘8’, ‘william’, ‘jobs’, ‘District of Columbia’, ‘washington’, ‘street2′, ’45’, ‘36985214’, ‘[email protected]’), (‘9’, ‘johnson’, ‘will’, ‘Iowa’, ‘Davenport’, ‘street7′, ’65’, ‘65478236’, ‘[email protected]’), (’10’, ‘john’, ‘steve’, ‘florida’, ‘Orlando’, ‘street10′, ’36’, ‘65498256’, ‘[email protected]’);
Flight:
INSERT INTO `flight` (`Flight_number`, `Arrival_Time`, `Arrival_Date`, `Departure_Time`, `Departure_Date`, `Destination`, `Airplane_id`, `Departure_City`, `Arrival_City`) VALUES (‘1′, ’02:00:00’, ‘2018-04-03′, ’05:00:00’, ‘2018-04-03’, ‘Orlando’, ‘1’, ‘Atlanta’, ‘Chicago’), (‘2′, ’04:00:00’, ‘2018-04-05′, ’11:00:00’, ‘2018-04-04’, ‘Miami’, ‘2’, ‘Los angeles’, ‘Orlando’), (‘3′, ’02:23:56’, ‘2018-04-03′, ’03:00:00’, ‘2018-04-03’, ‘indianapolis’, ‘2’, ‘chicago’, ‘springfield’), (‘4′, ’01:00:00’, ‘2018-04-09′, ’05:00:00’, ‘2018-04-09’, ‘Miami’, ‘3’, ‘Dover’, ‘Washington’), (‘5′, ’03:30:00’, ‘2018-04-12′, ’04:00:00’, ‘2018-04-12’, ‘Hazard’, ‘3’, ‘sprinfield’, ‘indianapolis’), (‘6′, ’12:00:00’, ‘2018-04-13′, ’01:00:00’, ‘2018-04-14’, ‘Boston’, ‘4’, ‘freeport’, ‘baltimon’), (‘7′, ’06:00:00’, ‘2018-04-19′, ’07:00:00’, ‘2018-04-20’, ‘Pensacola’, ‘1’, ‘Beverly hills’, ‘denver’), (‘8′, ’08:00:00’, ‘2018-04-18′, ’09:00:00’, ‘2018-04-18’, ‘St. Louis’, ‘2’, ‘Dulux’, ‘bilothi’), (‘9′, ’10:00:00’, ‘2018-04-23′, ’11:00:00’, ‘2018-04-23’, ‘New Orleans’, ‘5’, ‘miami’, ‘ORLANDO’), (’10’, ’09:00:00′, ‘2018-04-26′, ’12:00:00’, ‘2018-04-28’, ‘Logan’, ‘5’, ‘Autin’, ‘Camden’);
Flight Class:
INSERT INTO `flight_class` (`class_id`, `class_type`, `flight_id`) VALUES (‘1’, ‘first class’, ‘1’), (‘2’, ‘business class’, ‘1’), (‘3’, ‘economy class’, ‘1’), (‘4’, ‘First class’, ‘2’), (‘5’, ‘business class’, ‘2’), (‘6’, ‘economy class’, ‘2’), (‘7’, ‘first class’, ‘3’), (‘8’, ‘business class’, ‘3’), (‘9’, ‘economy class’, ‘3’), (’10’, ‘economy class’, ‘4’);
Checkin:
INSERT INTO `checkin` (`checkin_id`, `Seat_number`, `row_number`, `empty_seats`) VALUES (‘1’, ‘1’, ‘1’, ‘5’), (‘2’, ‘4’, ‘2’, ‘4’), (‘3’, ‘3’, ‘3’, ‘6’), (‘4’, ‘4’, ‘1’, ‘5’), (‘5′, ’12’, ‘2’, ‘6’), (‘6′, ’13’, ‘1’, ‘7’), (‘7′, ’15’, ‘2’, ‘5’), (‘8′, ’16’, ‘2’, ‘6’), (‘9′, ’17’, ‘3’, ‘3’), (’10’, ’19’, ‘2’, ‘4’);
Booking:
INSERT INTO `booking` (`Booking_id`, `Passanger_id`, `Flight_number`, `Seat_number`, `Baggage`, `Class`) VALUES (‘1’, ‘1’, ‘1’, ’12’, ‘carry-on’, ‘First class’), (‘2’, ‘2’, ‘1’, ’13’, ‘checked-in’, ‘economy class’), (‘3’, ‘3’, ‘2’, ’10’, ‘carry-on’, ‘first class’), (‘4’, ‘4’, ‘2’, ‘5’, ‘carry-on’, ‘business class’), (‘5’, ‘5’, ‘3’, ‘9’, ‘carry-on’, ‘business class’), (‘6’, ‘6’, ‘3’, ‘6’, ‘carry-on’, ‘business class’), (‘7’, ‘7’, ‘4’, ‘5’, ‘carry-on’, ‘business class’), (‘8’, ‘8’, ‘4’, ‘6’, ‘carry-on’, ‘business class’), (‘9’, ‘9’, ‘5’, ‘9’, ‘carry-on’, ‘economy class’), (’10’, ’10’, ‘7’, ‘2’, ‘carry-on’, ‘business class’);
Payment:
ALTER TABLE payment
ALTER COlUMN Payment_amount INT
INSERT INTO `payment` (`Payment_id`, `Booking_id`, `Payment_date`, `Payment_amount`) VALUES (‘1’, ‘2’, ‘2018-04-03 03:00:00’, ‘100’), (‘2’, ‘1’, ‘2018-04-03 05:08:16’, ‘150’), (‘3’, ‘3’, ‘2018-04-04 10:00:00’, ‘100’), (‘4’, ‘4’, ‘2018-04-05 03:00:00’, ‘120’), (‘5’, ‘5’, ‘2018-04-08 06:00:00’, ‘130’), (‘6’, ‘6’, ‘2018-04-10 11:00:00’, ‘100’), (‘7’, ‘7’, ‘2018-04-07 12:00:00’, ‘130’), (‘8’, ‘8’, ‘2018-04-20 04:00:00’, ‘100’), (‘9’, ‘9’, ‘2018-04-24 05:00:00’, ‘110’), (’10’, ’10’, ‘2018-04-14 00:00:00’, ‘120’);
Test Cases:
Test Case # 1:
Retrieve airplanes and airport information. The information contains the airplane name and type and its airport name and location.
Expected Result:
The information will be retrieved containing the airplane name and type and its airport name and location.
Actual Results:
As Expected.
Test Pass/Fail:
Pass.
Test Case # 2:
Retrieve airports information located in new York and Chicago.
Expected Result:
The information will be retrieved containing the information details of airports situated in New York and Chicago.
Actual Result:
As Expected.
Test Pass/Fail:
Pass.
Test Case # 3:
Retrieve information related to passengers and their flight bookings.
Expected Result:
The information will be generated containing the data about passengers and their flight bookings.
Actual Result:
Test Pass/Fail:
Pass.
Test case #4:
The information retrieved containing the airbus flights details.
Expected Result:
The information will be fetched containing the details about the airbus flights.
Actual Result:
As expected.
Test Pass/Fail:
Pass.
Test Case # 5:
The information retrieved containing passenger, booking and baggage details.
Expected Result:
The information will be fetched containing the details about the passenger, booking and baggage detail.
Actual Result:
As expected.
Test Pass/Fail:
Pass.
Conclusion:
We have created the airport database using MYSQL. We have implemented Queries using DDL and DML. Multiple queries has been created and executed. The results has been analyzed. The actual results are according to the expected results and hence we have created and linked tables by following the relationship rules. We have tried to normalize the database structure to avoid complex and unexpected results of SQL queries. We have constructed efficient queries to generated multiple views so that we can manipulate those views in future according to the changing requirements.
References
DML, DDL, DCL and TCL Statements in SQL with Examples. (2018). Retrieved from tech-recipes website: http://www.tech-recipes.com/rx/55356/dml-ddl-dcl-and-tcl-statements-in-sql-with-examples/
SQL FOREIGN KEY Constraint. (2018). Retrieved from w3schools website: https://www.w3schools.com/sql/sql_foreignkey.asp
