Railway System Database Design: Conceptual to Logical

This project presents a comprehensive database design for a railway system, addressing a case study involving a private railway company operating between Dartford and London Charing Cross. The design encompasses both conceptual and logical database models, including an Entity-Relationship Diagram (EER) and normalized forms. The assignment details the creation of tables for various entities such as trains, passengers, tickets, departments, and employees, along with the implementation of indexes to optimize query performance. Data population is demonstrated for each table, followed by several SQL queries designed to retrieve specific information from the database, such as finding ticket checkers working on reserved trains, identifying employees by salary, and retrieving passenger details based on destination stations and journey dates. The project concludes with a bibliography of relevant database management and SQL resources.

Running head: RAILWAY SYSTEM DATABASE DESIGN
Railway System Database Design
Name of the Student
Name of the University
Author Note

1RAILWAY SYSTEM DATABASE DESIGN
Table of Contents
Task 1.1: Case Study..................................................................................................................2
Task 1.2: Conceptual Database Design......................................................................................3
Task 2.1: Logical Database Design............................................................................................4
Task 2.2: Table Creation............................................................................................................4
Task 2.3: Index Creation............................................................................................................8
Task 2.4: Data Population........................................................................................................10
Task 2.5: SQL Query...............................................................................................................14
Bibliography.............................................................................................................................19

2RAILWAY SYSTEM DATABASE DESIGN
Task 1.1: Case Study
The CEO of the private company ‘Abstract Pvt. Ltd’ is planning to develop a database
design based on their current railway system.
The organization runs trains from Dartford to London Charing Cross. Two type of
trains run on the particular route node; Local Trains and Reserved Trains. Passengers need to
buy a ticket before riding the train. The ticket will contain details like Ticket ID, Passenger
ID, journey date, source and destination station.
The railway system has various department like driving, ticket checking, cleaning,
maintenance, security and many more. Various employees work in a single department.
Depending upon the satiation an employee like Train Driver may drive either Local Trains or
they might drive reserved trains. Ticket checkers may or may not be present either on
reserved train or on a local train. Checkers can also be present on railway stations.
Passenger journey (tours) can be of quite a few layouts. In UK, tours can be of one
day, three days, it can be even for one whole week. Day tours last only for one day, even
though some day tours may involve early start and delayed ending; pricing for a day tour
(reserved trains) includes the seat/coach charge (fare). Some journeys may contain more than
one destination. Day tours more than four hours will have a tea or coffee break.
Main System Objects will be:
Train – The train will be of two types Local Trains and Reservation type trains.
Passenger – They may book either of the Local Train tickets or of Reservation Tickets before
getting on the train.
Ticket – Tickets will contain Passenger id, Journey date, starting and ending station.

3RAILWAY SYSTEM DATABASE DESIGN
Department – A department consists of many employees and employees can be of various
types
Task 1.2: Conceptual Database Design
Fig: EER Diagram of the Railway System Case Study

4RAILWAY SYSTEM DATABASE DESIGN
Task 2.1: Logical Database Design
Fig: Normalised form of the Conceptual Database Design
Task 2.2: Table Creation
Train Table creation
CREATE TABLE P2545878TRAINS(
TRAIN_ID INT NOT NULL,
TRAIN_NAME VARCHAR (30) NOT NULL,
PRIMARY KEY (TRAIN_ID)
);

