The primary aim of the project was to design and implement a structured relational database to analyze crime patterns, track criminal activities, and support law enforcement operations.
Objective:
The primary aim of the project was to design and implement a structured relational database to analyze crime patterns, track criminal activities, and support law enforcement operations.
-
Database Design:
- Developed a normalized schema with tables for Crime, Criminal, Victim, and Officer to ensure data integrity and efficient querying.
-
Data Management:
- Populated the database with realistic data containing at least 100 rows per table, capturing diverse and detailed information.
-
SQL Query Development:
- Implemented SQL queries to analyze crime data, such as:
- Trends in crime type and crime locations.
- Insights into officer case loads and criminal profiles.
- Age and gender-based distributions of victims and criminals.
- Optimized queries to enhance performance and reduce execution time.
- Implemented SQL queries to analyze crime data, such as:
-
Advanced Queries:
- Created joins, subqueries, and aggregations to extract meaningful insights like:
- High-crime areas.
- Officers handling the most cases.
- Recent crimes and arrests.
- Created joins, subqueries, and aggregations to extract meaningful insights like:
- Identified high-crime locations and most common crime types.
- Analyzed demographic details such as age and gender for both victims and criminals.
- Evaluated officer performance and workload distribution.
- Found connections between crimes, victims, and criminals.
- Tools: SQL, MySQL Workbench
- Skills: Database Design, SQL Query Optimization, Data Analysis, Reporting
This project successfully built a comprehensive database for analyzing crime data, enabling efficient tracking and decision-making for law enforcement activities. It also enhanced practical skills in database management and data analysis.