Computer Assignment 1: Querying Data, and Creating Tables, Charts, and Graphs

The purpose of this assignment is for you to be able to query information from existing databases, download the information, create tables, charts, and graphs in Excel
as well as interpret the results. See the lecture in Blackboard for substantive information on creating charts, graphs, tables, and statistics.
This assignment will be submitted through the plagiarism detection software, SafeAssign, in Blackboard. This software will confirm that your work is original. Do not
copy or use other students’ assignments to complete this assignment, as you will receive a zero on the assignment if you plagiarize and may receive an F in the course
depending on the seriousness of the violation. You will submit only one Word document containing all parts of the assignment completed. No late assignments are
accepted.
Part 1. (32 points: 80%): Use the following FBI website to do a query and create your own spreadsheet of data in Excel:
http://www.ucrdatatool.gov/Search/Crime/State/StatebyState.cfm
There are three parameters for your query:
a. Choose one or more states: for this select either the entire United States or only ONE
state of your choice. Selecting more than one state will give you too many tables to work with.
b. Choose one or more variable groups: Again, only select ONE variable group and for this assignment you should choose either the violent crime rates or property crime
rates. DO NOT select NUMBER of violent or property crimes.
c. Choose years to include: You can choose whatever time frame you would like as long as it has at least 10 years in a row (e.g., 1971 to 1981).
?????
After you “Get Table” you will create a spreadsheet in Excel by clicking on “Spreadsheet of this table (.cvs file). Although a .cvs file is not an Excel file, it will
ask you whether you want to
???open it 1.
2. 3.
4.
5. 6.
in Excel, which you do. To complete the assignment, you will follow these steps:
Format the table that is provided by the website so that it would look good in a report (i.e., add borders, colors, bold column titles, etc.).
Create a line graph that depicts the crime rate over time.
Create a pie-chart that depicts the percentages of the crime type breakdown in one year (e.g., property crime is broken down into burglary, larceny-theft, and motor
vehicle theft). Do NOT include the total for the category in your pie chart, only the subcategories.
Create a third chart or graph (not table) of your choice with the same data, as long as it makes sense.
Copy the table and the three charts, and paste them into a Word document.
After each table or chart, describe in three sentences or so what it is presenting as well as make several conclusions based on what the results are. For example, you
might write something like this for the line chart: The chart depicts the overall property crime rate per year for the state of Arizona from 1985 to 2000. The chart
shows that the overall property crime rates were highest in Arizona from in 1989, 1990, 1994, and 1995 with 1995 being the highest. Also, the property crime rates were
lowest in the most recent years (i.e., 1998 to 2000).
Part 2. (8 points: 20%): Use the following website to do 5 separate queries. You will create YOUR OWN spreadsheet of the query data in Excel.
http://webappa.cdc.gov/sasweb/ncipc/mortrate10_us.html
For the queries you will be calculating the risk of death rates. You need to do this 5 separate times. You may use any intent or manner of injury, variation of cause
or mechanism, region of the country, sex, race, year, or age categories, etc. You should select age-adjusting using 2000 as the Standard Year (use this for ALL 5
queries).
To complete the assignment, you will follow these steps:
1. Create ONE table in Excel that contains the results of ALL 5 queries in a way that is easy to understand. You might first have to copy and paste your queries into
Excel separately, then put them all together into one table. Your queries should have something to do with one another so they can be presented together.
For example, you may do the same query for five different years, or you can do five different types of deaths for white females ages 30 to 34. Include the information
that is the same for all the queries in the title. The columns should include: number of deaths, population, crude rate, age adjusted rate. The rows would be the
values of the variable that you did different queries on. In the examples above, it would be year and type of deaths. Label the rows and columns appropriately.
2. When finished, format the table so it looks nice, copy and paste it into the SAME Word document and write several sentences describing what the table shows overall
and what each row and column shows in your table. You may pick out one interesting value, but don’t give your opinion about why you think that result was found. This
is not an exercise in figuring out why people die, it is just about using secondary databases and Excel.