5RAILWAY SYSTEM DATABASE DESIGN
Local Train Table creation
CREATE TABLE P2545878LOCAL_TRAIN (
TRAIN_ID INT REFERENCES P2545878TRAINS(TRAIN_ID) NOT NULL,
TRAIN_NAME VARCHAR (30) NOT NULL,
PRIMARY KEY (TRAIN_ID)
);
Reserved Train Table creation
CREATE TABLE P2545878RESERVED_TRAIN (
TRAIN_ID INT REFERENCES P2545878TRAINS(TRAIN_ID) NOT NULL,
TRAIN_NAME VARCHAR (30) NOT NULL,
PRIMARY KEY (TRAIN_ID)
);
Passenger Table creation
CREATE TABLE P2545878PASSENGER(
PASSENGER_ID INT NOT NULL,

6RAILWAY SYSTEM DATABASE DESIGN
PASSENGER_NAME VARCHAR (20) NOT NULL,
PRIMARY KEY (PASSENGER_ID)
);
Ticket Table creation
CREATE TABLE P2545878TICKET (
TICKET_ID INT NOT NULL,
PASSENGER_ID INT references P2545878PASSENGER(PASSENGER_ID),
J_DATE DATE,
SOURCE_STN VARCHAR (25) NOT NULL,
DESTINATION_STN VARCHAR (25) NOT NULL,
PRIMARY KEY (TICKET_ID)
);
Department Table creation
CREATE TABLE P2545878DEPARTMENT (
DEP_ID INT NOT NULL,
DEP_NAME VARCHAR (20) NOT NULL,
PRIMARY KEY (DEP_ID)

7RAILWAY SYSTEM DATABASE DESIGN
);
Employee table creation
CREATE TABLE P2545878EMPLOYEE (
EMP_ID INT NOT NULL,
EMP_NAME VARCHAR (20) NOT NULL,
EMP_SALARY INT NOT NULL,
DEP_ID INT references P2545878DEPARTMENT(DEP_ID),
PRIMARY KEY (EMP_ID)
);
Creation of Train Driver table
CREATE TABLE P2545878TRAIN_DRIVER (
EMP_ID INT REFERENCES P2545878EMPLOYEE(EMP_ID) NOT NULL,
TRAINDRIVER_NAME VARCHAR (20) NOT NULL,
TRAIN_ID INT references P2545878TRAINS(TRAIN_ID),
PRIMARY KEY (EMP_ID)
);

8RAILWAY SYSTEM DATABASE DESIGN
Creation of Ticket Checker table
CREATE TABLE P2545878TICKET_CHECKER (
EMP_ID INT REFERENCES P2545878EMPLOYEE(EMP_ID) NOT NULL,
TC_NAME VARCHAR (20) NOT NULL,
TRAIN_ID INT references P2545878TRAINS(TRAIN_ID),
PRIMARY KEY (EMP_ID)
);
Task 2.3: Index Creation
An index is used to increase the speed of a SQL query run time in other words, for
tables with huge amount of data, an index with a selected category can be created to group
similar data for easy retrieval of the same. Index for the railway-system use case can be taken
on Train_Name in Train table, Employee_Name in Employee table, Passenger_Name in
Passenger table and J_Date (Date) in the Ticket table.
1. Generally, train names are based on source and destination station of a particular
train; between two stations, ‘n’ number of trains can travel, therefore in order retrieve the
details about what train runs between two points, train name can be used as an index to get a
fast and easy retrieval of the Train table. Following syntax can be used to create Train_Name
index in Train table.

9RAILWAY SYSTEM DATABASE DESIGN
CREATE INDEX IDX_TNAME
on P2545878TRAINS (TRAIN_NAME);
2. The whole railway system has ‘n’ number employees working for them. Each station
has separate employees like ticket checker, cleaner, supervisor, accountant (in some cases)
and many more; for this reason Employee_Name from Employee table can be used as an
index for fast retrieval of Employee table based on employee names. Following syntax can be
used to create Employee_Name index in Employee table.
CREATE INDEX IDX_ENAME
on P2545878EMPLOYEE (EMP_NAME);
3. Passengers avail the railway system on a daily basis. A condition may arise, two
passengers with same name (first name), availing same train may confuse their seat numbers;
in order to resolve the issue, concerned authority will need to retrieve the details of
passengers based of that particular name. An index on Passenger_Name of Passenger table
will save a lot of time while retrieving data from the railway system database. Instead of
searching through the whole database, it will just search in that particular index. Following
syntax can be used to create Passenger_Name index in Passenger table.
CREATE INDEX IDX_PNAME

10RAILWAY SYSTEM DATABASE DESIGN
on P2545878PASSENGER (PASSENGER_NAME);
4. Passengers avail the railway system on a daily basis. Certain authority of the railway
system may need to find out a list (or graphical comparison) of passengers that have travelled
by using their service based on a monthly basis; in other words, the authority wants to find
out the busy and lean months of their transportation service. Creating index on J_Date (Date)
of Ticket table will resolve this issue. Following syntax can be used to create J_Date index in
the Ticket table.
CREATE INDEX IDX_JDATE
on P2545878TICKET (J_DATE);
Task 2.4: Data Population
Passenger Table population
Fig: Populated Passenger Table

11RAILWAY SYSTEM DATABASE DESIGN
Ticket Table population
Fig: Populated Ticket Table
Train Table population
For train table, 20 records have been populated because; it is a generalized entity for
the entities LocalTrain and ReservedTrain.
Fig: Populated Train Table