In this activity, you will download two tables of data from the Homeland Infrastructure Foundation-Level Data (HIFLD) website and one table of data from the American Community Survey (ACS).

Instructions for downloading the two tables from the HIFLD website are listed in the Useful Notes at the bottom of this exam. You may download the table of data from the ACS here [CSV] Download You may download the table of data from the ACS here [CSV].

"According to the U.S. Census Bureau, 31% of the people that perished in tornadoes between 2009 and 2011 were residing in or fleeing from mobile homes. An inventory of mobile home park locations and the number of mobile homes within those parks is, therefore, essential for emergency preparedness, response, and evacuation." [HIFLD Website: Mobile Home ParksLinks to an external site.]

This first HIFLD table is a listing of mobile home parks across the United States; and the second HIFLD table is a listing of historical tornado tracks. The ACS table (b01003) provides the total population of each U.S. state. You will load all three of these data tables into a MySQL database named ex2 to complete the following tasks and/or answer the following questions. When answering a question, you must provide the queries you used to answer the question.

1. the following three VIEWS:
   
   
   1. a VIEW which lists the total number of tornado track records for each state during the time interval 2000-2015. (Requirement:  VIEW)
      
       
   2. a second VIEW which lists the number of mobile home units in each state. (Requirement:  VIEW)
      
       
   3. a third view which lists the total number of tornado fatalities in each state. (Requirement:  VIEW)
      Note: The column named fat in the tornado track data lists the total number of fatalities, and the column st specifies the state associated with each record.
      
       
2. List the five U.S. states in order which contain the largest number of LARGE mobile home parks. (Requirement: Answer question. Provide query.)
   
    
3. List the five U.S. states in order which have had the greatest number of fatalities due to tornados from 1950 to 2015. Include the total population for each state in the resulting table. (Requirement: Answer question. Provide query.)
   Note: The column geo_stusab in the population data specifies the state by abbreviation.

The SQL queries you write to answer these questions must do all of the calculations. Your answer to the above questions cannot involve doing additional calculations outside of the query after looking at the results of a query. Use MySQL to do the calculations, but do not simply use MySQL as a calculator.

In your Linux user home folder, a text file named answers.txt that contains answers to each of the questions above as well as the SQL queries you used to find these answers. Clearly state which answer in this file corresponds to which question. During grading, the SQL queries you include in this file will be tested on the database you created.

You must a new MySQL user named grader and GRANT it only the privileges needed to execute the queries included in answers.txt. Set the password for grader as

passworD1@

When you have completed all of these tasks, stop your VM and create an image named exam2 in project 4355-sum23-euid (where euid is replaced by your EUID). Since you will share images in this project with the instructor and TA, they will access your image to grade it.

Answer rating: 100% (QA)

To accomplish this task youll need to perform several steps ... View the full answer