TechDiva001/galamsey-impact-tracker

SQL and SQLite project tracking galamsey's impact on Ghana's water and public health from 2020 to 2026. 6-table relational database, SQL joins, and Plotly visualizations covering mercury contamination, affected populations, and regional hotspots. # 🌍 Galamsey Impact Tracker **A relational database and analytics project tracking the water and public health impact of illegal mining (galamsey) in Ghana, 2020–2026.** Built as the Phase 2 capstone of my self-directed AI/ML learning journey, focused on SQL, relational database design, and data visualization. --- ## 📌 Overview Galamsey (illegal small-scale gold mining) has been steadily poisoning Ghana's rivers for years, but the scale of it is hard to grasp from headlines alone. This project turns that problem into a structured, queryable dataset: a 6-table relational database in SQLite, joined and analyzed with SQL, and visualized with Plotly, covering water quality, mining activity, water supply risk, and public health outcomes across all 16 regions of Ghana. The goal was simple: take a real, urgent national issue and show what it looks like when you actually structure the data around it, instead of just reading about it. --- ## 🎯 Project Objectives - Design and build a normalized relational database from scratch using SQL - Populate 6 linked tables with research-backed real-world data for all 16 Ghana regions - Use SQL joins, aggregations, subqueries, and connectivity analysis to extract insights - Visualize environmental degradation, water contamination, and health impacts over time - Identify which clean rivers are next in line for downstream contamination - Quantify the human cost — deaths, disease cases, children affected, population at risk --- ## 🗄️ Database Schema The database (`galamsey_tracker.db`) contains 6 relational tables: | Table | Description | |---|---| | `regions` | All 16 regions of Ghana: population, area, and galamsey hotspot status | | `rivers` | Rivers per region: length, contamination status, and downstream flow target | | `water_quality_readings` | 126 readings (2020–2026): pH, mercury, turbidity, cyanide, and lead levels per river | | `mining_activity` | Mining activity per region per year: hectares destroyed, number of s …