Description

• Project #3 ERD Dropbox (graded)

  Read "Project #3 Details..." and utilize "Google Draw" to:

  1. Create Presentation Layer ERD
  2. Create a fine granular Extended ERD
  3. Convert the EERD into your logical schema

  
  

• Project #3 DB Normalization, Tables and Relationships (Required - Graded)

  Utilize the materials and resources in Module 3 to:

  1. Identify functional dependencies and derive candidate keys, and
  2. Follow the normalization process to determine tables and relationships

• Project #3 - SQL (Required - Graded)

  Run the following SQL Queries in the database you have created for project #3 and submit reports:

  1. Display the origin, destination, departure time from origin, and arrival time at destination for all flights that occur in the same time zone. Your results should be displayed in order by flight number.
  2. Display the code, location, and elevation of all airports without a hub airline. Your results should be in descending order by elevation.
  3. Display the departures originating from Los Angeles, CA. Include in your results flights from Los Angeles for which no departures currently exist. Los Angeles, CA and not LAX should be used in the WHERE clause of your query.
  4. Display the flight numbers and the codes for the origins and destinations of all flight reservations made by Andy Anderson.
  5. Display the seating capacity, fuel capacity, and miles per gallon for all aircraft manufactured by Boeing. Information about each equipment type should be displayed only once.
  6. Display the names of all pilots who live outside of the state of Texas. Order the results in alphabetical order by last name.
  7. Display the flight number, flight date, fare, origin, and destination for all tickets with a flight date of July 2006. Use the fare in the FLIGHT table as the fare for the ticket. Order your results in ascending order by flight date and within flight date by flight number.
  8. Display all flights that originate at an airport without a hub airline.
  9. Display all flights that arrive at an airport without a hub airline.
  10. Display all flights that both originate and arrive at an airport without a hub airline.
  11. Display all departures that are flown by an aircraft not manufactured by Boeing. Your results should be in ascending order by departure date and within departure date by flight number.
  12. Display the distance divided by the fare for each flight. For each flight, display the flight number, the origin, the destination, the fare, and the quotient. Your results should be in descending order by the quotient and rounded to two places to the right of the decimal point. Create a descriptive column alias for the quotient.
  13. Display the total number of flights that originate from each point of origin.
  14. Revise the previous query so that instead of displaying the code for each point the location of each point of origin from the AIRPORT table is displayed.
  15. Revise the previous query to also include the display of those locations where no flights originate.
  16. Display the average flight pay for pilots that live in each state.
  17. Display the name and flight pay for those pilots whose flight pay exceeds the average flight pay for all pilots.
  18. Display the name and flight pay for those pilots whose flight pay exceeds the average flight pay for all pilots in the state in which they reside.
  19. Display the date of the most recent departure flown by each pilot. Include in what you display the name of the pilot.
  20. Display not only the date of the most recent departure by each pilot but also the number of days since the last departure date. Truncate the number of days (i.e., if 37.67655, display 37) to zero places to the right of the decimal point. Order the result in descending order by the number of days.
  21. Display the number of departures that involve flights for each of the three time zone differences.
  22. Display the number of airports located in each state.
  23. Display the number of departures where the distance flown is greater than or equal to 1000 miles.
  24. Display the difference in age between the oldest and youngest pilot.
  25. For each type of aircraft, display the total distance that can be flown before refueling. Display your results in descending order by total distance that can be flown.
  26. For each passenger listed in the PASSENGER table, display the name of the person responsible for his or her reservation.
  27. For each passenger listed in the PASSENGER table, display the name of the person responsible for his or her reservation only if the passenger himself or herself was not responsible for making the reservation.
  28. For each reservation in the RESERVATION table, display the name of the pilot who will be piloting the flight.
  29. Display those tickets that include only one flight.
  30. Display the name of the passengers whose tickets include only one flight.