Railway Management System - MK 1
Table of Contents
Overview
Needed another project. So I’m buidling a railway management system database. I will make sure to keep it simple with this revision and I’ll post the SQL scripts in a GitHub repository that I’ll link later. Down the road I hope to develop a front end for its design to make it more user friendly. Its link will also be added to this post.
Scenario
Something that may help this all make sense is an overall scenario.
Requirements
A railway system, which needs to model the following:
- Stations
- Tracks and connecting stations. You can assume for simplicity that only one track exists between any two stations. All the tracks put together to form a graph.
- Trains, with an ID and a name.
- Train schedules recording what time a train passes through each station on its route.
You can assume for simplicity that each train reaches its destination on the same day and that every train runs every day. Also, for simplicity, assume that for each train, for each station on its route, you store.
- Time in.
- Timeout (same as time in if it does not stop).
- A sequence number so the stations in the route of a train can be ordered by sequence number.
Passenger booking consisting of train, date, from-station, to station, coach, seat and passenger name.
Additions
Tables
stations
id PK name string
tracks
id PK name string
trains
id PK name string model FK
train_models
id PK make string model string
schedules
id PK train_id FK start_time datetime end_time datetime
passengers
id PK ticket_no int first_name string last_name string booking_date datetime