Railway Reservation system Case study of oracle db by cj date

2014
By SANA MEMON
RAILWAY RESERVATION SYSTEM

The railway reservation system
2 | P a g e
Case Study The Railway Reservation System
Database Design by Sana Memon
Term 2nd Semester 4rth
Session August to December
Computer Department
Government Girls Degree College Sukkur

3 | P a g e
Table of Contents
Acknowledgement 04
Preface 05
 Step1 problem domain 06
1. Aim 06
2. Description 06
3. List of Assumptions 06
 Step2 Conceptual data base design 07
1. Entities and their Attributes 07
2. Entity Relationship Notations 07
3. Cardinality 08
4. Entity 08
5. Attribute 08
6. Entity type 08
7. Entity Relationship Diagram 09
8. Functional Dependencies 10
9. Normalization 11
a. First NormalForm 11
b. Second NormalForm 11
c. Third Normal Form 12
 Step3 Data modelmapping 12
1. Descriptionof Tables and Procedures 12
a. Trains 12
b. Train_Status 12
c. Passenger 12
d. BookTicket 13
e. Cancel 13
 step4 Physical database design 13
1. Relational database schema for case study 13
2. Screen Shots 13
 step5 The set of Queries in text and solution in Oracle 18
1.DDL Statements 18
2.DML Statements 19

4 | P a g e
Acknowledgement
I am thankful to almighty ALLAH that by HIS grace i have successfully
completed my project.
I have come to know very things while completing this project and
I am also thankful to our respected teacher Mam Naila Wahab without her guidance our
project would have been incomplete and imperfect.
I have tried my best to fulfil all the requirements given by the teacher to
make the project more comprehensive.
This is my 4rth semester project. I have tried my best & worked really hard for a better
outlook to come. This project has been developed in Oracle db and Ms access . I have
been assigned to make a Software based on The Railway Reservation System as a
project.
I really believe that this project will provide all the necessary information which are
required by Teacher.
Regards,
Sana Memon

5 | P a g e
Preface
This report shows the case study of Railway Reservation System. This System is basically
concerned with the reservation of railway tickets to the passenger. In this i am discussing
that how the reservation is done. What are the rules and regulations and procedure for
reservation?
In this project all the manual work should be converted in to computerized so that the
load of employees should decrease. The database should be stored in computer rather
than in register/manually.
Features:
 Searching of data is easy
 Passenger don’t have to wait for a long time
 Information is accurate
 It is a fast process
 Data efficiency is more
Objectives:
 The objective of railway reservation system is to give structural design to
railway system.
 The project provides functionality and flexibility to railway system such that
One can operate that system easily and efficiently.
 This project also provides a complete set of solutions for some common and
Specific are as of work in the railways.
Scope:
 All the manual work should be converted in to computerized so that the load
of employees should decrease.
 The database should be stored in computer rather than in register/manually.
 Easy to store information rather and fast accessing of information.
 Centralized management reporting and decision support.
 Accurate and timely control program.
 To make it easy for fast processing and modification.
 List of accounts of all customers is available.
 Easy retrieval of accounts.
 To make the railway system is interactive.
 Quick feedback.

6 | P a g e
Db design process consists of following steps
Step 1
Problem domain
Consisting of collecting the relevant data and preparing a statement of the situation.
The Railway Reservation System
Aim: The railway reservation system facilitates the passengers to enquire about the
trains available on the basis of source and destination, booking and cancellation of tick-
ets, enquire about the status of the booked ticket etc.
The aim of case study is to design and develop a database maintaining the records of dif-
ferent trains, train status, and passengers. The record of train includes its number, name,
source, destination, and days on which it is available, whereas record of train status in-
cludes dates for which tickets can be booked, total numberof seats available, and num-
ber of seats already booked. The database has been developed and tested on the Oracle.
Description: Passengers can book their tickets for the train in which seats are avail-
able. For this, Passenger has to provide the desired train number and the date for which
ticket is to be booked. Before booking a ticket for a passenger, the validity of train num-
ber and booking date is checked. Once the train number and booking date are validated,
it is checked whether the seat is available. If yes, the ticket is booked with confirm status
and corresponding ticket ID is generated which is stored along with other details of the
passenger. After all the available tickets are booked, certain numbers of tickets are
booked with waiting status. If waiting lot is also finished, then tickets are not booked
a5nd a message of non‐availability of seats is displayed.
The ticket once booked can be cancelled at any time. For this, the passenger has to pro-
vide the ticket ID (the unique key). The ticket ID is searched and the corresponding rec-
ord is deleted. With this, the first ticket with waiting status also gets confirmed.
List of Assumption: Since the reservation system is very large in reality, it is not
feasible to develop the case study to that extent and prepare documentation at that lev-
el. Therefore, a small sample case study has been created to demonstrate the working of
the reservation system. To implement this sample case study, some assumptions have
been made, which are as follows:
 The number of trains has been restricted to 5.
 The booking is open only for next seven days from the current date.
 Only two categories of tickets can be booked, namely, AC and General.
 The total number of tickets that can be booked in each category (AC and
General) is 10.
 The total number of tickets that can be given the status of waiting is 2.
 The in‐between stoppage stations and their bookings are not considered.

