TradeSphere

Server- and client-side code for a web application showcasing the prettified output of complex and dynamically generated SQL queries that pull international trade data from TradingEconomics.com, among other sources.

TradeSphere lets you explore US trade with about 200 partner countries, mostly as of 2022, alongside country-level indicators from 2005 to 2023, such as wages, unemployment, living conditions, and temperature change. It was built in Fall 2023 as the group final project for CIS 5500: Databases & Information Systems at the University of Pennsylvania, taught by Professor Susan B. Davidson.

Try the live demo. It runs the project's SQL queries on a copy of the database inside your browser, so there is no server to keep paying for.

View the Final Report for details about the database architecture, table normalization practices, API specification, query performance and evaluation, and the entity-relationship (ER) diagram.

View the Video Demo for a video demonstration of the functionality of each page.

Features

• Home: the top five US imports or exports by dollar value, for the world or one of five countries, and starter questions that link into the rest of the site.
• US Trade: a map of the top 5 US trading partners for any trade type and product category, plus tables of every partner, trade volume by continent, and each partner's largest trade category.
• Country Profiles: one page with four tabs that filter countries with sliders and drop-downs: living conditions, temperature change, highest unemployment rate, and wages (a two-country line chart plus average yearly wage growth).
• About: the pipeline, the ER diagram, what the team learned (including a query optimization with both versions of the SQL), and the 2026 revamp.
• Show SQL under every chart and table, with the query and its current parameters.
• Tables with search, column filters, and CSV export.

How it works

TradingEconomics API ─┐
                      ├─> Python + pandas ─> MySQL on AWS RDS ─> Express API ─> React app
Our World in Data ────┘   (clean, merge,      (17 tables,          (16 routes)   (MUI, Recharts,
                           unit conversion)    up to 312k rows)                   Leaflet)

1. Collect: Python notebooks in preprocessing/ pull economic indicators for 196 countries from the TradingEconomics API and country statistics from Our World in Data.
2. Clean: pandas removes duplicates, fixes inconsistent country names, splits dates into year and month, and builds a unit-conversion table so values reported in different currencies, scales, and frequencies can be compared in USD per year. The cleaned CSVs are in data/.
3. Store: the data loads into a MySQL schema of 17 tables designed to BCNF or 3NF, with country names as foreign keys. UnitConversion deliberately breaks 3NF so conversions need no extra joins.
4. Serve: an Express server (server/routes.js) exposes 16 routes. Each one runs a SQL query using CTEs, joins, aggregates, or window functions.
5. Show: a React client (client/) calls the API and renders tables, charts, and a map.

The live demo skips the server. scripts/build_sqlite.py rebuilds the tables from the CSVs as a SQLite file, and the client runs the same route queries (client/src/queries.js) on it with sql.js. GitHub Actions builds and deploys it to GitHub Pages.

Tech stack

Layer	Tools
Data pipeline	Python, pandas, Jupyter, TradingEconomics API
Database	MySQL on AWS RDS
Server	Node.js, Express, mysql
Client	React 18, Vite, React Router, MUI and MUI X Data Grid, Recharts, React Leaflet
Live demo	SQLite via sql.js, GitHub Actions, GitHub Pages

Team

Built by Lisa Garcia, Zung-Ru Lin, Hanling Su, and Irene Tang in Fall 2023. Revamped by Irene Tang in October 2026 with help from Claude Code, an AI coding assistant. The site's About page covers what the team learned and how the revamp was done.

Running it locally

The client runs on its own against an in-browser SQLite database built from the CSVs in data/:

• Build the database with python3 scripts/build_sqlite.py.
• In the client folder, run npm install and npm start, then open http://localhost:5173.
• npm test runs every query against the database.

To run the original Express server instead, you need a MySQL database of your own, loaded from the CSVs in data/. The AWS database used during the course has been shut down.

• Download the code as a .zip file and extract it.
• Create server/config.json with rds_host, rds_port, rds_user, rds_password, rds_db, server_host, and server_port for your database. This file is gitignored.
• Navigate to the server folder using a terminal or command prompt.
• Install packages and start the server with npm install and npm start.
• Navigate to the client folder using a terminal or command prompt.
• Install packages and start the client with npm install and npm start. Set VITE_API_URL to the server's URL, such as http://localhost:8080, so the client calls the server instead of the in-browser database.
• Deprecation warnings can be ignored.