ConAir (CA) commercial airlines company requires you experience in database design and have approached you to assist in the design of their new airline database system. They urgently need to have their database set up to keep track of a multitude of information such as for their commercial aircrafts, flights and reservations etc. Based on CA Company ERD diagram on the next page, you have been tasked with creating a database that will capture the basic structure given in the ERD. CA Company also require some reports for strategic decision-making and improving efficiency of daily operations. To achieve this you are required to: ? Use DDL to create the table structures and populate them.

? Use DML to create queries that will facilitate extraction and presentation of required information.

Part A ? Creating Table Structures and Inserting Data : You will create the two SQL batch/script files described below with Notepad++. We will test these files by running them in the MySql shell. Comments/remarks may be used to explain what queries are written using --. 1. Create a Notepad++ text file called SXXXXXXXX_A2_CreateStructures.sql. The Xs represent your student ID. In this file, you will write all your SQL queries to only generate the table structures as given in CA Company ERD above. This task is part of DDL. Note the following:

a. When you run your script, all the tables should be created with their constraints as given in the ERD.

b. For the date fields, do not include the time with the date. All times in a day in the database need to be stored in the 24-hour format: 'HH:MM:SS' using the Time data type. Here, HH represents the hours, MM the minutes and SS the seconds. For instance, '23:33:06' would mean 11:33PM with 6 seconds elapsed.

c. Add comments to indicate which SQL statements correspond to which tables using -- .

d. Attribute/entity names should be exactly the same as in the ERD. You can also add any additional useful attributes to the given tables and would not necessarily need to remove any existing ones. Consider any such changes that you introduce carefully.

e. You may add any additional entities that you feel are necessary in the database. However, you should not remove the required relationships and entities from the given CA Company ERD on page 2. Consider any such changes that you introduce carefully.

f. The basic structure in the given ERD must be captured in your implementation in Part a. Any significant changes, such as additions of extra attributes or entities, or any others in (d) and (e) above, must be clearly and very briefly explained with comments in the script where the relevant SQL statement is.

g. Avoid commands to ignore any particular constraint checks when creating your tables.

h. You need the SQL statements to create the tables. You should be able to create your constraints within the statements to create the tables as well. Any other types of statements may not be needed.

i. Test out your script in the MySQL shell. When it runs, it is expected to create all the tables with all their required constraints as given in the ERD without any errors. Avoid syntax errors.

c. Avoid any code that ignores any particular constraint checks when inserting data. d. Test out your script to see if it works without errors (syntax or other). Part B ? Queries: To create the queries that will facilitate extraction of information for reports follow the instructions given below and create a text file called SXXXXXXXX_A2_Queries.txt. Write in this file, all the queries below based strictly on the given ERD. a. Add comments to indicate the query you are writing, eg. Query 1, Query 2, etc. If the query is ambiguous, you may not get any credit for that query. b. Make sure your queries have no syntax errors. Avoid any gratuitous elements in your queries where possible. Test them out. c. Each query is to generate results based on the requirements given below: I. Query 1: Write a query to display all the details of each booking class without listing each attribute in the query. The results should be sorted in descending order of the flight cost factor. II. Query 2: Write a query to display each passenger?s full name (eg. John A. Thomas) aliased as Full Name, phone number (using the format example: 1-700-4325896, where 1 is the country code, 700 is the area code and the rest of the digits make up the rest of the phone number), including the email of all passengers who have actually purchased less than 15 tickets at AC. Indicate the total number of tickets purchased as well, aliased as Total Tickets, and sort the results in ascending order of it. Alias the phone number as Phone. Passengers who have not purchased any tickets at all are not to be part of the query result. III. Query 3: List the total number of ?Economy Class? flights associated with the booking. Aliased as TotalEconomyFlights. IV. Query 4: List the full name (eg. Razzul A. Henry) aliased as Name and country of all the pilots that have flown the aircrafts made by the following manufacturing companies: Malo Aircraft, Ralm and RST. Sort the results in the ascending order of the full names of the pilots. V. Query 5: List all the aircrafts that are not allowed to land at the airport with the code, BAX. Just list the aircraft serial number, model number and number of seats. Note that each airport allows at least one aircraft type to land in it and that the database would already have records to reflect this. Sort the results with an explicit ascending order clause on the number of seats. 2. Create another text file in Notepad called SXXXXXXXX_A2_InsertData.sql. The Xs represent your student ID. In this file, you will write all your SQL statements to populate the tables created in the first step with data. This script will only be executed after the first script above. Note the following: a. The data to be inserted in each table is entirely up to you, but should be realistic and pertinent to the scenario. Add only 10 records per table. b. Also add comments/remarks to indicate which statements correspond to which tables using --.

