Digital Detective is a cyber crime intelligence analytics system designed to transform raw, unstructured forensic logs into a structured relational database.
It enables:
- Cybercrime pattern detection
- Financial loss tracking
- Suspect profiling
- Investigator performance analysis
- Geographic hotspot identification
Real-world cybercrime logs are messy and inconsistent:
- Free-text incident descriptions break structured parsing
- Duplicate suspect/victim data across multiple cases
- Inconsistent city and region naming
- Difficult manual analysis at scale
This project solves these issues using:
- ETL pipeline for data cleaning
- Fully normalized 3NF relational database design
- Structured entity separation (cases, suspects, victims, investigators, etc.)
- Optimized SQL analytics layer
The system follows a strict Third Normal Form (3NF) design.
- Cyber Cases → Central transactional table
- Locations → Geographic intelligence layer
- Suspects → Threat actor profiling
- Victims → Impacted organizations tracking
- Investigators → Performance evaluation system
- Evidence → Digital forensic records
Cases act as the central hub connecting: suspects → victims → investigators → locations → evidence
The system includes pre-built analytical queries for intelligence extraction.
- Crime type frequency
- Financial damage distribution
- Attack vector comparison
- City-wise cybercrime density
- Regional risk classification
- Financial damage hotspots
- Repeat offenders detection
- Arrest status tracking
- Financial damage attribution per suspect
- Case success rate
- Resolution time analysis
- Workload distribution
To ensure fast query execution:
- Indexing applied on high-frequency search columns
- Optimized joins across relational tables
- Reduced full table scans
- Improved analytical query speed significantly
- MySQL 8.0+
- MySQL Workbench / any SQL client
- Clone the repository
- Import database schema into MySQL
- Load dataset into tables
- Run analytical query module
- Ransomware causes highest financial damage per incident
- Mumbai & Delhi are major cybercrime hotspots
- Few suspects account for repeated high-value attacks
- Investigator efficiency varies significantly across units
- MySQL 8.0
- SQL (Advanced Joins, Aggregations)
- Data Modeling (3NF normalization)
- ETL Pipeline Design
Aditi Paitandy
GitHub: https://github.com/aditi-paitandy
If you like this project:
- ⭐ Star the repository
- 🍴 Fork it
- 🔁 Share it with others
Built as a real-world cyber intelligence simulation system for advanced SQL + data engineering practice.