Microsoft Access assignment

User Generated

nob63

Programming

Description

Hi

You can find the instructions attached. Here is a video that will explain all the steps: https://www.youtube.com/watch?v=NHJWhA3dB_Y

Also, please write the Author as this name: ABDULAZIZ

Unformatted Attachment Preview

HOMEWORK INSTRUCTIONS Homework #4 Airfare Prices Problem Background Information Since the implementation of the Airline Deregulation Act of 1978, American airlines have been free to set their own fares and routes. The application of market forces to the airline industry has had a number of substantial impacts, first in shaping the hub-and-spoke airport system and allowing the growth of the legacy carriers, and now in the increase of lowcost carriers. Problem Statement Over the past decade, there has been a dramatic realignment in the air transportation industry brought on by economic and demographic changes. Many of the legacy carriers have reorganized while a number of low-cost carriers have expanded. In this assignment, students will analyze government statistics to look at the costs of flying and to see how various types of carriers compare. Instructions IMPORTANT: This assignment requires the Windows version of Microsoft Office. IMPORTANT: Complete the steps below in the order they are given. Completing the steps out of order may complicate the assignment or result in an incorrect result. 1. Download and extract the provided Data Files ZIP file. It contains the following files for use in this assignment: a. originairports.xml – Information on major origin airports in the United States [1], [2]. Table: OriginAirports Field Name OriginAirportCode Type Short Text City Population Hub Short Text Number Yes/No Introduction to Computer Applications West Virginia University Page 1 of 7 Description Primary key. Airport designator code (e.g., PIT for Pittsburgh International Airport). Location with city and state. 2014 population of the city served by airport. If yes, airport is a hub. Version 25.5 Modified 10/26/2017 HOMEWORK INSTRUCTIONS Homework #4 Airfare Prices Problem b. destinationairports.xml – Information on major destination airports in the United States [1], [2]. Table: DestinationAirports Field Name Type DestinationAirportCode Short Text City Population Hub c. d. Short Text Number Yes/No airfare.xml – Data including fare (ticket price) information for major route pairs in the United States as well as information on market share. Figures are from the 1st Quarter 2015 [3]. Table: Airfare Field Name OriginAirportCode Type Short Text DestinationAirportCode Short Text Distance Passengers Number Number Fare Currency LargestAirlineCode LargestAirlineShare Short Text Percentage LargestAirlineFare CheapestAirlineCode CheapestAirlineShare Currency Short Text Percentage CheapestAirlineFare Currency Description Part of composite key. Airport designator code of origin airport. Part of composite key. Airport designator code of destination airport. Distance (in miles) between airport pairs. Number of passengers flying this route annually. Average fare (ticket price) of all airlines on route. Code of airline with largest market share. Percentage of market share held by largest airline. Average fare for largest airline. Code of airline with cheapest fare. Percentage of market share held by cheapest airline. Average fare for cheapest airline. largestairlines.xml – Information on airlines that had the largest market share for their routes. Table: LargestAirlines Field Name Type LargestAirlineCode Short Text AirlineName AirlineType 2. Description Primary key. Airport designator code (e.g., PIT for Pittsburgh International Airport). Location with city and state. 2014 population of the city served by airport. If yes, airport is a hub. Short Text Short Text Description Primary key. Code of airline with largest market share (e.g., "DL" for Delta Air Lines). Name of the airline. Type of the airline (Legacy, Low-Cost, or Other). Create a new Microsoft Access database named lastname_firstname_hw4_app.accdb . Introduction to Computer Applications West Virginia University Page 2 of 7 Version 25.5 Modified 10/26/2017 HOMEWORK INSTRUCTIONS Homework #4 Airfare Prices Problem 3. 4. Import the following items into the database: a. originairports.xml file – Import structure and data into a new table. b. destinationairports.xml file – Import structure and data into a new table. c. airfare.xml file – Import structure and data into a new table. d. largestairlines.xml file – Import structure and data into a new table. We need to create a table to store data on the airlines. a. Create a table named CheapestAirlines with the fields below. Use appropriate field types and designate a good primary key. Table: CheapestAirlines Field Name CheapestAirlineCode Description 2-character code of airline with cheapest average fare (e.g., "DL" for Delta Air Lines). Full name of airline. Type of the airline (Legacy, Low-Cost, or Other). AirlineName AirlineType b. Enter records for all cheapest airlines below. HINT: The CheapestAirlines table will contain 12 records. CheapestAirlineCode AA AS B6 DL F9 G4 NK SY UA US VX WN 5. AirlineName American Airlines Alaska Airlines JetBlue Airlines Delta Air Lines Frontier Airlines Allegiant Air Spirit Airlines Sun Country Airlines United Air Lines US Airways Virgin America Southwest Airlines AirlineType Legacy Other Low-Cost Legacy Low-Cost Low-Cost Low-Cost Low-Cost Legacy Legacy Low-Cost Low-Cost Create appropriate relationships for the following tables. Do not enforce referential integrity. a. OriginAirports and Airfare b. DestinationAirports and Airfare c. LargestAirlines and Airfare d. CheapestAirlines and Airfare Introduction to Computer Applications West Virginia University Page 3 of 7 Version 25.5 Modified 10/26/2017 HOMEWORK INSTRUCTIONS Homework #4 Airfare Prices Problem 6. Create separate queries to provide the information requested below. Name each query after the step in which it appears (e.g., name the query in Step 6a as Query6A). HINT: Run your queries to test them. Make sure that they display all and only the records that you would expect to appear. a. Create a query displaying information on all flight routes. List the origin city name, the destination city name, largest carrier name, and cheapest carrier name. Sort by origin city name in ascending order. HINT: This query will show 5,253 records and 4 fields. b. We wish to compare the types of airlines flying each route. Create a query listing the origin city name; destination city name; the name, type, and share of the largest airline; and the name, type, and share of the cheapest airline. Only display records where the cheapest airline type is low-cost carrier. Sort by origin city name and then by destination city name, both in ascending order. HINT: This query will show 1,638 records and 8 fields. c. Create a query to view information on hub airports. List the origin city name, if the origin airport is a hub, destination city name, if the destination airport is a hub, passengers, and fare. Only display records where at least one of the airports (origin and/or destination) is a hub. Sort by fare in descending order. HINT: This query will show 3,279 records and 6 fields. Introduction to Computer Applications West Virginia University Page 4 of 7 Version 25.5 Modified 10/26/2017 HOMEWORK INSTRUCTIONS Homework #4 Airfare Prices Problem d. We wish to calculate the revenue miles per route. Create a query listing the origin city name, destination city name, distance, and passengers. Also, include a field to calculate the revenue miles. You can calculate the revenue miles using the formula: [𝐴𝑖𝑟𝑓𝑎𝑟𝑒. 𝐷𝑖𝑠𝑡𝑎𝑛𝑐𝑒] ∗ [𝐴𝑖𝑟𝑓𝑎𝑟𝑒. 𝑃𝑎𝑠𝑠𝑒𝑛𝑔𝑒𝑟𝑠] Format the calculated field as a standard-type number. HINT: This query will show 5,253 records and 5 fields. e. Create a query to calculate the cost-per-mile for flight routes. List the origin city name, destination city name, flight distance, passengers, and fare. Also, include a field to calculate the cost-per-mile. You can calculate the cost-per-mile using the formula: [𝐴𝑖𝑟𝑓𝑎𝑟𝑒. 𝐹𝑎𝑟𝑒] [𝐴𝑖𝑟𝑓𝑎𝑟𝑒. 𝐷𝑖𝑠𝑡𝑎𝑛𝑐𝑒] Only display routes that are either less than 250 miles (< 250) or more than 1,500 miles (> 1500) long. Format the calculated field as currency with 3 decimal places. HINT: This query will show 1,301 records and 6 fields. f. We wish to determine the average distance for flights departing from each airport. Create a query listing, for each origin city name, its airport code, if it is a hub, and the average distance of its departing flights. Format the average distance as a standard-type number with no decimal places. HINT: This query will show 230 records and 4 fields. Introduction to Computer Applications West Virginia University Page 5 of 7 Version 25.5 Modified 10/26/2017 HOMEWORK INSTRUCTIONS Homework #4 Airfare Prices Problem g. We want to calculate statistics about the departing passengers per resident for each origin airport. Copy-and-paste this SQL code into a new query: SELECT OriginAirports.City, OriginAirports.OriginAirportCode, OriginAirports.Hub, OriginAirports.Population, Sum(Airfare.Passengers) AS AnnualPassengers, Round([AnnualPassengers]/[Population],2) AS PassengersPerResident FROM OriginAirports INNER JOIN Airfare ON OriginAirports.OriginAirportCode = Airfare.OriginAirportCode GROUP BY OriginAirports.City, OriginAirports.OriginAirportCode, OriginAirports.Hub, OriginAirports.Population ORDER BY OriginAirports.City; IMPORTANT: Do not make any modifications to this query other than entering the above SQL code. HINT: This query will show 230 records and 6 fields. 7. We need to create a new table to store analysis questions responses. a. Create a table named AnalysisQuestions with fields below. Use appropriate field types and designate a good primary key. Table: AnalysisQuestions Field Name QuestionNumber Response 8. Description Question being answered. Response to the analysis question prompt. In the AnalysisQuestions table, answer four of the five analysis questions below. Respond to one question per record. a. Flights between cities less than 200 miles apart generally tended to have a fairly low number of passengers. Name at least two reasons why this might be the case. b. While people are normally price-conscious, there are many cases where the carrier with the largest market share on a route is not the cheapest carrier. Why might this be the case? c. How does the cost per mile on long flights (over 1,500 miles) compare to the cost on short flights (under 250 miles)? What is a possible explanation for any differences in the cost? d. Is there any relationship between the ticket price and the number of people flying a given route? Why might this be so? Introduction to Computer Applications West Virginia University Page 6 of 7 Version 25.5 Modified 10/26/2017 HOMEWORK INSTRUCTIONS Homework #4 Airfare Prices Problem e. 9. The median departing passengers-per-resident for all airports is 1.31. What are some reasons why some airports have extremely high passengers-per-resident ratios and others have extremely low ratios? Run the Compact and Repair Database utility on your database. Ignore any errors you receive when running the utility. Grading Rubric This assignment is worth 50 points. It will be graded by your instructor using this rubric, with partial credit awarded as appropriate: Step 3 Steps 4a-b Step 5 3 points 4 points total 3 points Steps 6a-g Step 7a Steps 8a-e (pick 4 of 5) 4 points each 2 points 2.5 points each The analysis questions in Steps 8a-e will be evaluated using this rubric: Standard Answer is reasonable. Answer is supported. Meets Requirements (1.25 points) Answer addresses the question prompt and is factually correct or a reasonable interpretation of available data. Logical rationale is provided to support the given answer. Does Not Meet Requirements (0 points) Answer does not address the question prompt, is factually incorrect, or is an unreasonable interpretation of available data. Logical rationale is not provided to support the given answer. References [1] “QuickFacts,” U.S. Census Bureau. Available: http://quickfacts.census.gov/qfd/index.html. [2] “Enplanements at All Commercial Service Airports (Rank Order) CY14,” Federal Aviation Administration, Sep. 2015. Available: http://www.faa.gov/airports/planning_capacity/passenger_allcargo_stats/passe nger/media/cy14-commercial-service-enplanements.pdf. [3] “Domestic Airline Consumer Airfare Report,” U.S. Department of Transportation. Available: https://www.transportation.gov/policy/aviation-policy/domesticairline-consumer-airfare-report. Introduction to Computer Applications West Virginia University Page 7 of 7 Version 25.5 Modified 10/26/2017
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

At...


Anonymous
I was struggling with this subject, and this helped me a ton!

Studypool
4.7
Indeed
4.5
Sitejabber
4.4

Related Tags