Overview
Created and managed a relational database in a PostgreSQL server, then used pandas to extract the data and display it in Google Sheets and Excel.
The goal was to build a presentable SQL database that could connect to an Excel file for easy viewing. I built a database for the Celtics' 2024–25 starting five: one table of players, and several related tables holding stats and details for each of them.
Resources
How it was built
Step 1 — ER diagram
The goal was at least five tables, one many-to-many relationship, and a schema normalized to 3NF. I used LucidChart to design the ER diagram before writing any SQL.
Step 2 — SQL script
Next came an idempotent SQL script generated from that diagram — safe to run repeatedly, and responsible for creating every table, inserting its data, and defining at least one view.
Step 3 — Python extraction
Raw SQL output isn't easy to read at a glance, so a Python script pulls the table data and writes it to a Microsoft Excel file. That file opens in Excel through Power Query, which behaves like a normal table and refreshes each time the script runs.
Step 4 — Excel reporting
The last step turns that refreshed table into a PivotTable and PivotChart, so the underlying data can be sliced and viewed a few different ways quickly.