Database Management Systems
COCSC05
RAILWAY RESERVATION MANAGEMENT SYSTEM
Submitted by:- Submitted to:-
2020UCO1576 PARTH JAIN Prof. Sushma Nagpal
2020UCO1585 GAURAV BANSAL
2020UCO1605 RAGHAV MANCHANDA
Problem statement
In a railway system there are several passengers travelling through different
trains. A railway system has users, passengers, stations, train status, train
and its route.
Users, that is the assigned railway system workers, have a user id, password
to login to the system and a security question to add an extra layer of certainty
to the authentication process.
If a user books a seat in a train, he is referred to in the passengers database
as now he is a valid passenger in a train.
Trains have a Train id, Train type, Train name, different compartments(1st
class, 2nd class, sleeper etc), Schedule (arriving time and departure time),
number of stops, it’s Capacity, and many passengers. The railway system also
has trains that do not carry passengers but rather are used to carry cargo.
There can be many trains running on 1 route. But each train will have one
specified [Link] train has its arrival time,departure time and the stop
number at which it has to stop.
Routes further consist of stations. Station has its unique station id and station
[Link] starts and ends at a particular station.
Each train has a status of the bookings done in it. Each train should have a
status id to uniquely identify it. Also we need to know the number of available
seats in the train at a moment. In addition to it, we need to store the seats that
are in waiting, and also that are available for further booking. Also each
booking has its booking date which relates the passengers with the status of
the train.
The above mentioned train statuses are related to a specific train uniquely by
their ids.
Passengers on the other hand will have a unique seat number, name,unique
PNR number, age, gender and phone number,reservation status. A passenger
can travel through only one train but a train can carry a large amount of
passengers depending upon its capacity.
RAILWAY RESERVATION MANAGEMENT SYSTEM
(ER MODEL)
RELATIONAL SCHEMA
SQL QUERIES
create table Passenger(
PNR int primary key,
P_name varchar(20),
Class varchar(20),
age int,
Gender varchar(10),
Reserve_status varchar(10),
Seat_no int
);
create table User(
Password varchar(20),
Security_quest varchar(100),
U_id varchar(10) primary key
);
create table train_status(
Status_id varchar(20),
booked_seat int,
Avail_seat int,
Wait_id int,
Dates date,
U_id varchar(20),
PNR int,
Foreign key (U_id) references User(U_id),
Foreign key (PNR) references Passenger(PNR)
);
create table train(
Train_id varchar(20) primary key,
T_name varchar(20),
Train_type varchar(20),
Avail_class varchar(20),
Status_id varchar(20),
Foreign key(Status_id) references train_status(Status_id)
);
Create table station(
Station_id varchar(20) primary key,
Station_name varchar(50)
);
Create table route(
Arr_time time,
Depart_time time,
Stop_no int
);
Create table start(
Train_id varchar(20) primary key,
Station_id varchar(20),
Foreign key(Train_id) references train(Train_id),
Foreign key(Station_id) references station(Station_id)
);
Create table end(
Train_id varchar(20) primary key,
Station_id varchar(20),
Foreign key(Train_id) references train(Train_id),
Foreign key(Station_id) references station(Station_id)
);
Create table consist_of(
Station_id varchar(20),
Foreign key(Station_id) references station(Station_id)
);
insert into passenger
values(1020,'Parth','General',23,'Male','Reserved',11);
insert into passenger values(1021,'Raghav','Business', 19, 'Male', 'Reserved', 3);
insert into passenger values(1023,'Gaurav','Economy', 24, 'Male', 'Reserved',
90);
insert into Passengers values (1022,'Harish','Business', 55, 'Male', 'Waiting', 38);
insert into Passengers values (1024, 'Mitul', 'Economy' ,46,'Male', 'Waiting', 24);
insert into Passengers values (1025,'Priya','Business', 19, 'Female', 'Reserved',
4);
insert into User values ('ahahegwj', 'What is your 1st school name?', 'U03');
insert into User values ('aqhs3bs', 'What is your favorite brand?', 'U01');
insert into User values ('ahshsh23377', 'What is your father’s nickname?',
'U05');
INSERT INTO train_status VALUES ('ST12', 24, 15, 11, '2021-12-09', 'U03',
1021);
INSERT INTO train_status VALUES ('ST15', 12, 29, 4, '2021-12-14', 'U05',
1024);
INSERT INTO train_status VALUES ('ST69', 4, 2, 45, '2021-12-18', 'U03',
1025);
INSERT INTO train_status VALUES ('ST11', 53, 5, 1, '2021-12-19', 'U03',
1022);
INSERT INTO train_status VALUES ('ST90', 120, 31, 54, '2021-12-21', 'U01',
1023);
INSERT INTO train_status VALUES ('ST35', 89, 90, 78, '2021-12-12', 'U01',
1020);
INSERT INTO train VALUES ('T01', 'Rajdhani', 'Express','AC', 'ST11’);
INSERT INTO train VALUES ('T02', 'Shatabdi', 'Express','AC', 'ST15’);
INSERT INTO train VALUES ('T03', 'Kalka', 'Passenger','Non-AC', 'ST35');
INSERT INTO train VALUES ('T04', 'Shivaji', 'Passenger','Sleeper', 'ST90');
INSERT INTO route VALUES ('12:00:00', '12:02:00', 2);
INSERT INTO route VALUES ('6:30:00', '6:35:00', 3);
INSERT INTO route VALUES ('16:00:00', '16:02:00', 1);
INSERT INTO route VALUES ('18:07:00', '12:09:00', 4);
INSERT INTO station VALUES ('Station1', 'Delhi');
INSERT INTO station VALUES ('Station2', 'Bangalore');
INSERT INTO station VALUES ('Station3', 'Amritsar');
INSERT INTO station VALUES ('Station4', 'Noida');
INSERT INTO station VALUES ('Station5', 'Mumbai');
INSERT INTO station VALUES ('Station6', 'Chennai');
insert into start values('T01', 'Station1');
insert into start values('T02', 'Station2');
insert into start values('T03', 'Station3');
insert into start values('T04', 'Station4');
insert into end values('T01', 'Station3');
insert into end values('T02', 'Station4');
insert into end values('T03', 'Station5');
insert into end values('T04', 'Station6');
que1 > Number of tickets booked by each user.
select U_id, count(*)
From train_status
Group by U_id;
ques 2 > print name of passenger whose seat no. is 90.
select P_name
from passenger
where Seat_no=90;
ques3 >calculate the number of males and females passenger
select gender,count(gender)
from Passenger
group by gender;
ques4> find the train id and name which starts from delhi and ends at
amritsar.
select train.Train_id, train.T_name
from train,start,end
where train.Train_id=start.Train_id and train.Train_id=end.Train_id and
start.Station_id=( select Station_id from station where Station_name='Delhi')
and end.Station_id=( select Station_id from station where
Station_name='Amritsar');
ques5 > Passenger with maximum age.
select P_name, age
from passenger
where age=(select max(age) from passenger);
ques6> Find details of passengers whose age is less than 30 and are in
Business class.
Select *
from passenger
where Class=’Business’ and age<=30;
ques 7 >create a view to get arrival time and departure time of the train.
create view train_timings as
select Arr_time, Depart_time
from route;
ques 8 write a trigger to increase age of passenger by 1 when the date is
1st january.
Create trigger age_increase
Before insert
On passenger
For each row
Set [Link]=[Link]+1
Where (DATEPART(mm, curdate()) = 01 AND DATEPART(dd, curdate()) =
01)
And (datepart(yyyy,curdate()))>2021;