SQL代写 MGT 560G Database Design and SQL

程序代写 CS代考 / SQL代写代考

MGT 560G: Database Design and SQL Fall 2018 Mini A Assignment #3: SQL Part 1

INSTRUCTIONS

  1. This is an individual assignment. You may not discuss your approach to solving these questions with anyone, other than the instructor or TA.
  2. Please include your Student ID on the submission.
  3. The only material you are allowed is:
    1. Class notes
    2. Content posted on Canvas
    3. Textbook
  4. You are not permitted to use other online resources
  5. Due in class, on Wednesday, October 3
  6. There will be TA office hours. See the schedule on Canvas.

ASSIGNMENT

Minneapolis – St. Paul (MSP) is the largest airport in Minnesota, serving the city of Minneapolis and surrounding cities. There are also quite a few connecting flights through MSP. The airport has always prided itself in the large number of airlines that use the airport and in its efficiency in quickly preparing planes for departure.

The Airport dataset includes information about flights arriving at MSP and those departing MSP. The dataset also includes information about 2 terminals in the airport and services available at each gate in the airport.

Specifically, the dataset includes gate information, detailing the time each gate opens and available services. Not all gates have services adjacent to them. Tables for arriving and departing flights include airline and flight number, and the airport where the flight departed from (or is destined to reach), scheduled times, and updated times. Not all flights have updated times. The Gate Assignment table provides the gate assigned to each flight and turnaround time to prepare the flight at that gate.

Important Note a fair amount of the data includes times, such as arrival time. In SQL you need to convert time to numbers to facilitate calculations. Use the TIME_TO_SEC function to enable calculations of time variables.

The TIME_TO_SEC function converts time variables to numeric values (seconds) that are usable in calculations. Use this function on the turnaround time and other times, when needed.

The database is posted on Canvas
The E/R diagram for this database is below:

MGT 560G – Fall 2018 – Assignment #3: SQL Part 1

-1-

For each question, submit your SQL code and a screen-shot of the results. If the results are too long, partial results are fine. Include relevant attributes in each query results, not all attributes.

Each of these questions is 10 points

  1. 1)  For each gate, find the gate number, terminal name, and services offered at that gate
  2. 2)  Calculate the number of flights scheduled for each gate, only for gates that have flights

    scheduled

  3. 3)  The TIME_TO_SEC function converts time variables to numeric values (seconds) that are

    usable in calculations. Use this function on the turnaround time for each flight. Display the

    turnaround time both in its original format and when using the TIME_TO_SEC function.

  4. 4)  List the number of flights for each gate, including gates without flights. Which gates don’t

    have flights assigned to them?

  5. 5)  Find the terminal, airline, and services offered to flights with a destination of ORD
  6. 6)  Calculate the number of flights for each arrival airport
  7. 7)  List the number of scheduled arriving flights by airline and time

Each of these questions is 5 points

  1. 8)  Identify a couple of trends (2) you can see in the results of question 7
  2. 9)  For each Terminal, and each Gate, calculate the number of flights and total turnaround time

    for the gate.

    •   IMPORTANT NOTES:
    •   MySQL doesn’t add time values correctly. You have to use the

      TIME_TO_SEC(turnaroundtime) in order to convert time values to numeric values.

      You can then take the add up these values to get reasonable answers.

    •   By the way, SUM(TIME_TO_SEC()) ≠ TIME_TO_SEC(SUM())

-2-

MGT 560G – Fall 2018 – Assignment #3: SQL Part 1

  1. 10)  Which airlines has the shortest average delay of arrivals? Which has the longest average delay of arrivals?

    Notes: remember to use the Time_To_Sec() function here. Also, do not include flights that do not have an updated departure time in this analysis.

  2. 11)  Find all pairs of gates with the same service
  3. 12)  Find all pairs of departing airports for arriving United Airlines flights
  4. 13)  How much time did you spend on this homework?

-3-

MGT 560G – Fall 2018 – Assignment #3: SQL Part 1

Related Posts