1. Consider the AIRLINE relational database schema and some sample data given in APPENDIX A. The schema describes a database for airline flight information. Each flight is identified by a Flight_number, and consists of one or more FLIGHT_LEGs with Leg_numbers 1, 2, 3, Each FLIGHT_LEG has scheduled arrival and departure times, airports, and one or more LEG_INSTANCEs one for each Date on which the flight travels. FAREs are kept for each FLIGHT. For each FLIGHT_LEG instance, SEAT_RESERVATIONs are kept, as are the AIRPLANE used on the leg and the actual arrival and departure times and airports. An AIRPLANE is identified by an Airplane_id and is of a particular AIRPLANE_TYPE. CAN_LAND relates AIRPLANE_TYPEs to the AIRPORTs at which they can land. An AIRPORT is identified by an Airport_code.

a) Develop the database schema diagram that shows the tables, columns, primary keys and the referential integrity constraints (foreign keys) that must hold on the AIRPORT database. Your diagram must be a neat readable diagram (preferably not hand-drawn, or use straight readable line if drawn by hand). Use Hays principle of schema layout.

b) If the AIRLINE database needs to enter a new reservation (of your choice) for some trip on a specific date, describe the operations that would need to be performed for the reservation to be created. Assume that any required information in other relations has been entered, flights cannot be overbooked, and the new reservation has 2 leg instances. You may make up the data to show the operations that need to be performed to create the reservation. The operations do not need to be expressed in SQL.

c) For each of the operations identified above, identify if the constraints that will be checked during the operation are key, entity integrity, referential integrity, or general constraints specific to the AIRLINE database.

d) Thinking about the operation of the AIRLINE database, describe one other example of a general constraint (i.e., neither implicit nor schema based) that could be imposed on the database. You are not required to provide the SQL statement to implement this constraint in the database.

e) Write the DDL queries to create the AIRLINE database, the tables in it, and all key and referential integrity constraints. You must have appropriate data types for each attribute of the relations. Except for the query to create the database (which is not compulsory to include in your solution), all your queries must be executable in a single click. Your DDL commands must also use at least one of the following types of constraint where appropriate: i. Check constraint ii. Domain constraint i.e., use a defined/created domain If you make any assumptions regarding a data type or domain of an attribute, you must state the assumption in the comment before the creation of each relation.

f) Write the DDL queries to modify the necessary table(s) so that if a flight number is removed or updated, all associated flight leg and flight leg instance are also be removed or updated, respectively.

Part B

Using PostgreSQL, create the Company database from our textbook and insert the data. The scripts needed are provided as part of this assignment.

2. Using the created database, and without using subqueries write queries to:

a) List the ssn, last name and first name of all employees sorted by last name and then first name, both in reverse order. Employee first names must be displayed first.

b) List the name, number, and location of all projects with the sequence of characters tion in its name.

c) List the ssn and names of all employees who are working on the project named ProductX, in order by last name and then first name.

d) List the ssn, names and sex of all employees who have the same names and sex with any of their dependents.

e) List the ssn, last, birth date and salary of the oldest employee among those who have the least salaries in the administration department.

f) List the names of all employees who have a daughter that was born in the 1980s.

g) List the names of all employees and the names of their supervisors (if they have one). The column with the supervisor names should be appropriately labeled.

h) List the ssn, name, and birth date of the oldest employee who is not a supervisor.

i) Determine the average salary of employees who are not managers.

j) List the ssn, first name, and the total number of hours worked for employees who have worked more than 25 hours but not more than 40 hours in total. Columns must be displayed in the order specified and values must be sorted in the order specified.

3. Using the same database, write the queries to do the following:

a. List the names of projects that have more than 2 employees working on them. List in order of the number of people working on projects and then in alphabetical order of the project name.

b. List the SSN and last name of employees who earn above $10000 and have a daughter and a son.

c. For each employee with dependents, list their last name and the name of their youngest dependent. Display the results in ascending order of the dependent names.

d. Retrieve the first name of each employee who has a son. List the employees in sequence by last name. Do this in 3 different ways:

i. Use the IN operator

ii. Use the EXISTS operator

iii. Use a single-block query (i.e., do not use nested query).

e. Create a view named Project_hoursthat lists the name of each project and the total hours worked on each project. Use the view created to retrieve a list of employees (fname, lname, ssn) who are working on the project with the least hours

.

APPENDIX A AIRPORT \begin{tabular}{|l|l|l|l|} \hline Airport code & Name & City & State \\ \hline \end{tabular} FLIGHT \begin{tabular}{|l|l|l|} \hline Flight_number & Airline & Weekdays \\ \hline \end{tabular} FLIGHT_LEG \begin{tabular}{|l|c|c|c|} \hline Flight_number & Leg_number & Departure_airport_code & Scheduled_departure_time \\ \hline Arrival_airport_code & Scheduled_arrival_time \\ \hline \end{tabular} LEG INSTANCE \begin{tabular}{|c|c|c|c|l|c|c|} \hline Flight_number & Leg number & Date & Number_of_available_seats & Airplane_id \\ \hline \hline \end{tabular} FARE \begin{tabular}{|l|l|l|l|} \hline Flight_number & Fare_code & Amount & Restrictions \\ \hline \end{tabular} AIRPLANE_TYPE \begin{tabular}{|l|l|l|} \hline Airplane_type_name & Max_seats & Company \\ \hline \end{tabular} CAN LAND \begin{tabular}{|l|l|} \hline Airplane_type_name & Airport_code \\ \hline \end{tabular} AIRPLANE \begin{tabular}{|l|l|l|} \hline Airplane id & Total_number_of_seats & Airplane_type \\ \hline \end{tabular} SEAT_RESERVATION \begin{tabular}{|l|l|l|l|l|l|} \hline Flight number & Leg_number & Date & Seat_number & Customer_name & Customer_phone \\ \hline \end{tabular} DDT EI IfLT FLIGHT_LEG LEG INSTANCE Aidni Ahie trac FAN I AMD i hic CEAT RFCFRUATIRAM