City Property #2

Progress Report: Due by Wednesday, November 7 at midnight

Final: Due by Friday, November 16 at midnight. Last accepted with late days is Monday, November 19 at midnight

Goal of assignment

Setup a DB for the city data and use it to answer the questions posed.

What to do

  1. You will set up a RDBMS for the city data. After the class discussion on designs for the ER diagram, you are to create one that your group will use. You can use/combine any of the ideas discussed. There is a link on Moodle (right below this description) with the ER diagrams done by teams for the first part and discussed in class. You will need to turn in this ER diagram and also note any changes you make as you proceed on this assignment.
  2. You will load in the data from the spreadsheets provided by the city. I would like you to use SQL to do this (not a program or App to interface with the DB) but it can be SQL that is specific to your RDBMS (MySQL, PostgreSQL). You need to document how you do this so I can see what was done. I found a basic web search tells how to do this for various RDBMS.
  3. Use the system you created to answer the questions posed by the city. One main question was the relationship between property type and code violations. They were also interested in seeing if there were proximity issues by using the census track and subtrack. There were also questions about the relationship between violations and how they were grouped. I suggest you start with a basic question and then move to other ones. You will document what you do, the SQL commands you use, and any results you find.

About half way through the assignment you need to turn in a progress report (one per team). It needs to discuss what you have done, the time you have spent, the ER diagram you settled on, and any results you have.

Working in groups

As discussed in class, the teams for this part will be combinations of people from the teams from the first part. The teams will be (names removed):

Team 1

4 people

Team 2

5 people

Team 3

5 people

Team 4

5 people

Turning in your work

There is an assignment link on Moodle. Turn in your file(s) with your answers. It can be in any reasonable format (PDF, RTF, MS Word, Open Office, Pages, ....). There is a link for the progress report and and link for the final result.