Technology Competency Assignment

Given that almost all corporate data are stored in relational databases, managers need to understand what data are available and how to use them. This is also pretty useful should you ever need to analyze data, where misunderstandings about the source of data can lead to crucially bad outcomes.

In this assignment, youll need to use Access and the Borrasca Airlines dataset provided via the course website. All tables and fields in the database are represented in the diagram below (note that there is actually only one Airport table in the database; two are shown because of the way Access handles multiple foreign keys referencing the same table).

Instructions

• Complete this assignment individually.
• For each of the queries listed, include in your response (a) a screenshot of the query design screen showing the query that was run (i.e., a screenshot of the Access GUI you used to select the records requested) and (b) a screenshot (or equivalent) of the results generated.
• Number your answers corresponding to the query number.
• Once completed, upload your assignment to the course website.

Queries

1. For each aircraft type included in the database, show the name (ACName) and maximum range. Order results from greatest range to least.
2. For each airport included in the database that is above 40 degrees north latitude, show the IATA Code, city name (CityShortName), and the airport turnaround time. Results should be ordered alphabetically (by CityShortName).
3. Which flights in June had 20 or fewer passengers on board? For each, show the date, flight number, origin code, destination code, and number of passengers. Order results by date, then by origin code alphabetically.
4. For each aircraft used by the company, show the tail number, the acquisition date, the name of the manufacturer, the type name (ACName), the number of coach seats (ACYSeats), and its cruising speed. Order results by tail number (lowest to highest).
5. Which ERJ 175 flights that departed on Tuesdays in June 2015 were full? For each full flight, list the date, flight number, IATA codes for origin and destination, and the number of passengers on board. Order results by date and then alphabetically by origin code. (The ERJ 175 is configured to hold 78 passengers.)
6. Create a departures board for OKC that shows all morning flights on June 26, 2015. The output should include, for each OKC departure, only the following: the flight number, the destination name (use CityLongName), the scheduled time of departure, and the aircraft type used (use ACName). Results should be ordered from earliest to latest.
7. Create your own query with the data you think is most important for an operations manager to see every morning. Describe what the query is supposed to do and why you think it is the most important.