Overview

The first homework is to construct a set of SQL queries for analysing a dataset that will be provided to you. The dataset contains information about every court case on the Maryland Judiciary Court Search website from the past decade. We focus on Maryland because Andy is a Maryland native and the members of the TA team were curious about his criminal past.

This homework is an opportunity to: (1) learn basic and certain advanced SQL features, and (2) get familiar with using the SQLite DBMS. This is the same relational DBMS that you will be hacking on during the rest of the semester.

This is a single-person project that will be completed individually (i.e., no groups).

• Release Date: Aug 28, 2017
• Due Date: Sep 13, 2017 @ 11:59pm

Project Specification

The homework contains 12 questions in total, and will be graded out of 100 points. For each question, you will need to construct a SQL query that fetches the desired data from the SQLite DBMS. Here's a rough time estimate: 1-2 hours for setting up SQLite; approx. 4-6 hours for completing the questions.

We provide the database dump (md_courts.dump.gz) on which your queries will be executed. We will also provide a compressed folder (sql.tar.gz) containing empty placeholder files (00.txt,01.txt,...,12.txt). You will need to fill in the output of the SQL queries in these placeholder files.

You can decompress this folder by running the following command on the terminal:

$ tar -zxvf sql.tar.gz

After filling in the queries, you can compress the folder by running the following command:

$ tar -czvf sql.tar.gz hw1

Instructions

Setting Up SQLite

You will first need to install SQLite on your development machine.

Install SQLite3 on Ubuntu Linux

Install the sqlite3 and libsqlite3-dev packages by running the following command;

$ sudo apt-get install sqlite3 libsqlite3-dev

Install SQLite3 on Mac OS X

On Mac OS Leopard or later, you don't have to! It comes pre-installed. You can upgrade it, if you absolutely need to, with Homebrew.

Load the Database Dump

1. Check if sqlite3 is properly working by following this tutorial.

2. Download the database dump file:

$ wget http://15445.courses.cs.cmu.edu/fall2017/files/md_courts.dump.gz

1. Reconstruct the database from the provided database dump by running the following command on your shell. This page contains more details.

$ zcat md_courts.dump.gz | sqlite3 md_courts

1. Check the contents of the database by running the .tables command on the sqlite3 terminal. You should see 9 tables, and the output should look like this:

$ sqlite3 md_courts.db
SQLite version 3.11.0
Enter ".help" for usage hints.
sqlite> .tables
attorneys   charges     documents   judgements  rawcases  
cases       complaints  events      parties

Placeholder Folder

Download the placeholder folder at here:

$ wget http://15445.courses.cs.cmu.edu/fall2017/files/sql.tar.gz
$ tar zxfv sql.tar.gz

This should contain empty placeholder files (00.txt,01.txt,...,12.txt).

Sanity Checks

1. Get familiar with the schema (structure) of the tables (what attributes do they contain, what are the primary and foreign keys, etc.). Run the .schema $TABLE_NAME command on the sqlite3 terminal for each table. The output should look like this:

sqlite> .schema cases
CREATE TABLE cases (
    case_id character varying NOT NULL PRIMARY KEY,
    "timestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    title character varying,
    court_system character varying,
    type character varying,
    filing_date date,
    status character varying,
    disposition character varying,
    disposition_date date,
    violation_county character varying,
    violation_date date
);
sqlite> .schema attorneys
CREATE TABLE attorneys (
    case_id character varying NOT NULL,
    name character varying,
    type character varying,
    appearance_date date,
    removal_date date,
    practice_name character varying,
    address character varying,
    city character varying,
    state character varying,
    zip character varying,
    FOREIGN KEY (case_id) REFERENCES cases(case_id)    
);
CREATE INDEX attorneys_case_id_idx ON attorneys (case_id);

1. Perform a sanity check by running the following query:

Q0 [0 points]: Count the number of cases in the Maryland Judiciary Court System. The output should look like this (only a single number):

sqlite> select ...;
12345

Details: Make use of the count function.

Answer: Here's the correct SQL query and expected output:

sqlite> select count(case_id) from cases;
2000728

1. To redirect the output of this SQL query to the appropriate file (00.txt) in the submission directory (sql), enter the above query string (including ';') into a sql file (00.sql) and then run the following command:

$ sqlite3 md_courts.db < 00.sql > sql/00.txt

