The Underground Lens - CS348 Final Project

CS348: Introduction to Database Systems Fall 2025 Project (30%).

Members: Joanna Moon, Luna Nguyen, Lydia He, Mathilda Lee.

About

The Underground Lens is a database-driven application that provides real-time insights for riders boarding the New York City Metropolitan Transportation Authority (NYC MTA) transportation network. The database migrates MTA subway data (Trip, Station, Ridership) from Open New York (data.ny.gov) to provide basic (station information, trip times) and complex (congestion, route suggestions) information to users.

This project is a mono-repo of four milestones summarizing the final product.

Version Prerequisites

• Python 3.9+
• MySQL 8.0+ (instructions below)
• mysql-connector-python 8.1.0+
• Node.js 18+ and npm 9+ (for React frontend)

Database Setup + Testing

We will use MySQL on local machines with Python (mysql-connector-python).

1. Install MySQL (8.0+)

MacOS via Homebrew:

    brew install mysql
    brew services start mysql

Windows via MySQL Installer:

• Setup Type: server-only is sufficient.
• Execute all default installation instructions.

Once installed, run mysql --version. Should be 8.0 or higher.

2. Python Setup (3.9+)

Perform the following steps based on your operating system.

Make sure you are in the cs348-project\3-milestone directory.

2. a) Create Virtual Environment

If MacOS / Linux, run in terminal:

  python3 -m venv .venv

If Windows, run in PowerShell:

  python -m venv .venv

2. b) Activate Virtual Environment

If MacOS / Linux, run in terminal:

  source .venv/bin/activate

If Windows, run in PowerShell:

  .venv\Scripts\activate

2. c) Install Dependencies

MacOS / Linux / Windows:

  pip install -r requirements.txt

3. Create Database and Tables

Copy the .default_env file to create your .env file, then update it with your MySQL password.

3. a) Create Database

Prerequisites:

• You have MySQL installed with a root user (or some other admin username).
• You know the password for that user.

Log in to MySQL as root (make sure you are in the cs348-project\3-milestone directory):

  mysql -u root -p

Create the Database:

CREATE DATABASE IF NOT EXISTS transit_db;

To use the (user: rider_user, pass: Rider_pass123 OR user: admin_user, pass: Admin_pass123) combination, create the user and grant permissions by roles:

CREATE USER IF NOT EXISTS 'rider_user'@'localhost' IDENTIFIED BY 'Rider_pass123';
CREATE USER IF NOT EXISTS 'admin_user'@'localhost' IDENTIFIED BY 'Admin_pass123';

SOURCE db/administration.sql;

GRANT rider_role TO 'rider_user'@'localhost';
GRANT admin_role TO 'admin_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

This is recommended, as it matches the .default_env file.

Note: Grant SUPER Privilege for Triggers and Procedures

Why This is Needed:

The application uses MySQL triggers and stored procedures (for R12 Advanced Feature: Data Suggestions). When binary logging is enabled in MySQL (which is common), creating triggers requires either:

• SUPER privilege, OR
• Setting log_bin_trust_function_creators = 1

Important: Each teammate with their own local MySQL installation will need to grant this privilege on their own machine.

While still logged in as root (from step 3.a above), grant the SUPER privilege:

GRANT SUPER ON *.* TO 'cs348_user'@'localhost';
FLUSH PRIVILEGES;

Alternative: If you prefer not to grant SUPER, you can instead set the global variable (requires SUPER or SYSTEM_VARIABLES_ADMIN privilege):

SET GLOBAL log_bin_trust_function_creators = 1;

For more details, see 3-milestone/db/README_privileges.md.

You can now exit MySQL:

EXIT;

3. b) Create Tables, Procedures, Triggers, Views, Indexes, and Populate Automatically

The app.py script will automatically:

1. Create tables (station, hourly_ridership, trip, suggestion)
2. Create stored procedures
3. Create triggers
4. Populate tables from CSV files (stations, hourly ridership, trips)

Make sure you are in the cs348-project/3-milestone directory.

Note: Create/Populate Tables Manually (Admin privilege, R14)

Creating Tables

If MacOS / Linux, run in terminal:

  mysql -u admin_user -p < db/createtables.sql

If Windows, run in PowerShell:

  Get-Content db/createtables.sql | mysql -u admin_user -p

Populating Tables

If MacOS / Linux, run in terminal:

  mysql -u admin_user -p transit_db < db/populatetables.sql

If Windows, run in PowerShell:

  Get-Content db/populatetables.sql | mysql -u admin_user -p transit_db

3. d) Run Application

If MacOS / Linux, run in terminal:

  python3 app.py

If Windows, run in PowerShell or Git Bash:

  python app.py

You should see output confirming each step:

• Tables created!
• Stored procedures created!
• Triggers created!
• CSV data loaded
• Database setup complete!

3. d) Run Queries

If MacOS / Linux, run in terminal:

  mysql -u cs348_user -p -t < queries/test-sample.sql > queries/test-sample.out

If Windows, run in PowerShell:

  Get-Content queries/test-sample.sql | mysql -u cs348_user -p -t

3. e) Test Query Output Against Expected Results

To verify that your query output matches the expected results in test-sample.out:

If MacOS / Linux, run in terminal. If Windows (Git Bash), run in Git Bash:

  mysql -u cs348_user -p -t transit_db < queries/test-sample.sql | diff - queries/test-sample.out

No differences should be displayed. This means the output matches the expected results.

4. Frontend Setup (React + Vite)

Milestone 2 uses a React frontend instead of vanilla JavaScript.

4. a) Install Node.js

Ensure you have Node.js 18+ and npm 9+ installed:

  node --version
  npm --version

If not installed, download from nodejs.org.

4. b) Navigate to Frontend Directory

  cd frontend

4. c) Install Frontend Dependencies

MacOS / Linux / Windows:

  npm install

This will install:

• React and React DOM
• React Router (for navigation)
• Axios (for API calls)
• Recharts (for data visualization)
• Tailwind CSS (for styling)
• Vite (development server and build tool)

5. Starting the Application

You need to run both the backend API and the frontend dev server.

5. a) Start the Backend API

Open a terminal in the 3-milestone directory:

If MacOS / Linux, run in terminal:

  source .venv/bin/activate
  ./run_api.sh

If Windows, run in PowerShell:

  .venv\Scripts\activate
  ./run_api.bat

Or manually:

  python -m flask --app api.rest_endpoints run --port 5000

The API will be running at http://localhost:5000

5. b) Start the Frontend Dev Server

Open a second terminal and navigate to the frontend directory:

  cd 3-milestone/frontend
  npm run dev

The React application will be running at http://localhost:5173

Open http://localhost:5173 in your browser to use the application.

6. Testing the API

To test the REST API endpoints, see the linked README.