| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Spring 2013 COP4709 Assignment 2

Page history last edited by Dr. Ron Eaglin 11 years, 2 months ago

 Assignment 2 - Import and Analyze Data

 

Objectives

 

Demonstrate the ability to import data into SQL Server and analyze that data with basic queries

Assignment

 

In SQL Server you will create a new database called CrimeDatabase. You are going to import some crime statistics into this database. These statistics are available from data.gov - we will use the 2007 uniform crime report which is available at https://explore.data.gov/Law-Enforcement-Courts-and-Prisons/2007-Crime-in-the-United-States/x2gu-2drm - here you can download the Excel spreadsheets of these statistics.

 

You will then import the Florida Statistics from the file 07tbl08fl.xls which will be one of the files in this download (the download is a zip file with multiple spreadsheets representing various Uniform Crime Reporting (UCR) statistics.

 

Now from this data you are going to determine the most dangerous cities based on the field "Violent Crime". The query should show the City, Population, Number of violent crimes, and the number of violent crimes per 1000 residents. It should be ordered so the worst cities based on the number of violent crimes per 1000 persons are at the top of the list.

 

Your report will show the SQL Query you used and the show the worst 10 cities with the fields above. You can easily do this by screen capturing the results of the query from Management Studio and pasting it into your submission. This should be a doc or pdf file turned into the drop box prior to the assignment due date.

 

Your submission will be a one page report that shows the SQL Query and the results.

 

Information

 

The data is available at https://explore.data.gov/Law-Enforcement-Courts-and-Prisons/2007-Crime-in-the-United-States/x2gu-2drm

 

Estimated Completion Time

 

This took me about 20 minutes. To go through the lecture and try different methods of import you should allot a few hours.

 

Supporting Lectures 

 

The data import necessary for this is completely covered in the week 2 lectures COP4709 Course Lectures By Week

 

Questions and Answers

 

Q: What will I need to know to do this assignment (besides the lectures)

A:

This is pretty easily done with a single SQL query. If you are not comfortable with this level of SQL you will want to do some practice with SQL. You can start with the SQL at W3Schools -

 

http://www.w3schools.com/sql/sql_tryit.asp 

 

but this SQL is very basic and this class will go into a LOT more depth. Here is a piece of the schema from my working this sample if you want to play with the data with SQL

 

http://sqlfiddle.com/#!2/231fb

 

Finally - you will need to delve into SQL pretty heavily. As a starting point for this class you need to be familiar with all the SQL commands at this tutorial site

 

http://www.sqlcourse2.com/intro2.html

SQL is NOT HARD - it is extremely powerful and learning the basics should only take a few hours.

 

 

 


 

 

 

External Resources

 

 

Grading Criteria

 

5 points - demonstrating you can successfully download and import the data

5 points - structuring the correct query and reporting the results.

Comments (0)

You don't have permission to comment on this page.