PROBLEM 2: DESIGN A RELATIONAL DATABASE SCHEMA AND IMPLEMENT THE DATABASE. [50]
In this problem, you are required to create a relational database schema and to create a database using DDL and DML. Write all the SQL statements using PostgreSQL system.
Design the relational database schema for the ER Diagram of the airline database (problem 2 of assignment 1). You can use Figure 9.2 as an example of a relational database schema. Use the ER-to-Relational Mapping Algorithm for creating this schema.
Create the airline database.
Declare your relations using the SQL DDL.
Include your constraints in the DDL: primary keys, foreign keys, unique values, Default, null,
not null, etc.
Include any necessary column that is missing from the original ER Diagram, for example customer’s
ID or customer’s number.
Populate the airline database
Populate the database using SQL DML.
Use the provided tables as the data to insert in the database.
Answer
List the departure date, flight number and the number of booked customers.
List the pilot names that can fly a A310 plane type.
List the departure date and flight number that never got any personnel assigned.
Create 5 more queries that will be useful for retrieving data from this database. Specify the queries and get a screenshot of the results.
Implement your database using PostgreSQL.
Create a single sql for the creation and population of the airline database. For example: create_airline_db.sql
Submit a document that contains each query and a screenshot of the result of the queries that you create. Also send the sql files (one query per file (name each file Assig2_P2Q.sql)– zip them all) using the Canvas system.
You can submit all the files from this assignment in one single zip file
the following queries a few queries using PostgreSQL (psql or pgAdmin).
format: “xyzassignment2” (xyz are your initials) using the canvas system. Embed any graph in the
with the name of the file using this
document.
Use this information to insert data in the airline database:
CUSTOMER
Name
|
Last_Name
|
Street
|
City
|
State
|
Zip_Code
|
phone
|
Rose
|
Parker
|
123 Thomas St.
|
Toledo
|
OH
|
57556
|
801-556-2239
|
Jessica
|
Jones
|
234 Pines St.
|
Los Angeles
|
CA
|
34898
|
801-552-2943
|
John
|
Peters
|
345 Star St.
|
Raleigh
|
NC
|
79999
|
801-393-2230
|
Bryan
|
Brown
|
435 Palm St.
|
Miami
|
FL
|
30533
|
801-933-2320
|
Mark
|
Williams
|
348 Andrew St.
|
Fort Lauderdale
|
FL
|
33318
|
801-343-2320
|
Carol
|
Phillips
|
395 Pine St.
|
Omaha
|
NE
|
88899
|
801-323-2320
|
Madison
|
Parker
|
285 Diamond St.
|
San Diego
|
CA
|
99977
|
801-493-2203
|
Justin
|
Colano
|
223 Easy St.
|
Tampa
|
FL
|
66798
|
801-193-2320
|
Claudia
|
Stevens
|
775 Main St.
|
Saint Louis
|
MO
|
99878
|
801-303-2222
|
Arthur
|
Hooper
|
456 Rose St.
|
Las Vegas
|
NV
|
17878
|
313-912-2101
|
Sergio
|
Ryan
|
567 Spruce St.
|
Lincoln
|
NE
|
87898
|
801-228-6729
|
Julia
|
Maverick
|
678 Tulip St.
|
Raleigh
|
NC
|
79999
|
NULL
|
Brandon
|
Gordon
|
789 First St.
|
Miami
|
FL
|
30533
|
NULL
|
BOOKED_ON
Customer Name Rose Parker Bryan Brown Arthur Hooper Sergio Ryan Brandon Gordon Madison Parker Mark Williams Carol Phillips Justin Colano Claudia Stevens Julia Maverick John Peters
Departure_Date Flight_Num Oct-31-2018 100 Oct-31-2018 206 Oct-31-2018 334 Oct-31-2018 449
Nov-1-2018 991 Nov-1-2018 991 Nov-1-2018 100 Nov-1-2018 100
Oct-31-2018 449 Oct-31-2018 449 Nov-1-2018 991 Nov-1-2018 100
PLANE TYPE
Model_No Manufacturer B727 Boeing
B747 Boeing
B757 Boeing
DC9 MD DC10 MD A310 Airbus A320 Airbus A330 Airbus A340 Airbus
EMPLOYEE
AIRCRAFT
Serial_no Model_no B1110 B727 B1325 B727 B1088 B747 B1419 B747 B2244 B757 B9377 B757 D2101 DC9 D2216 DC9 D2333 DC9 D2429 DC9 D2530 DC10 A7099 A310 A8055 A320
Employee_no
|
Name
|
Salary
|
1001
|
John Jones
|
90000
|
1002
|
Albert Peters
|
85000
|
1003
|
Carl Rowe
|
115000
|
1004
|
Phillips
|
25000
|
1005
|
William Gates
|
50000
|
1006
|
Robert Clark
|
45000
|
1007
|
Joseph Warnock
|
50000
|
1008
|
Lisa Stuart
|
75000
|
PILOT
Employee_no
Name
License_Date
Plane_model_ can_fly
1001
John Jones
JUNE-1-1999
B727
1001
John Jones
AUG-14-2000
B747
1001
John Jones
APRIL-20-2005
DC10
1002
Albert Peters
SEP-25-2010
DC9
1002
Albert Peters
APRIL-20-2005
A310
1002
Albert Peters
JULY-17-2003
B757
1002
Albert Peters
MAY-28-2012
A320
1002
Albert Peters
DEC-01-2015
B727
1003
Carl Rowe
JAN-25-2013
A310
1003
Carl Rowe
JUNE-05-2015
DC9
FLIGHT
Flight_num origin dest 100 SLC BOS
206 DFW STL
334 ORD MIA
335 MIA ORD
336 ORD MIA
337 MIA ORD
121 STL SLC
122 STL YYV
330 JFK YYV 991 BOS ORD
394 DFW MIA
395 MIA DFW
449 CDG DEN
930 YYV DCA
931 DCA YYV
932 DCA YYV
112 DCA DEN
dep_time arr_time 8:00 17:50 9:00 11:40 12:00 14:14 15:00 17:14 18:00 20:14 20:30 23:53 7:00 9:13 8:30 10:19 16:00 18:53 17:00 18:22 19:00 21:30 21:00 23:43 10:00 19:29 13:00 16:10 17:00 18:10 18:00 19:10 14:00 18:07
DEPARTURE
ASSIGNED_TO
Departure_Date
|
Flight_Number
|
Aircraft_serial_no
|
Oct-31-2018
|
100
|
B1110
|
Oct-31-2018
|
112
|
NULL
|
Oct-31-2018
|
206
|
D2530
|
Oct-31-2018
|
334
|
NULL
|
Oct-31-2018
|
335
|
NULL
|
Oct-31-2018
|
337
|
D2216
|
Oct-31-2018
|
449
|
NULL
|
Nov-1-2018
|
100
|
D2530
|
Nov-1-2018
|
112
|
A8055
|
Nov-1-2018
|
206
|
NULL
|
Nov-1-2018
|
334
|
NULL
|
Nov-1-2018
|
395
|
NULL
|
Nov-1-2018
|
991
|
B1325
|
Employee_no
dep_date
flight_num
1001
Oct-31-2018
100
1003
Oct-31-2018
100
1004
Oct-31-2018
100
1002
Oct-31-2018
206
1007
Oct-31-2018
206
1003
Oct-31-2018
337
1004
Oct-31-2018
337
1005
Oct-31-2018
337
1006
Oct-31-2018
337
1001
Nov-1-2018
100
1005
Nov-1-2018
100
1007
Nov-1-2018
100
1003
Nov-1-2018
991
1006
Nov-1-2018
991
1007
Nov-1-2018
991
1002
Nov-1-2018
112
1004
Nov-1-2018
112
1008
Nov-1-2018
112
Notes:
Employee table contains all the personnel of the airline that can be assigned to a departure.
This data is not necessarily a database state. It is just data to insert in the database.
If you are planning to use auto increment columns for some tables columns like for example a
possible customer_id for the customer table, you can read a quick tutorial on how use serial to
create auto-increment columns here.
Try to eliminate as much data duplication as possible.