7 | P a g e
Step 2
Conceptual db design
It is the process of conceptual database design.
Entities and their Attribute
 Trains with all attributes
 TrainStatus with all attributes
 Passenger with all attributes
 BookTicket with all attributes
 Cancel with all attributes
Entity Relationship Notations
The E-R diagram is a graphical representationofthe E-R model.The E-R diagram, with the helpof
various symbols.effectivelyrepresentsthe variouscomponentsof the E-R model.The symbols
usedfor the various componentscan be seenas:
Component Symbol Example
Entity
Relationship
Key Attribute
WeakEntity
Attribute
Entity Trains
WeakEntity Train status
Attribute Train_Number
Relationship Booking
Attribute T_ID

8 | P a g e
Cardinality
In database design, the cardinality or fundamental principle of one data table with re-
spect to another is a critical aspect. The relationship of one to the other must be precise
and exact between each other in order to explain how each table links together.
In the relational model, tables can be related as any of "one-to-many" or "many-to-
many." This is said to be the cardinality of a given table in relation to another.
1 M
1 M
M
1
Entity: Something which data is collected, stored and maintained.
Attribute: A characteristic of an entity.
Entity type: A class of entities that have the same set of attributes.
Trains Status Of Trainstatus
Cancel Booking Bookticket
Passenger Booking
Status
of
Trains
Trainstatus

9 | P a g e
Entity Relationship Diagram
An entity/relationship diagram is a pictorial representation of (a) the entities (more ac-
curately, entity types) that are of interest to some enterprise and (b) the relationships
(more accurately, relationship types) that hold among those entities.
1 M
1
1 M
M
Status OfTrains Trainstatus
Mon_Availabl
Genfair
ACfair
tues_Availabl
Destination
Source
Train_Number
Train_Name
TrainDate
Ac_Seats
Gen_seats
B_Acseat
B_Genseat
Booking
Passenger
PAddress
PCategory
PStatus
PName
PAge
Psex
T_ID
Cancel
trainNumber
dateofbooked
category
Ticketid dateofbooked
Bookticket
Cancel
Can-
cel
Login
Name
Password

10 | P a g e
Functional Dependencies
A Functional Dependency is defined as a Constraint between twosets of attributes in a
relation from a database.
Normalization
Initially all database are characterized by large numbers of columns and records. This ap-
proach has certain drawbacks. Consider the Following details train table in the railway
reservation system
First Normal Form
To achieve first normal form for a database, you need to make sure that no table con-
tains multiple columns that you could use to get the same information. Each table should
be organized into rows, and each row should have a primary key that distinguishes it as
unique. The primary key is usually a single column, but sometimes more than one col-
umn can be combined to create a single primary key.
Using the rules of first normal form, there may be redundant information across multiple
rows, but each row will be unique.
Train Table
As per First Normal Form, no two Rows of data must contain repeating group of infor-
mation i.e each set of column must have a unique value, such that multiple columns
cannot be used to fetch the same row. Each table should be organized into rows, and
each row should have a primary key that distinguishes it as unique. The Primary key is
Train_Name
Dateofbooked
pname
TicketID
Train_Number
Page
Paddress
status category
psex
Source
Destination
ACfair
GenfairMon_Available
Tues_Available
dateofbooked category
bookedGenseat
bookedAcseatsAcSeats
No
GenseatsNo
TrainDate
Dateofbooked