1. Now, you can verify the contents of the output text file (00.txt):

cat solutions/00.txt
2000728

Construct the SQL Queries

Now, its time to start constructing the SQL queries and fill in their output into the placeholder files.

Q1 [5 points]: Count the number of attorneys in the system. The output should look like this (only a single number):

sqlite> select ...;
12345

Q2 [5 points]: Repeated phone calls can sometimes land you in jail. Count the number of charges related to phone calls by examining their description. The output should look like this:

12345

Q3 [5 points]: Reckless endangerment is another reason why one can end up in a courthouse. Count the number of cases related to reckless endangerment in each county. The output should look like this:

County A|500
County B|400
County C|300

Q4 [5 points]: Let's now go back in time and look at the cases filed in the 1950s. The output should look like this:

CASE1|1950-03-12
CASE2|1951-01-01
CASE3|1952-01-01

Q5 [10 points]: It looks like a lot of cases got filed in the 1950s. In which decades did the most number of cases get filed? The output should look like this:

10000|1970s
5000|2000s
1000|1980s

Q6 [5 points]: Cases can often be closed for bizzare reasons. One such reason is statistics. Determine the percentage of cases that were statistically closed. The output should look like this:

5.123456789

Q7 [5 points]: Let's look at some prolific defendants. List the top 3 parties who have been charged in the most number of distinct counties. The output should look like this:

A|100
B|50
C|10

Q8 [20 points]: How does the average age of guilty criminals vary over time? The output should look like this:

2017|50.123456789
2016|55.123456789
2015|60.123456789
2014|55.123456789
2013|50.123456789

Q9 [15 points]: Let's next look at case disposition by race to see if there is any inherent bias in the system. The output should look like this:

African American|Guilty|60.000
African American|Not Guilty|40.000
Caucasian|Guilty|50.000
Caucasian|Not Guilty|50.000

Q10 [5 points]: Certain zip codes might have -- ahem -- more interesting citizens than Squirrel Hill. Retrieve the top 3 zip codes in Maryland where the most number of cases were filed. The output should look like this:

21000|500
21001|400
21002|300

Q11 [15 points]: Some attorneys are awesome at their job. List the top 5 attorneys in Maryland by examining the number of cases that an attorney handles and the percentage of cases wherein they were successful. The output should look like this:

A|100|60.123
B|200|45.123
C|100|30.123
D|500|20.123
E|100|10.123

Q12 [5 points]: Find the attorney with the seventh highest success percentage (by extending the previous query). The output should look like this:

G|50|5.123

Details: Print the attorney's name, number of cases handled, and the percentage of cases won (i.e. the disposition was Not Guilty). Examine only attorneys who have handled more than 100 cases. Ensure that the attorney's name is not empty.

Grading Rubric

Each submission will be graded based on whether the SQL queries fetch the expected sets of tuples from the database. Note that your SQL queries will be auto-graded by comparing their outputs (i.e. tuple sets) to the correct outputs. For your queries, the order of the output columns is important; their names are not.

Late Policy

See the late policy in the syllabus.

Submission

We use Autolab for grading in order to provide you with immediate feedback. After completing the homework, you can submit your compressed folder sql.tar.gz (only one file) to Autolab:

• https://autolab.andrew.cmu.edu/courses/15445-f17

We will be comparing the output files using a function similar to diff. Ensure that your output matches the expected output in 00.txt. You can submit your answers as many times as you like. Your score will be sent via email to your andrew account within a few minutes after your submission.

Additional Rule: In addition to placing the text files containing the query output in sql.tar.gz, you will also need to submit all the SQL queries in files named 00.sql, 01.sql, ..., 13.sql in the same folder. If your handin folder sql.tar.gz does not contain these files with SQL queries, your final homework score will unfortunately be reduced.

We have downloaded the submission order and scoreboard before this rule was added. We will align your rankings with your prior submission order. Adding the SQL queries will not affect you earlier rankings.

Collaboration Policy

• Every student has to work individually on this assignment.
• Students are allowed to discuss high-level details about the project with others.
• Students are not allowed to copy the contents of a white-board after a group meeting with other students.
• Students are not allowed to copy the solutions from another colleague.

WARNING: All of the code for this project must be your own. You may not copy source code from other students or other sources that you find on the web. Plagiarism will not be tolerated. See CMU's Policy on Academic Integrity for additional information.