🚆 Railway Reservation System (MySQL)

A Railway Reservation System database built using MySQL that manages trains, stations, passengers, bookings, and payments. This project demonstrates database design, relationships, SQL queries, and reporting using real-world railway reservation scenarios.

---

📌 Features

• Add and manage railway stations
• Manage train information
• Register passengers
• Book and cancel tickets
• Record payment details
• Track booking history
• Generate revenue reports
• Search train and passenger data
• Execute advanced SQL queries using JOINs and aggregate functions

---

🛠️ Technologies Used

• MySQL
• MySQL Workbench

---

📂 Database Structure

• Stations
• Trains
• Passengers
• Bookings
• Payments

---

🔗 Database Relationships

• One Station → Many Trains
• One Passenger → Many Bookings
• One Train → Many Bookings
• One Booking → One Payment

---

📁 Project Structure

Railway-Reservation-System-MySQL/
│
├── schema.sql
├── insert_data.sql
├── queries.sql
├── README.md
└── screenshots/

---

📸 Entity Relationship Diagram

📸 Project Screenshots

Stations Table

Trains Table

Passenger Booking History

Revenue by Train

Complete Booking Details

📊 SQL Concepts Used

• CREATE DATABASE
• CREATE TABLE
• PRIMARY KEY
• FOREIGN KEY
• AUTO_INCREMENT
• ENUM
• CHECK Constraints
• INSERT
• UPDATE
• DELETE
• INNER JOIN
• LEFT JOIN
• GROUP BY
• HAVING
• ORDER BY
• Aggregate Functions
• Subqueries

---

Sample Queries

Passenger Booking History

SELECT
    p.first_name,
    p.last_name,
    t.train_name,
    b.journey_date
FROM Bookings b
JOIN Passengers p
ON b.passenger_id = p.passenger_id
JOIN Trains t
ON b.train_id = t.train_id;

---

Revenue by Train

SELECT
    t.train_name,
    SUM(pay.amount)
FROM Payments pay
JOIN Bookings b
ON pay.booking_id = b.booking_id
JOIN Trains t
ON b.train_id = t.train_id
GROUP BY t.train_name;

---

🎯 Learning Outcomes

• Relational Database Design
• Data Integrity using Foreign Keys
• Query Optimization
• Complex SQL Joins
• Aggregate Reporting
• Real-world Database Modeling

---

👨‍💻 Author

Debrup Santra

GitHub: https://github.com/DebrupSantra