11 | P a g e
usually a single column, but sometimes more than one column can be combined tocre-
ate a single primary key. For example consider a table which is not in First normal form.
In First Normal Form, any row must not have a column in which more than one value is
saved, like separated with commas. Rather than that, we must separate such data into
multiple rows.
Usingthe First Normal Form,data redundancy increases,as there will be many columns with
same data inmultiple rows but each row as a whole will be unique.
Second Normal Form
As per the SecondNormal Form there must not be any partial dependencyofany columnon
primary key. It means that for a table that has concatenatedprimary key, each column inthe ta-
ble that is not part of the primary key must dependupon the entire concatenated keyfor its ex-
istence.Ifany columndependsonly on one part of the concatenated key,then the table fails
Secondnormal form.
In this Table the candidate key will be {TicketID,train_Number}column. Now,both the above
tablesqualifiesforSecond Normal Form and will neversufferfrom Update Anomalies.Although
there are a fewcomplexcases in which table in SecondNormal Form suffersUpdate Anomalies,
and to handle those scenarios Third Normal Form isthere.

12 | P a g e
Third Normal form
Third Normal form appliesthat every non-prime attribute of table must be dependentonprima-
ry key. The transitivefunctional dependency shouldbe removedfrom the table. The table must
be inSecond Normal form. For example,considera table with followingfields
In this table TicketID isPrimary key,but dateofbook,pname ,Page,Psex,Paddress,statusandcat-
egory dependsupontrain_Number. The dependencybetweentrain_Number and other fieldsis
calledtransitive dependency.Hence to apply 3NF.
Step 3
Data model mapping
Consisting of converting the entity relationship diagram intoTables in Normal Form.
Description of Tables and Procedures
Tables and procedures that will be created are as follows:
Trains:
This table consists of details about all the available trains. The information
stored in this table includes train number, train name, source, destination, fair for AC
ticket, fair for general ticket, and weekdays on which train is available.
Constraint The train number is unique.
Train_Status:
This table consists of details about the dates on which ticket can be
booked for a train and the status of the availability of tickets. The information stored in
this table includes train number, train date, total number of AC seats, total number of
general seats, number of AC seats booked, and number of generalseats booked.
Constraint Train number should exist in TrainList table.
Passenger:
This table consists of details about the booked tickets. The information
stored in this table includes ticket ID, train number, date for which ticket is booked,
Name, age, sex and address of the passenger, status of reservation (either confirmed or
waiting), and category for which ticket is booked.
Constraint Ticket ID is unique and the train numbershould exist in TrainList table.
BookTicket:
In this procedure, the train number, train date, and category is read from the
Passenger. On the basis of the values provided by the passenger, corresponding record is
retrieved from the Train_Status table. If the desired category is AC, then total number of
AC seats and number of booked AC seats are compared in order to find whether ticket

13 | P a g e
can be booked or not. Similarly, it can be checked for the general category. If ticket can
be booked, and then passenger details are read and stored in the Passenger table.
Cancel:
In this procedure, ticket ID is read from the passenger and corresponding record is
searched in the Passenger table. If the record exists, it is deleted from the table. After
deleting the record (if it is confirmed), first record with waiting status for the same train
and same category are searched from the Passenger table and its status is changed to
Confirm.
Step 4
Physical database design
Consists of executing the tables in an actual database is as follows
Relational database schema for case study
The relational database for Railway Reservation System as follows:
 Trains(Train_Number, Train_Name,Source,Destination,Ac_Cost,
Gen_Cost,Sun_Available,Sat_Available)
 Train_Status (TrainDate,Ac_Seat,Gen_Seat,B_ACSeat,B_Genseat,
BookedDate)
 Passenger (PName,PAddress,PCategory,PStatus,PGender,PAge,
Train_Number,BookedDate)
 BookTicket (train_Number,BookedDate,category)
 Cancel (TicketID,dateofbooked)
Table and Form Design Screen Shoots
New Registration Form

14 | P a g e
Description: Any User coming to this page can register to this form by providing re-
quired detail. The details being provided should not violet the validation rules otherwise
registration will not be allowed.
Passenger table
Passenger Form
Login page- ADMIN, USER

15 | P a g e
Description: Registered users login to the software by using specific name and pass-
word. This will redirect the user to a different view level where the user will be availed
various services like reservation , cancellation of ticket etc.
Login Table
Login Form
Add Train
Description: Admin of this software can add new train to the database which will be
further allowed for reservations. Trains will be assigned a Train_Number which should be
unique and then a train name will also be assigned to the train for its identification.
Trains Table

16 | P a g e
Trains Form
Status of Train
Description: This form can be Use the total available seats. Booked seats and waiting
seats in particularly classes of a train.
Train Status table
Train Status Form

17 | P a g e
Ticket Booking
Description: This form give information of date of booked and the category of Ticket.
Book Tickets table
Book Tickets Form
Cancellation of ticket
Description: By giving Ticket Id and date of booked , Ticket can be cancel by user.
When user Cancel ticket the T_ID status set as cancelled. with the cancellation of one
ticket ,available seats of category in Train status table for specific train should decrease
and booked seats should cancel by 1.
Cancel table

