Task #

Task Description

1

Normalize an expanded version of the Flight Departures dataset Download an expanded version of the Flight Departures datasetfrom the Bureau of Transportation Statistics into Second Normal Form (2NF).

Create a new database in DB Browser for SQLite and a new SQL file in which to copy the Data Definition Language (DDL)Links to an external site. statements, DROP TABLELinks to an external site. and CREATE TABLELinks to an external site., and the Data Manipulation Language (DML)Links to an external site. statement, INSERTLinks to an external site., in the DDLDML Worksheet.

Create a new SQL file (e.g., normalization.sql) in which to write your database normalization DDL and DML statements to normalize the Departure0NF table to First Normal Form (1NF) then to 2NF.

Note: This SQL file must run from the first line to the last line multiple times without errors. If the run/execution stalls (i.e., the stop button is enabled), stop it and rerun your SQL code.

Create an entity-relationship diagram (ERD) for your 2NF tables (e.g., flight-delay.drawio), and cross-check that your ERD matches your SQL code for normalizing the data. Ensure the order of your SQL statements respects the constraints enforced by the table relationships.

Note (again): This SQL file must run from the first line to the last line multiple times without errors. If the run/execution stalls (i.e., the stop button is enabled), stop it and rerun your SQL code.

2

Import additional Flight Delay datasets into a single prepared table via INSERTLinks to an external site. statements created using Excel functions and operations.

Open and run this auxiliary-and-template-tables.sql Download auxiliary-and-template-tables.sqlfile to create auxiliary lookup (2NF) tables and an empty DepartureDelay table in which to add the following Flight Delay datasets:

CarrierDelay.xlsxDownload CarrierDelay.xlsx

LateAircraftDelay.xlsxDownload LateAircraftDelay.xlsx

NASDelay.xlsxDownload NASDelay.xlsx

SecurityDelay.xlsxDownload SecurityDelay.xlsx

WeatherDelay.xlsxDownload WeatherDelay.xlsx

Complete the DML Worksheets referencing the relevant Excel functions and operations used in the DDLDML Worksheet used for Task 1.

1. Note 1: It is key to understand the purpose of the Excel functions used (e.g., SUBSTITUTE(…)Links to an external site. to handle airport names with apostrophes, such as O’Hare, TEXT(…)Links to an external site. for formatting dates, and IFLinks to an external site.(LEN(…)Links to an external site.>0, …, …) for checking empty cells signifying no data or the last data in a list). Refer to the contents of DelayCompensation2NF table for valid values to INSERTLinks to an external site. into the DelaySource column in the DepartureDelay table (e.g., ‘Late Aircraft’ for the second dataset).

Note 2: The DDLDML Worksheet of Excel file from task 1, can help you understand how to extract the information from different cells. However, you cannot simply copy/paste the formulas into Task 2 worksheets, as the order of information is different as well as it include information for columns which are not part of Task 2 database table. 

Note 3: Verify the correct order of information that needs to be in the INPUT statement for Task 2 database table.

Save your modifications to the Excel files above (for submission), and copy and run your INSERTLinks to an external site. statements from the DML worksheets in DB Browser for SQLite to insert all Flight Delay data into the DepartureDelay table.

3

Create an inner-joined (data warehouse-type) table on which to run queries that return information typically exhibited as PivotTables.

Open, complete, and run this inner-join.sql Download inner-join.sqlSQL file to create a consolidated table of flight departure delay information.

1. Refer to the sections for Inner Joins and Cartesian Joins in Chapter 10Links to an external site. of the course textbook, as well as the upcoming lecture.

Note: Reference the SQLite strftime(…) functionLinks to an external site. to obtain different parts of the date

Open, answer/complete, and run/check this queries.sql Download queries.sqlSQL file to display the requested information using SELECTLinks to an external site. statements.

• Note 1: Write only one SELECTLinks to an external site. statement per Query # prompt.

Note 2: DelayRate = sum(DelayMinutes)/sum(Departures)

CAVEAT: Rates are not additive; they are ratios of sums (not sums of ratios)

CAVEAT: Rates are REAL numbers; you may multiply the numerator by 1.

1. Note 3:

For cross-tabs, consider using the CASELinks to an external site. statement in SQLite. You may need to use multiple CASE statements to show the required record for each Quarter.