Assignment - Answer all questions -

Questions 1-6 make use of the following relational schema for a database storing water quality readings.

INSTRUMENT (name, description)

SITE (name, water_depth, description, latitude, longitude)

DEPLOYMENT(id, instrument, site, begin, end)

PROFILE(id,timestamp, deployment)

READING(id, timestamp, profile, depth, temperature, turbidity, dissolved_oxygen, power)

INSTRUMENT describes a type of instrument and SITE is a particular location. DEPLOYMENT captures the deployment of a particular instrument at a particular location; 'begin' and 'end' are the beginning and ending dates of the deployment. 'end' may be NULL for a current deployment.

Water quality profiles are taken several times a day. The timestamp for a profile is the time the profile was started. A profile consists of a number of readings taken at various depths. Each reading has a timestamp indicating when it was taken, the depth at which the reading was taken, and values for several water quality parameters (water temperature, turbidity, and oxygen saturation). Power indicates the battery charge.

All IDs are non-negative integers. The maximum depth is 200m; two decimal places are recorded. Latitude and longitude use the decimal degrees format e.g. 42.818833, -76.960167. Timestamps include both date and time, and must accommodate values since 1/1/2014. Power ranges from 0 to 15, with one decimal place recorded. Temperature ranges from 0 to 40, turbidity ranges from -5 to 100, and dissolved oxygen ranges from -5 to 20. All data values are recorded with two decimal places.

Key attributes are underlined. Foreign key constraints:

• DEPLOYMENT. instrument and DEPLOYMENT. site refer to INSTRUMENT .name and SITE .name, respectively
• PROFILE. deployment refers to DEPLOYMENT . id
• READING.profile refers to PROFILE.id

Additional constraints:

• A reading must be associated with a profile and must have a depth.
• A profile must be associated with a deployment.
• A deployment must have a site and instrument, but the date range is optional.
• There is at most one reading for a given depth in a particular profile.

Give SQL statement(s) to perform the following tasks. (You do not need to actually do the tasks, just write the statement(s) to accomplish them.) When asked for SQL statements to create something (table, view, stored routine, trigger), you can write the appropriate statement from scratch or you can use MySQL Workbench and copy the SQL statement(s) that it executes when you click "Apply". Use the database username exam2 for anything you want to execute/test in a database.

1. Define the five tables described above. Choose appropriate data types and column attributes, and include all appropriate constraints.

2. Populate the database using the files in /classes/cs343/exam2.

3. (a) Define a view containing the profile ID, reading ID, timestamp, depth, water temperature, turbidity, dissolved oxygen, power, site, and instrument for each reading. Readings recorded when the battery charge is too low are not trustworthy, so only readings where the power is at least 9.5 should be included.

(b) For each profile, report the date and time of the profile, the maximum water temperature in the profile, and the depth at which that temperature occurred. If the maximum water temperature occurred at multiple depths in a profile, include all of those readings. For full credit, make use of the view you defined.

4. (a) Delete all of the readings where the battery power is less than 9.5.

(b) Negative turbidity readings indicate a miscalibrated instrument. Set turbidity readings that are below 0 to 0.

(c) Add a new site named SenecaB with a water depth of 20m at coordinates 42.850987, -76.963802.

5. Define stored routines for each of the following. Choose a procedure or a function as appropriate.

(a) Given a profile ID, determine the number of readings associated with that profile.

(b) Given a profile ID, depth, and water temperature, update the temperature associated with that reading if such as reading exists, otherwise insert a new reading with the specified information.

(c) A thermocline is a thin layer of water where the temperature changes rapidly; it separates the warmer surface water from the colder deep water. Given a profile ID, find the biggest change in temperature between successive depth readings and report the depths above and below that interval.

For example, given the following set of readings, the largest change in temperature occurs between 23.98 and 25.61 meters, so those would be the depths reported. If there is more than one occurrence of the same largest change, reported the shallowest pair.

6. Define the following triggers. Treat each separately, that is, don't assume that the triggers from earlier parts are in place when you answer later ones.

(a) When the last reading in a profile is deleted, also delete the profile.

(b) Readings should not be taken at a depth that exceeds the water depth at the site. Signal an error (and do not allow the modification) if this occurs.

(c) If a reading is inserted without a timestamp, use the timestamp from the reading's profile.

Questions 7-14 make use of the following relational schema for a flight reservation database.

FLIGHT(fliohtnum, from, to, miles, deptime, arrtime)

FARE(farecode, price)

PRICING(flightnum, farecode, numseats)

RESERVATION(name, flightnum, date, farecode, confnum)

AIRPORT(code, city, state)

'state' is the two-letter state abbreviation e.g. NY. Departure and arrival times use the 24-hour clock. Key attributes are underlined. Foreign key constraints:

• FLIGHT. from and FLIGHT. to refer to AIRPORT. code
• PRICING . flightnum and RESERVATION. flightnum refer to FLIGHT. flightnum
• PRICING. farecode and RESERVATION. farecode refer to FARE. farecode

Write an SQL query for each of the following. You can use the database ex_flights if you want to test your queries.

7. Find the flights departing from an airport in NY state.

8. Find the names of passengers who have more than one reservation.

9. Find the flights which have at least one seat in every fare code.

10. Find the flights with no reservations.

11. Find the total number of seats on each flight.

12. Find the cheapest fare that has been booked on each flight, along with the fare code and the names of the people who have booked that fare.

13. For each flight and fare code, determine the number of seats available. (The number of seats available is the number of seats allocated for the flight and fare code minus the number that have been reserved.)

14. Find all of the airports that can be reached from ROC with exactly one stop. Make sure that the connection is legal - the second flight cannot depart before the first one arrives. For example, one such airport is LAX because there's a flight ROC→ORD which arrives at 6:59 and a flight ORD-) LAX which leaves at 8:32.

Bonus - Write an SQL statement/query for each of the following. (use the flight reservations database schema)

15. For each airport, find the number of passengers who have a reservation departing from that airport and the total ticket sales for those flying first class (fare codes starting with 'F', 'A', or 'P').

16. Find the busiest airport(s) - the one(s) with the largest number of departing and arriving flights. (Include all such airports if there's a tie.)

17. For each flight, find the top three fare codes in terms of the revenues brought in. The revenue brought in for a fare code is the number of reservations at that fare code times the price of that fare code.

You may use the textbook on reserve in the library (Elmasri and Navathe, 5th edition), your own course materials (your own assignments that have been handed back and notes made prior to the exam being handed out), and the materials posted directly on the course website.