MetroCore

Metro Operations & Passenger Management System

A production-grade backend system modelled on GMRC Ahmedabad Metro — built to demonstrate deep DBMS design, SQL query writing, and Java OOP architecture.

---

Tech Stack

• Database: PostgreSQL 16
• Language: Java 17
• Connectivity: JDBC (PostgreSQL Driver 42.7.3)
• Schema: 21 tables across 5 domains

---

Domain Architecture

Domain	Tables
Transit	lines, stations, trains, trips, line_stations, stop_times, fare_rules
Passenger	passengers, smart_cards, transactions
Operations	staff, incidents, depots, depot_assignments, trip_assignments
Maintenance	maintenance_logs, spare_parts
Analytics	ridership_stats, revenue_reports, complaints

---

Key Design Decisions

stop_times — junction table with payload (arrival_time, departure_time, sequence). Time belongs to the trip+station pair, not either entity alone.

fare_rules — zone-based pricing (9 rows) instead of station-to-station (225 rows). Stations carry a zone attribute; fare lookup is O(1).

transactions — immutable ledger. No UPDATE ever. Refunds are compensating entries. balance_before and balance_after stored for full audit trail.

revenue_reports — pre-aggregated snapshot, not derivable on demand. Avoids scanning millions of transaction rows per dashboard load.

resolved_at IS NULL — open incident marker. Cleaner than a status column; enables direct NULL filter in queries.

maintenance_logs — single table absorbs both planned and actual maintenance via a status column (planned / in_progress / completed).

---

Java OOP Layer

4 Pillars

• Abstraction — Entity abstract class + PaymentStrategy and IIncidentObserver interfaces
• Encapsulation — SmartCard.balance is private; only debit() and credit() can modify it, enforcing business rules atomically
• Inheritance — 3-level hierarchy: Entity → TransitAsset → Train / Station
• Polymorphism — runtime via Entity list calling overridden display(); compile-time via 3 overloads of FareCalculator.calculate()

Design Patterns

Strategy — Fare calculation plugged into SmartCard at runtime:

Observer — Incident reporting notifies all registered handlers:

SQL Queries

#	Technique	Query
1	3-table JOIN	Stations on Blue Line in sequence order
2	Aggregation + HAVING	Stations with >100 entries today
3	LAG() window	Travel time between consecutive stops
4	Subquery	Passengers with balance < minimum fare
5	CTE	Top 3 revenue stations this month
6	Computed columns	Fare matrix with card-type discounts
7	NULL filtering	All open incidents with full details
8	RANK() window	Trains ranked by trip count

---

Running Locally

Prerequisites: PostgreSQL 16, Java 17, JDBC driver in lib/

# Create database
psql -U $(whoami) -p 5433 -d postgres -c "CREATE DATABASE metrocore;"

# Load schema
psql -U $(whoami) -p 5433 -d metrocore -f /tmp/metrocore_ddl.sql

# Load data
psql -U $(whoami) -p 5433 -d metrocore -f /tmp/metrocore_data.sql

# Compile
javac -cp lib/postgresql-42.7.3.jar -d out $(find src -name "*.java")

# Run
java -cp out:lib/postgresql-42.7.3.jar metrocore.Main

---

Project Structure

---

Modelled on GMRC Phase 1 — Blue Line (Vastral Gam ↔ Thaltej Gam) and Red Line (Motera Stadium ↔ APMC Gota)

---

Diagrams

ER Schema Diagram

UML Class Diagram

---

Diagrams

ER Schema Diagram

UML Class Diagram