Project 3 Details:
This project is based on eight tables (AIRPORT, FLIGHT, DEPARTURES, PASSENGER, RESERVATION, EQUIP_TYPE, PILOTS, and TICKET) that contain data about the Belle Airlines. Download and use data in Project 3 zip file for this project.
Some Background on Belle Airlines
Belle Airlines is a regional carrier that operates primarily in the southwestern United States. At the present time, Belle Airlines operates its own reservation information system. To simplify our analysis, we will assume that all reservations on Belle Airlines flights are placed through Belle Airlines employees.
Flights are not booked through travel agents and Belle Airlines does not participate in industry-wide reservations services. Each flight is assigned a unique flight number and has its own set of flight characteristics (ie. flight number, origin, destination, departure time, arrival time, meal code, base fare, mileage between origin and destination, and number of changes in time zone between the origin and destination of the flight). Departures of each flight are stored in the Departures table. Each departure contains four attributes (flight number, departure date, pilot id, and equipment number).
Belle Airlines flies out of airports located all over the country. Data on these airports is stored in the Airport table. Data on these airports includes: a three-character airport code, location of the airport, elevation, phone number, hub airlines that operates out of the airport. Since Belle Airlines flies out of airports located all over the country, Belle Airlines pilots live all over the country.
Data on these pilots is stored in the Pilots table, which contains the following attributes: pilot id, pilot name, social security number, street address, city, stat, zip code, flight pay, date of birth, and date hired. The company also owns its own fleet of airplanes. Data on these airplanes is stored in the Equip_Type table which contains the following attributes: equipment number, equipment type, seating capacity, fuel capacity, and miles per gallon.
Three additional tables populate the Belle Airlines database: the Passenger table (with attributes: passenger name, itinerary number, and confirmation number), the Reservation table (with attributes: confirmation number, reservation date, reservation name, reservation phone, reservation flight number, and reservation flight date), and the Ticket table (with attributes: itinerary number, flight number, flight date, and seat assignment).
--------------------------------
Instructions:
Read "Project #3 Details" above.
Utilize "Google Draw" to:
Create Presentation Layer ERD
Create a fine granular Extended ERD
Convert the EERD into your logical schema
Zip all files and submit via Project #3 ERD Dropbox (graded)
---------------------------
Utilize the materials and resources in Module 3 to:
Identify functional dependencies and derive candidate keys, and
Follow the normalization process to determine tables and relationships
---------------------------
Run the following SQL Queries in the database you have created for project #3 and submit reports:
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.
Display the code, location, and elevation of all airports without a hub airline. Your results should be in descending order by elevation.
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.
Display the flight numbers and the codes for the origins and destinations of all flight reservations made by Andy Anderson.
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.
Display the names of all pilots who live outside of the state of Texas. Order the results in alphabetical order by last name.
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.
Display all flights that originate at an airport without a hub airline.
Display all flights that arrive at an airport without a hub airline.
Display all flights that both originate and arrive at an airport without a hub airline.
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.
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.
Display the total number of flights that originate from each point of origin.
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.
Revise the previous query to also include the display of those locations where no flights originate.
Display the average flight pay for pilots that live in each state.
Display the name and flight pay for those pilots whose flight pay exceeds the average flight pay for all pilots.
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.
Display the date of the most recent departure flown by each pilot. Include in what you display the name of the pilot.
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.
Display the number of departures that involve flights for each of the three time zone differences.
Display the number of airports located in each state.
Display the number of departures where the distance flown is greater than or equal to 1000 miles.
Display the difference in age between the oldest and youngest pilot.
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.
For each passenger listed in the PASSENGER table, display the name of the person responsible for his or her reservation.
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.
For each reservation in the RESERVATION table, display the name of the pilot who will be piloting the flight.
Display those tickets that include only one flight.
Display the name of the passengers whose tickets include only one flight.