CS1100 Capstone Bird Strikes Project Assignment 18 Please follow the instructions finish the project about excel and access.the last name is LI.This link i

CS1100 Capstone Bird Strikes Project Assignment 18 Please follow the instructions finish the project about excel and access.the last name is LI.This link is the access document. https://drive.google.com/file/d/1EARgMOrZUOF0W305vrB3yqltQQSiiB9L/view CS1100: Project (Assignment 18)
Capstone Project Using Excel & Access
To complete this assignment you must submit an electronic copy to BlackBoard by the due date
of both your Access database and your Excel spreadsheet. Download the starter file and save it
under the name CS1100.LastName.Project and where LastName is your last name.
To download the files from Google Drive,
click the download button — do not open the
files in Google.
Background
Bird strikes on aircraft are a relatively common occurrence and in most instances those strikes do
not impact the flight. The file BirdStrikes.xslx contains a worksheet with data relevant to
analyzing bird strike patterns. Your task is to prepare the data for analysis using both Excel and
Access and then carry out some analysis and data visualization. Some tasks are better done in
Access and some are more suitable for Excel, so you’ll use both. The use of multiple tools in
very common in data analysis and business analytics, so you get a chance to practice this in this
project.
There are 5 queries (not counting all the subqueries) you need for the project. Save the queries
using our usual WAFE naming scheme.
You may wish to build a small database to test and debug your queries. The database you
construct from Excel is too big for testing. You can copy and paste queries from one database to
the other using CTRL-C to copy and CTRL-V to paste. Build the test database so that it has the
same queries but only add a few rows manually.
Part 1
Topic(s)
Pts
Open the BirdStrikes.xslx file and inspect the columns to familiarize yourself with the data
contained in the file.
1
0
Use the wizard to import the data in the BirdStrikes.xslx Excel file by following these steps:
1) Open Microsoft Access and create a new blank database named
CS1100.LastName.Project.
2) Click on External Data in the toolbar, then choose Excel.
11
0
3)
During the prompts of the import wizard:
a) Click Browse where it says File name. This will allow you to find your
Excel file on your computer.
1 of 7
b) Check the first option Import the source data into a new table in the
current database 1.
c) Do not import the Wildlife Num field
d) Choose Record ID as the primary key.
e) Under Import to Table,
i)
use All Bird Strike Data as the table name, and
ii)
check the option I would like a wizard to analyze my table after
importing the data.
Note that the import may take a while due to the large amount of data in the Excel file.
Use the Table Analyzer to help design the database table. Once the data import completes, a
wizard appears.
1) Select I would like a wizard to analyze my table after importing the data.
2) You will get many questions (pop-ups). Click through them all — do not change any
table names, primary keys, or correct values for now. Just accept everything as it is.
3) Finally, you will see a prompt asking whether to create a query to join all the new
tables. select Yes, create the query.
4) Select Finish
5) Do not save the import steps.
N/A
0
Update the table names per instructions given below. It is important to close all tables before
renaming them. Look at the data of each table first before you rename it.
➢ Table with the fields: Flight Date, Report Date, etc. as Bird Strikes
➢ Table with the Field Airport as Airports
➢ Table with the field Aircraft Models as Aircrafts
➢ Table with the field Aircraft Type as Aircraft Types
➢ Table with the field Airlines as Airlines
➢ Table with the field Flight Phase as Flight Phases
➢ Table with the field Impact as Impacts
➢ Table with Wildlife Size as Wildlife Sizes
➢ Table with Effect/Damage as Effects
After renaming all the tables, delete the table Bird Strikes_OLD 2.
12
5
1
Do NOT use the third option to link to the table. While linking is very useful because it propagates changes in the Excel spreadsheet to Access
automatically, having Access not be linked makes Access quicker and more efficient. Of course, if the data in the spreadsheet changes and you
want to analyze the new data, you’ll have to re-import the data.
2
Bird Strikes_OLD contains the original data imported from Excel, before you split them into separate tables. It is no longer useful.
2 of 7
Open the Bird Strikes table and switch to the relationship view. Rename the foreign keys so
each field’s name reflects the name of the field in the corresponding table. Refer to Figure 1
for how the fields should look once this step is complete.
5
Figure 1: Relationship View for Bird Strikes
When using the table analyzer, Access uses an embedded lookup for all linked tables. This
can cause issues when creating queries.
So, the next step is to remove those lookups.
1) View the table design first for the table Bird Strikes and then for
Aircrafts.
2) For each field, change the Lookup from Combobox to Text Box. Figure 2 shows an
example.
3 of 7
5
Figure 2: Changing the Lookup
The wizard asked several times to clean the data. Now it’s time to do it.
5
Some records contain both a date and a time for Report Date in the table Bird
Strikes.
1) Create an update action query that extracts the date and removes the time part in the
Report Date field. Because you cannot undo an update query, you should first make a
copy of the data. Create a make-table action query to copy the data in Bird Strikes to
a new table called Bird Strikes Cleaned. Call your action queries
QLastNameCreateTable and QLastNameForCleaning.
You need to use the function Int([Report Date])to extracts the date from the
field Report Date. Use this function in the Update To field.
2) Test your query on Bird Strikes Cleaned. Only once you are convinced your
query works, then apply the same query to the table Bird Strikes.
** If your update query doesn’t work, delete the Bird Strikes Cleaned table,
make another copy (repeat step 1), and try again. Once you are sure your update query
works correctly, apply it to the Bird Strikes table.**
3) Delete Bird Strikes Cleaned since you won’t need it any longer.
Now that the data is clean, let’s do some analysis. Initially, we’ll create some queries to help
explore the data and answer questions better done in Access rather than Excel. Save the
queries using our usual WAFE naming scheme.
4 of 7
14, 18
30
Create queries to answer the following questions:
1) How many bird strike incidents occurred to each Airplanes and Helicopters?
Your result should have two rows, one for Airplanes and one for Helicopters.
Note that the data also contains some blank aircraft types as well as C.
2) Which airline had the most bird strike incidents? Exclude UNKNOWN and
MILITARY from your analysis, i.e., find the airlines other than UNKNOWN and
MILITARY. Display the airline name only.
3) How many airlines had bird strike incidents during either the flight phase
APPROACH or CLIMB or both.
4) How many airlines had bird strike incidents during both the flight phases
APPROACH and CLIMB.
.
5) How many airlines had incidents during both DESCENT and APPROACH or
CLIMB.
That is, count the number of elements in the set D INTERSECT (A UNION C)
where D = DESCENT airlines, A = APPROACH airlines, and C = CLIMB airlines.
Visualization is best done in Excel, so let’s visualize the proportion of bird strikes across
aircraft type in an Excel pie chart.
15, 7
10
8
10
In Excel, 1) click on the Data tab, 2) choose From Access, and 3) link the data from Query1.
Build a pie chart as shown in Figure 3. Create the chart in a new worksheet. Name it Visual
Analysis.
Figure3: Bird Strikes Incidents by Aircraft Type
Using the original Excel data, create a pivot table that calculates the number of bird strikes per
FlightDate for each airline. Group the date by year. Place the Pivot Table in a new
worksheet . Name it Pivot Analysis, a portion of the pivot table is shown in Figure 4.
5 of 7
Figure 4: Count of bird Strikes per year for each airline
30
Part 2 : Peer Feedback
1) Import the smaller bird strikes database SmallDB.accdb into Excel. You will
have three spreadsheets which you should rename Airlines, Birdstrikes, and
FlightPhases. Name your file ProjectPart2-MyLastName.xlsx, where
MyLastName is your last name.
2) Imitate query 4 from Part 1 (i.e. How many airlines had bird strike incidents during
both the flight phases APPROACH and CLIMB) using either Excel tools or Google
sheets. Note that this will involve multiple steps. You can follow one of the
approaches described in Part2-Help document.
The solution should be in a fourth sheet, rename it Part2-Solution. Document each
step by numbering them and providing a brief description.
3) Share the link to your Google sheets with your partner to get feedback. If you create
your solution in Excel, then open your file in google sheets to be able to share your
work. Add a note as to whether the solution was created in Excel or using google
sheets.
Make sure you also share your Excel file by uploading it to blackboard in case you
use Excel to complete step 2. This is because pivot tables are not viewable in the
Google sheets format.
*Note that you need to have a google account to be able to complete this part*
4) Test your partner’s solution by checking all the steps and provide feedback by adding
comments to your partner’s formulas.
You will 1) test your partner’s solution using the Excel file and 2) give
feedback by adding comments to the formulas in their Google sheets.
** If your partner fails to share their solution by the due date, then a generic solution
will be provided to complete this part of the project.**
6 of 7
Follow these instructions to share your Google sheets and add feedback to
your partner’s solution.
5) Submit the link to both your solution and your partner’s solution as part of your
project submission. If your partner used Excel to create their solution, then upload
their Excel file as well.
The peer feedback should include the following:
– Whether the solution is correct or not. Provide a justification. If the query is claimed
to be incorrect then a counter example should be provided.
– How efficient is the approach followed and whether there is an easier way to
simulate the query.
This question will be graded based on two parts:
– Sharing the solution link with your partner by the due date (10 points)
– Completeness of the feedback given to your partner (20 points)
15/16
Part 3 : Bonus
Reports are best suited for Access. Create the report below by following these steps:
1) Update the table Flight Phases. Manually change the blank labels to
UNKNOWN for FlightPhaseID=1. You do this by opening the table and typing
the text UNKNOWN.
2) Create a query that calculates the number of incidents for each flight phase. Use
subqueries as needed.
3) Create a report that lists the flight phases and the corresponding number of incidents
using the query you created in step 2.
7 of 7
10
IMPORTANT:
If your partner fails to share their solution by the due date, then you can provide your feedback
to a generic solution that will be available in blackboard under the folder Project.
Help with part2 of CS 1100 project: Simulation Ideas and Peer
Feedback
There are different ways to simulate query 4 in a spreadsheet. Below are some of the
approaches. You start with the primitives you used in the Access solution and translate
each primitive into a spreadsheet solution. Sometimes multiple Access primitives can be
combined into one Excel solution using a powerful spreadsheet construct, such as a
pivot table.
Approach 1: Using Google Sheets (for adventurous students)
Create all unique pairs of (Airline,FlightPhase). We will reuse those pairs for both computing the
ApproachAirlines and the ClimbAirlines.
Step 1: Computing the unique pairs is broken down into first computing all pairs
(Airline,FlightPhase) and eliminating the duplicates.
➔ To compute all the pairs (Airline, FlightPhase) we start with the BirdStrikes table
and look up the Airline name in Airlines and the FlightPhase name in
FlightPhases.
➔ To eliminate the duplicates we use the UNIQUE function.
Step 2: To compute the ApproachAirlines, we use an IF followed by a FILTER to eliminate
empty cells.
Do the same for ClimbAirlines.
Step 3: To intersect ClimbAirlines and ApproachAirlines, we use MATCH (simulating a join).
Step 4: Finally, we count.
To get the database into Google sheets, import the database into Excel. Import 3 tables in 3
different worksheets and rename them Airlines, etc. Then open the Excel file in Google sheets
and convert it to Google sheets.
Approach 2: Using Excel and functions you know
We use Excel tools learned in class to simulate query 4. We import the small Access database
into Excel and create 3 worksheets called Airlines, Bird Strikes and Flight Phases. Airlines and
Flight Phases will be used for lookup. Add a fourth worksheet to create your solution, name it
Solution.
Step 1: Copy the columns (FlightPhaseID and AirlineID) from the Bird Strikes sheet into the
Solution sheet (the 4th sheet you added).
Step 2: Add a third column where you lookup the phase name for each FlightPhaseID using the
data in the Flight Phases sheet.
Step 3: Filter the data so you only keep Approach and Climb phases.
Step 4: Get the count of the phases for each airline (Note that the count should either be 1 or 2)
Step 5: Get the count of the airlines with a count of phases that is equal to 2.
Approach 3: Using Excel and functions you know
We use Excel tools learned in class to simulate query 4 using a spreadsheet. We import the
small Access database into Excel and create 3 worksheets called Airlines, Bird Strikes and
Flight Phases. Airlines and Flight Phases will be used for lookup..
Step 1: Create the pairs (FlightPhase, Airline) by translating the IDs into the English names like
Approach for a flight phase and AA for an airline.
Step 2: This step is a “big” step using the pivot table functionality. It eliminates duplicates and
creates at the same time all Approach Airlines and all Climb Airlines. Create a pivot table using
the FlightPhase and Airline columns.
Step 3: “Reach” into the cells of the pivot table and create the intersection of the Approach
Airlines and the Climb Airlines.
Step 4: Finally, we count.
There are many more correct solution approaches than the ones presented above.
For the peer feedback:
Test the formulas of your partner. You can add one more record to the Bird Strikes table so that
the result for query 4 is 2. Do the partner’s formulas work correctly with one more record added?
If you claim that there is a wrong formula in your partner’s solution, you must provide an input
(the three tables, including the Bird Strikes table) where the partner’s solution fails.

Purchase answer to see full
attachment