18 | P a g e
Cancel table Form
Relationships
Step 5
The set of Queries in text and solution in Oracle
DDL statements:
Create Table Train
(
Train_Number int NOT NULL,
Train_Name varchar(20),
Source varchar(20),

19 | P a g e
Destination Varchar(20),
Ac_Cost varchar(20),
Gen_Cost varchar(20),
Sun_Available Varchar(20),
Sat_Available Varchar(20),
CONSTRAINT trainsid PRIMARY KEY (Train_Number)
);
Create Table TrainStatus
(
TrainDate Date,
Ac_Seat Number(20),
Gen_Seat Number(20),
BookedDate Date,
TrainNumber number(20),
CONSTRAINT TrainNumber FOREIGN KEY (TrainNumber) REFERENCES
Trains(Train_Number)
);
Create Table Passenger
(
TicketID number(5),
PName varchar(20),
PAddress varchar(20),
PAge number(20),
PCategory varchar(20),
PStatus varchar(20),
PGender varchar(20),
BookedDate date(20),
TrainNumber number(20),
CONSTRAINT psngr PRIMARY KEY (TicketID) ,
);
Create Table BookedTicket
(
train_Number Number Not Null,
BookedDate date,
category varchar(10) ,
);

20 | P a g e
Create Table Cancel
(
TicketID Number(10),
Dateofbooked date,
CONSTRAINT TIDfk FOREIGN KEY(TicketID) REFERENCES
Passenger(TicketID)
);
DML statements:
Train Table
Insert into Train (Train_Number,Train_Name ,Source,Destination ,Ac_Cost ,Gen_Cost ,
Sun_Available ,Sat_Available)
Values (1,’Business Train’,’ Hyderabad’,’Sukkur’,2000,1200,’yes’,’no’);
Values (2,’ sukkur express,’ Sukkur’,’ lahore’,1200,700,’no’,’yes’);
Values (3, ‘Jaffer Express’,’ lahore’,’quetta’,1300, 1500,’yes’,’no’);
Values (4,’ Shalimar Express’,’ quetta’,’KHI’,1500,1200,’yes’,’yes’);
Values (5,’karakarum’,’KHI’,’Sukkur’,1500,1200,’yes’,’yes’);
TrainStatus Table
Insert into TrainStatus (TrainDate,Ac_Seat,Gen-Seat,BookedDATE,Train_Number)
Values (22/12/2014,23,20,1,1,1);
Values (25/12/2014,19,19,2,2,2);
Values (25/12/2014,17,16,2,3,4);

21 | P a g e
Values (26/12/2014,15,13,2,1,5);
Values (14/01/2015,10,9,1,1,3);
Passenger Table
Insert into Passenger ( TicketID, Train_Number,Dateofbook, Psex,PName, PAge,
PAddress , PStatus, Pcategory)
Values(1,3, 18/12/2014,’female’,’Sana’,19,’rohry’,’yes’,General’);
Values(2,4, 27/12/2014,’male’,’Salman’,12,’mangi muhalla’,’yes’,’AC’)
Values(3,5, 28/12/2014,’female’,’hassan’,25,’Qureshi road sukkur’,’yes’,’AC’);
Table BookedTicket
Insert into table BookedTicket (train_Number ,BookedDate ,category)
Values(1 ,27/12/2014,’General’);
Values(2 ,27/12/2014,’General’) ;
Values(3 ,27/12/2014,’AC’);
Cancel Table
Insert into table Cancel (TicketId,Dateofbooked)
Values(1 ,22/12/2014);
Insert into table Cancel (TicketId,Dateofbooked)
Values(1 ,22/12/2014);

22 | P a g e
Delete from Cancel where TicketId=1;
Update Cancel set TickedId=2 where Dateofbooked=22/1/2014;
Select Train_Number from Train where Train_Number <=5;
Truncate table train_status;
Select * from Train;
Select * from TrainStatus;
Select * from Passenger;
Select * from BookedTicket;
Select * from Cancel;

Railway Reservation system Case study of oracle db by cj date

Recommended

Recommended

More Related Content

What's hot

What's hot (20)

Similar to Railway Reservation system Case study of oracle db by cj date

Similar to Railway Reservation system Case study of oracle db by cj date (20)

Recently uploaded

Recently uploaded (20)

Railway Reservation system Case study of oracle db by cj date