The purpose of this project is to include crime analysis, statistical reporting, and understanding patterns related to criminal incidents in different locations. The database, called NYPD, contains tables for Incidents, Perpetrators, Victims, and Locations.
The Incident table contains details about each incident such as:
- ID - A unique identifier
- Date and Time - When the incident occurred
- Borough - The neighborhood where it happened
- LocationDesc - Description of the location
- Precinct - The connected police station
- Other fields including jurisdiction, classification, murder flag, perpetrator and victim details, and location coordinates.
The other tables (Perpetrator, Victim, Location) link to the Incident table and contain specific details about those entities.
The Incident table uses ID as its primary key. Foreign keys in the other tables reference the ID field to connect the data.
Some sample informational questions explored with SQL queries:
- Average perpetrator age group for incidents in Brooklyn
- Most common victim sex/race combination in Manhattan
- Number of incidents at bodegas in Queens
- Precincts with most Hispanic perpetrator incidents
- Age/sex breakdown for Bronx apartment victims
- Most common perpetrator race/sex against senior victims
- Number of incidents with 18-24 perpetrator and victim
- Homicide victims by gender
The separation into multiple linked tables enables these detailed analyses. Joins across the tables connect the relevant information.