Flights Galore Inc. provides “on-demand” aviation charters using a mix of different aircraft and aircraft types. The company has grown rapidly. You have been hired as its first database manager. The company’s database, developed by an outside consulting team, is already in place to help manage all company operations. Your first critical assignment is to develop a data warehouse to analyze the charter data. The charter operations manager wants to be able to analyze charter data such as cost, hours flown, fuel used, and revenue. He also wants to be able to drill down by pilot, type of airplane, and time periods. Review the summary of the company operations to complete the problem: Flights Galore Inc uses a fleet of four aircraft to provide charter services. Aircraft are identified by a unique registration number. Therefore, the aircraft registration number is an appropriate primary key for the AIRCRAFT table. The destinations are indicated by standard three-letter airport codes. ATL – Atlanta, GA, BNA – Nashville, TN, DFW – Dallas/Ft Worth, TX, etc. Year 2016 Sum of Value Region Product Region 1 Region 2 Region 3 Region 4 Total Figure 1 Sample output for pivot table MSITM-6301 Business Data Warehousing Course Group Project Page 3 of 4 AC-TTAF = Aircraft total time, airframe (hours) AC_TTEL = Total time, left engine (hours) AC_TTER = Total time, right engine (hours) The nulls in the CHARTER table’s CHAR_COPILOT column indicate that a copilot is not required for some charter trips or for some aircraft. Federal Aviation Administration (FAA) rules require a copilot on jet aircraft and on aircraft that have a gross take-off weight over 12,500 pounds. None of the aircraft in the AIRCRAFT table are governed by this requirement; however, some customers may require the presence of a copilot for insurance reasons. All charter trips are recorded in the CHARTER table. Customers are charged per round-trip mile, using the MOD_CHG_MILE rate. The MOD_SEATS column lists the total number of seats in the airplane, including the pilot and copilot seats. Therefore, an airplane with 10 seats includes the pilot, a copilot, and eight passenger seats available. The pilot licenses shown in the PILOT table include the ATP = Airline Transport Pilot and COM = Commercial Pilot. Businesses that operate “on demand” air services are governed by Part 135 of the Federal Air Regulations (FARs) that are enforced by the Federal Aviation Administration (FAA). Such businesses are known as “Part 135 operators”. Part 135 operations require that pilots successfully complete flight proficiency checks each six months. The “Part 135” flight proficiency check date is recorded in PIL_PT135_DATE. To fly commercially, pilots must have at least a commercial license and a 2nd class medical certificate (PIL_MED_TYPE = 2). The pilot ratings (PIL_RATINGS) include: SEL = Single Engine, Land MEL = Multi-engine Land SES = Single Engine (Sea) Instr. = Instrument CFI = Certified Flight Instructor CFII = Certified Flight Instructor, Instrument Given those requirements, use the data in the Excel file FLTSGALORE and MySQL to complete the following: HINT: The Excel file FLIGHTSCALOREDATA contains the data for the CHARTER, AIRCRAFT, MODEL, EMPLOYEE, PILOT, and CUSTOMER TABLES. First you must filter, integrate, and consolidate data for the Data Warehouse. Using MySQL, you will need to create the tables and the additional dimension tables before creating the fact table. (i.e., TIME dimension) Be sure to review the data in the tables to complete the data warehouse project. NOTE: Save answers to a–c, and f in the Word file. The answers to d and e will be in your MySQL dump and script files. A. Create the dimensional model for the Charter data warehouse. B. Define the necessary attribute hierarchies for reporting. (ex. City->State->Country)
