Home Who's Reisner? Journalism Florida International University Other Stuff
Database & Public Records Reporting/JOU3121 | News Workshop/JOU 3113



Final Assignment

Fall 2006


You cover police for the Brentwood Times, a small paper in the Midwest. It's your first job out of school; you came to the paper only a few months ago.


The police chief called you into his office this morning and told you he planned to announce a change in the department's approach to crime, moving to what's called "community based policing," the notion that community members should help  police develop the policies, priorities and practices they use to keep communities safe.


The reporter you replaced had been covering the department for years. She left you a database containing information on every violent crime reported to Brentwood Police for the first six months of 2005. (More recent data is not yet available.)


You open the database and notice a table called "Crimes" that appears to contain the crime reports. It has these fields:


CaseID, Offense, Method, Block, Street, Date, Time, Census Tract and Location.

You get nervous when you take a quick look at the table because you see that some of the data is dirty.


You get a little more nervous when you notice that the only information you have about each crime's location is the Block, Street and Census Tract in which it occurred.


You remember from your reporting classes that Census Tracts are small geographic areas that the Census uses to organize its population counts.

You don't know Brentwood that well yet and can't quickly get your hands on a Census map. In any event, the chief told you that police divide the city into five neighborhoods; you're pretty sure they don't think in terms of Census Tracts.


Then you notice another table called "Neighborhoods" and -- bless the journalism gods -- it has fields containing each tract number, the population of each tract and the neighborhood in which it is located.


You realize that this is a lookup table that will link to the "Crimes" table and let you calculate how many crimes of any type occur in each neighborhood and even calculate crime rates.


You get nervous yet again because you don't remember how to do those calculations and, especially, how to do them in Access.


Then you remember Prof. Reisner's class and all the handouts he has on his web site. You also remember an exercise about hunting accidents where you calculated -- what was it? -- how many accidents involved hunters wearing orange and you can get at that handout, too. A quick look tells you that the instructions for doing calculations are on Pages 12 and 21.


Feeling much more comfortable, you go to your editor and propose a story on violent crime in Brentwood. You tell him that you'll be able to look at crime throughout the city, the neighborhoods with the worst crime problems, what kinds of crimes are most common and maybe even tell if there are particular times of month or times of day when certain crimes tend to occur.


Your editor says he wants to see a memo on your analysis no later than Monday, Dec. 18, at 5 p.m. He wants to a budget line and for you to tell him the results of your analysis, the kinds of human sources you intend to consult and what you hope to learn from each. He'd also like to see the results of your queries. You can provide those by copying/pasting the first 10 rows of your results into a Word document, along with the SQL that goes with them.


Feeling much more confident you can do the job, you download the database from this link on the paper's web site and start your work.




Home | Who's Reisner? | Journalism | Florida International University | Other Stuff