DEPARTURE Departure Date Flight_Number PILOT Good luck! Oct-31-2018 Oct-31-2018 Oct-31-2018 EMPLOYEE Employee_no Name 10

[answerhappygod]

Departure Departure Date Flight Number Pilot Good Luck Oct 31 2018 Oct 31 2018 Oct 31 2018 Employee Employee No Name 10 1 (458.37 KiB) Viewed 35 times

DEPARTURE Departure Date Flight_Number PILOT Good luck! Oct-31-2018 Oct-31-2018 Oct-31-2018 EMPLOYEE Employee_no Name 1001 1002 Oct-31-2018 Oct-31-2018 Oct-31-2018 Oct-31-2018 Nov-1-2018 Nov-1-2018 1002 1002 Nov-1-2018 Nov-1-2018 Nov-1-2018 Nov-1-2018 1002 1002 1003 Employee_no 1001 1001 1003 FLIGHT 1001 John Jones 1002 Albert Peters 1003 Carl Rowe 1004 Phillips 1005 William Gates 1006 Robert Clark 1007 Joseph Warnock 1008 Lisa Stuart Name John Jones John Jones John Jones Albert Peters Albert Peters Albert Peters Albert Peters Albert Peters Carl Rowe Carl Rowe Flight_num origin dest 100 SLC BOS 206 DFW STL 334 ORD ΜΙΑ ORD ΜΙΑ ORD SLC 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 much data duplication as possible. 335 ΜΙΑ 336 ORD 337 ΜΙΑ 121 STL 122 STL JFK 991 BOS 394 DFW 395 ΜΙΑ 449 CDC 930 YYV 931 DCA 932 DCA 112 DCA Last Name Parker Jones Peters YYV YYV Brown Williams Phillips Madison Parker Justin Colano Claudia Stevens Hooper Arthur Sergio Ryan Julia Brandon BOOKED_ON Customer Name ORD ΜΙΑ DFW DEN DCA YYV YYV DEN Maverick Gordon Rose Parker Bryan Brown Arthur Hooper Sergio Ryan Brandon Gordon Madison Parker Mark Williams Carol Phillips Justin Colano Claudia Stevens Julia Maverick John Peters 100 112 206 334 335 337 449 100 112 206 334 395 991 Aircraft_serial_ B1110 NULL D2530 NULL NULL D2216 NULL D2530 A8055 NULL NULL NULL B1325 Salary 90000 85000 115000 25000 50000 45000 50000 75000 License Date UNE-1-1999 AUG-14-2000 APRIL-20-2005 SEP-25-2010 APRIL-20-2005 JULY-17-2003 MAY-28-2012 DEC-01-2015 JAN-25-2013 JUNE-05-2015 dep_time arr_time 17:50 11:40 14:14 17:14 8:00 9:00 12:00 15:00 Plane model can fly 18:00 20:14 20:30 23:53 9:13 10:19 18:53 18:22 7:00 8:30 16:00 17:00 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 Street 123 Thomas St. 234 Pines St. 345 Star St. 435 Palm St. 348 Andrew St. 395 Pine St. 285 Diamond St. 223 Easy St. 775 Main St. 456 Rose St. 567 Spruce St. 678 Tulip St. 789 First St. ASSIGNED TO Employee no 1001 1003 1004 Departure Date Oct-31-2018 Oct-31-2018 Oct-31-2018 Oct-31-2018 Nov-1-2018 Nov-1-2018 Nov-1-2018 Nov-1-2018 Oct-31-2018 Oct-31-2018 Nov-1-2018 Nov-1-2018 B727 Use this information to insert data in the airline database: CUSTOMER Name Rose Jessica John Bryan Mark Carol B747 DC10 DC9 A310 B757 A320 B727 A310 DC9 1002 1007 1003 1004 Oct-31-2018 Oct-31-2018 1005 Oct-31-2018 1006 Oct-31-2018 1001 Nov-1-2018 1005 Nov-1-2018 1007 Nov-1-2018 1003 Nov-1-2018 1006 Nov-1-2018 Nov-1-2018 1007 1002 1004 Nov-1-2018 1008 Lincoln Raleigh Miami dep_date Oct-31-2018 Oct-31-2018 Oct-31-2018 Oct-31-2018 Oct-31-2018 AIRCRAFT Serial_no Model_no B1419 B2244 B1110 B727 B1325 B727 B1088 B747 B747 B757 B757 DC9 89377 D2101 Nov-1-2018 Nov-1-2018 D2216 DC9 D2333 DC9 D2429 D2530 DC10 A7099 A310 A8055 A320 City Toledo Los Angeles Raleigh Miami Fort Lauderdale Omaha San Diego Tampa Saint Louis Las Vegas Flight_Num State OH CA NC FL FL NE CA FL MO NV NE NC FL PLANE TYPE | Model No flight num 100 100 B727 206 B747 334 B757 449 DC9 991 DC10 991 A310 100 A320 100 A330 119 A340 449 991 100 100 100 206 206 337 337 337 337 100 100 100 991 991 991 112 112 112 Zip Code 57556 34898 79999 30533 33318 88899 99977 66798 99878 17878 87898 79999 30533 MD MD phone 801-556-2239 801-552-2943 801-393-2230 Airbus Airbus Airbus Airbus 801-933-2320 801-343-2320 801-323-2320 801-493-2203 801-193-2320 801 303 2222 313-912-2101 801-228-6729 Manufacturer Boeing Boeing Boeing NULL NULL 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. 1. 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. 2. Create the airline database. a. Declare your relations using the SQL DDL. b. Include your constraints in the DDL: primary keys, foreign keys, unique values, Default, null, not null, etc. 3. Include any necessary column that is missing from the original ER Diagram, for example customer's ID or customer's number. 4. Populate the airline database a. Populate the database using SQL DML. b. Use the provided tables as the data to insert in the database. 5. Answer the following queries a few queries using PostgreSQL (psql or pgAdmin). a. List the departure date, flight number and the number of booked customers. b. List the pilot names that can fly a A310 plane type. c. List the departure date and flight number that never got any personnel assigned. 6. Create 5 more queries that will be useful for retrieving data from this database. Specify and get a screenshot of the results. 7. Implement your database using PostgreSQL. Create a single sql for the creation and population of the airline database. For example: create_airline_db.sql queries 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<query_number>.sql)-zip them all) using the Canvas system. You can submit all the files from this assignment in one single zip file with the name of the file using this format: "xyzassignment2" (xyz are your initials) using the canvas system. Embed any graph in the document.