i. Query 6: List the total number of aircrafts each passenger has travelled in. List the passenger?s title, full name and country as well. The total number of aircrafts and full name should be aliased as TotalAircrafts and Full Name, respectively. Sort the results in ascending order of the passenger?s full name. ii. Query 7: For each flight, list its flight number, departing airport (eg. Nadi International Airport) and the airport?s country (eg. Fiji). List also the flight?s departure date in the format: 08/04/2018, which represents 8th April 2018. Order the results in descending order of the departing airport?s country name. VI. Query 8: Write a query to display for each booking, the booking ID, the corresponding flight number, the corresponding passenger?s full name aliased as Full Name, the corresponding flight?s departure date and time combined using the format example: Jan 9, 2017 - 9.24 PM. Also include the flight?s combined arrival date and arrival time in the same format as the departure details. For these combined time details, if either the date or time is null, show a null value for the combined result. Alias the combined time details as DepartureTime and ArrivalTime for the departure and arrival time details, respectively. Finally, include the flight duration (aliased as Duration) in the format: 1 day(s), 4 hr(s) and 4 min(s). For this query, assume that some flights may be longer in duration due to stoppages/transits etc. Hence, the duration tracking by days is included as well. The duration is simply the difference between the ArrivalTime and DepartureTime described earlier, and each day will be considered to be 24 hours. The duration should be nullified if any of the values being combined in the duration are null. All results are to be sorted in the ascending order of the passenger?s full name. VII. Query 9: Write a query to update the pilot salary to double its current figure for all pilots whose current salary is less than or the same as $20,000. The same update should also happen to all pilots that are based in the state of Arizona with a zip code of 85003 in USA (use this abbreviation and not its full form in the query). Only one query is required for this question. VIII. Query 10: Write a query to remove all bookings that have not yet been paid for.

Airport PK Ap Code Ap Name Ap City Ap State Ap_Country allows AircraftType Airport PK,FK1 AT ID PK,FK2 Ap Code lands Aircraft Type PK AT ID AT Name consits -H-- FK1 AT ID FK2 AM ID assigned receivos- deports- Booking Class PK BC ID BC_FliCostFactor BC Description Aircraft PK Ac SerialNum Ac_ModelNum Ac_NumSeats Ac_ManufactureDate --manufactures--H-PK AM ID AM_Name Aircraft Manufacturer Flight PK El Num F Fare FB_NumAvailSeats F DeptDate F DepTime F AmDate F ArTime FK1 Ac_SerialNum FK2 PI CAA LicenseNum FK3 Ap Code Booking PK Bk_ID Bk SeatNum Bk Date Bk Time Bk Credit Card Bk TotalCost Bk PaymentStatus FK1 BC ID FK2 Pas ID FK3 Fli Num ----H- --H- Pilot PK Pi CAA LicenseNum Pi FirstName Pi Initial Pi LastName PI DOB Pi StreetAddress Pi Suburb Pi City Pi StateOrProvince Pi PostalCodeOrZip Pi Country Pi RegularPhone Pi EmergencyPhone Pi Email Pi Salary Passenger PK Pas ID Pas Tile Pas FirstName Pas LastName Pas_DOB Pas_StreetAddress Pas Suburb Pas City Pas StateOrProvince Pas PostalCodeOrZip Pas Country Pas Email Pas PhCountryCode Pas PhAreaCode Pas_PhNum