DWDS(Railway Reservation System) 1

Individual Assignment
Module Code: CE00318-2-DWDS

Railway Reservation System

Level-2

Handout Date: February 1st, 2012 Hand in Date: April 20th, 2012

Submitted to: Submitted by:

Prof. Sulekh Sharma Anju Rawal (PT1084404)

DWDS(Railway Reservation System) 2

Acknowledgement

I have taken efforts to complete this assignment. However, it would not have been possible

without the kind support of my module lecturer. I would like to extend my sincere thanks to

her. My thanks and appreciations also go to my friends in developing the project and people

who have willingly helped me out with their abilities.

Name: Anju Rawal

Intake Code: Pt1084404

DWDS(Railway Reservation System) 3

Contents
Acknowledgement .................................................................................................................................. 2
1. Introduction .................................................................................................................................... 5
2. Proposed Solution ........................................................................................................................... 5
3. Entity-Relationship Diagram ........................................................................................................... 6
3.1 Entities and its Attributes ....................................................................................................... 7
3.2 Relationship and Entities ........................................................................................................ 8
3.3 Mapping of ERD to Relational Model...................................................................................... 8
Step1: Mapping of Regular Entity Type .......................................................................................... 8
Step2: Mapping of Weak Entity Type ............................................................................................. 8
Step3: Mapping of Relationships (1: M) ......................................................................................... 9
Step4: Mapping of Relationships (N: M) ......................................................................................... 9
Step6: Mapping of Ternary Relationships ....................................................................................... 9
4. Normalization.................................................................................................................................. 9
4.1 Initial Database Schema ........................................................................................................ 10
5. Table Design and Structure ........................................................................................................... 12
5.1 tb_1passenger....................................................................................................................... 12
5.2 tb_1Train ............................................................................................................................... 12
5.3 tb_Route ............................................................................................................................... 13
5.4 tb_TrainStation ..................................................................................................................... 13
5.5 tb_UserRegister .................................................................................................................... 13
5.6 tb_StatusTrain ....................................................................................................................... 14
5.7 tb_Station.............................................................................................................................. 14
5.8 tb_2passenger....................................................................................................................... 14
5.9 tb_2Train ............................................................................................................................... 15
5.10 tb_Reservation ...................................................................................................................... 15
5.11 Database Diagram ................................................................................................................. 16
6. DDL Statements ............................................................................................................................ 16
6.1 Create Database .................................................................................................................... 16
6.2 Create Tables ........................................................................................................................ 16
6.2.1 tb_1passenger............................................................................................................... 16
6.2.2 tb_2passenger............................................................................................................... 17
6.2.3 tb_1Train ....................................................................................................................... 17
DWDS(Railway Reservation System) 4

6.2.4 tb_2Train ....................................................................................................................... 17

6.2.5 tb_Reservation .............................................................................................................. 18
6.2.6 tb_Route ....................................................................................................................... 18
6.2.7 tb_Station...................................................................................................................... 18
6.2.8 tb_StationTrain ............................................................................................................. 19
6.2.9 tb_TrainStation ............................................................................................................. 19
6.2.10 tb_UserRegister ............................................................................................................ 19
7. DML Statements ........................................................................................................................... 20
7.1 Search Queries ...................................................................................................................... 20
7.2 Booking Queries .................................................................................................................... 23
7.3 Cancellation Queries ............................................................................................................. 23
7.4 Insert Queries........................................................................................................................ 23
7.4.1 Insert Query 1 ............................................................................................................... 23
7.4.2 Insert Query 2 ............................................................................................................... 23
7.5 Update Queries ..................................................................................................................... 24
7.5.1 Update Query 1 ............................................................................................................. 24
7.5.2 Update Query 2 ............................................................................................................. 24
8. Reports .......................................................................................................................................... 25
8.1 Report1: All Trains................................................................................................................. 25
8.2 Report 2: Passengers............................................................................................................. 25
9. Toolbar .......................................................................................................................................... 26
10. Conclusion ................................................................................................................................. 26
11. References ................................................................................................................................ 26
Book References: .............................................................................................................................. 27
Website References .......................................................................................................................... 27
DWDS(Railway Reservation System) 5

1. Introduction

In our module DWDS (Database and Web Database Systems), we will learn about the
databases, how they are created and used in websites. We will learn about the different
commands and how to generate a report.

Know talking about our assignment part, we have to design and develop a database based
system which is fully web-enabled. System which, we have to make is “ONLINE RAILWAY
RESERVATION SYSTEM” which is an automated computerized system and designed to
replace the current system and to provide the passengers easy to book their reservations
online. The functionalities and modules of the system are organized in such a well manner
that railway reservation processes and railway policies can be operated very smoothly.

The basic functions being performed by the system are reservation and cancellation. These
functions will be handled with the help of following sub functions: -

 Manages information about the trains.

 Manages information about the passenger.
 Manages the details of reservation fees, any concessions etc.
 Management of reserved, waiting, cancelled tickets.

Some features of system are:

 A train master who have access to add and modify the train information.
 Automatically, fare will be calculated as per the distance between two stations, train
seat allocation, etc.
 Reservation enquiry.
 Train schedule to enter train journey details include all the station name, arrival time
and departure time.

2. Proposed Solution
DWDS(Railway Reservation System) 6

Nowadays, lifestyle of every single person is very busy and full of hectic schedule. Nobody
have time to stand in queue for hours and make their reservations. Online reservation system
is the solution for this problem.

Some facilities which system will provide to the passengers:

 Firstly, passenger has to register to book tickets and do other thing.

 Passengers can book their tickets for the train in which seats are available.
 Passengers can cancel the tickets at any time.
 Passengers can view the history details of their systems.

Railway administrator is the other part of this system. Administrator has full access and right
of modification to all the information stored in the database at the server. Some of the
facilities which admin can access:

 Admin can add, modify and delete the train information.

 Admin can modify the details of the registered users.
 Admin manages the details of reservation fees, any concessions etc.

3. Entity-Relationship Diagram
DWDS(Railway Reservation System) 7

Class Name Age Gender SeatNo TicketId Status

UserId

Gender

Passenger
Age
User

Name

password

TotalSeatC pSource pDestination

TotalSeatC
1
3
WaitingSea
ts
AvDate TotalSeatC BookedSe
2 ats

Reservation Date

Status

Train Status

StationId

Has Station

StationNa
me

StopNo

ArrivalDate

Route
tSource tDestination
Departure
Date

Dist

Train

Has

TrainNo AvDays SeatClass1 SeatClass2 SeatClass3 FareClass1 FareClass2 FareClass3

3.1 Entities and its Attributes

Entities Attributes
DWDS(Railway Reservation System) 8

Station stationId, stationName

Train trainId, trainName, availableDays, fareClass1,
fareClass2, fareClass3, seatClass1, seatClass2,
seatClass3.
Passenger ticketId, seatNo, name, age, gender, status,
class,.
Route arivalTime, departureTime, stopNo, distance
Train Status availableDate, totalAvaillSeatC1,
totalAvaillSeatC2, totalAvaillSeatC3, bookedSeat,
waitingSeats
User userId, password, name, age , gender

3.2 Relationship and Entities

Relationship Entities participated

Reservation User, Passenger, TrainStatus
Has Train , TrainStatus
Has Route, Train, Station
tSource Train, Station
tDestination Train, Station
pSource Station, Passenger
pDestination Station, Passenger

3.3 Mapping of ERD to Relational Model

Step1: Mapping of Regular Entity Type
Strong Entity Type Relation
Train Train(trainId, trainName, availableDays,
seatClass1,seatClass2,seatClass3, fareClass1,
fareClass2, fareClass3)
Station Station(stationId, stationName)
User User(userId, password, name, age, gender)
Passenger (ticketId, name, age, gender,status,class, seatno,
sourceId, destinationId)

Step2: Mapping of Weak Entity Type

Weak Entity Type Relation
Route Route (trainNo, stopNo., arrivalTime,
departureTime, distance )
Train Status TrainStatus (TrainNo, availableDate, totalSeatC1,
totalSeatC2, totalSeatC3, bookedSeats,
waitingSeats)
DWDS(Railway Reservation System) 9

Step3: Mapping of Relationships (1: M)

Relation Entity Types Approach Changed Relation
pSource Passenger, Station Foreign Key Approach Passenger (TicketId ,
……..……. SourceId)
pDestination Passenger, Station Foreign Key Approach Passenger (TicketId
…….DestinationId)

tSource Train, Station Foreign Key Approach Train (TrainNo,………

SourceId)

tDestination Train, Station Foreign Key Approach Train (TrainNo,

……..DestinationId)

Has Train, TrainStatus Foreign Key Approach TrainStatus (TrainNo,

AvailableDate , ………..)

Has Train, Route Foreign Key Approach Route (TainNo,

StopNo, ………………..)

Step4: Mapping of Relationships (N: M)

Relation Entity Type Appraoch Changed Relation
Has Route, Station Cross Referencing TrainStation(TrainNo,
StationId, StopNo)

Step6: Mapping of Ternary Relationships

Relation Entity Type Appraoch Changed Relation
Reservation User, Passenger, Train Cross Referencing Reservation (User id,
Status Ticket id, Train no.,
AvailDate, Travel
Date, Status)

4. Normalization

Normalization is a database design technique, which begins by examining the relationships

(called functional dependencies) between attributes (Database Systems, 2008). There are
three main normal forms, each with increasing levels of normalization:

 First Normal Form (1NF): Each field in a table contains different information.
 Second Normal Form (2NF): Each field in a table that is not a determiner of the
contents of another field must itself be a function of the other fields in the table.
 Third Normal Form (3NF): No duplicate information is permitted.
DWDS(Railway Reservation System) 10

4.1 Initial Database Schema

UNF:

User:

UserName Password Name Age Address

Station:

StationId StationName

Train:

TrainNo TrainName Type AvailDays SourceId DestinationId

SeatClass1 SeatClass2 SeatClass3 FareClass1 FareClass2 FareClass3

Route:

TrainNo StopNo ArrivalTime DepartureTime DistSource

TrainStation:

TrainNo StationId StopNo

Reservation:

UserName Ticket Id TrainNo AvailDate TravelDate Status

Passenger:

TicketId Name Age Gender SeatNo Class

Status SourceId DestinationId

StatusTrain:

TrainNo AvailDate TotalSeatC1 TotalSeatC2 TotalSeatC3 BookSeatC1

BookSeatC2 BookSeatC3 WaitSeatC1 WaitSeatC2 WaitSeatC3

1NF:

Relation in 1NF:
DWDS(Railway Reservation System) 11

User:

Station:

Route:

TrainStation:

Resevation:

StatusTrain:

Relation that are not in 1NF:

Train:

Passenger:

Conversions of Relations into 1NF that are not in 1NF:

Train:

TrainNo TrainName Type AvailDays SourceId DestinationId

SeatClass1 SeatClass2 SeatClass3 FareClass1 FareClass2 FareClass3

Train1: Into 1NF

TrainNo TrainName Type SourceId DestinationId SeatClass1

SeatClass2 SeatClass3 FareClass1 FareClass2 FareClass3

Train2:

TrainNo AvailSun AvailMon AvailTue AvailWed AvailThr

AvailFri AvailSat

Passenger:

TicketId Name Age Gender SeatNo Class

Status SourceId DestinationId

Into 1NF
Passenger1

TicketId SeatNo NamePasngr AgePasngr GenderPasngr Status

Passenger2
DWDS(Railway Reservation System) 12

TicketId IdSource IdDestination TrainClass

All above relations have full functional dependencies, so they are already in 2NF.

Also, they do not have any transitive relations; hence they are also in 3NF.

5. Table Design and Structure

5.1 tb_1passenger

Fig. 5.1
Constraints: Primary Key: TicketId,

5.2 tb_1Train

Fig. 5.2

Constraints: Primary Key: TrainNo

DWDS(Railway Reservation System) 13

5.3 tb_Route

Fig. 5.3

Constraints: Primary Key: TrainNo+StopNo, Foreign Key-> TrainNo

5.4 tb_TrainStation

Fig. 5.4

Constraints: Primary Key: TrainNo+StationId+StopNo, Foreign Key-> TrainNo,

StationId, StopNo.

5.5 tb_UserRegister

Fig. 5.5
DWDS(Railway Reservation System) 14

Constraints: Primary Key: UserName

5.6 tb_StatusTrain

Fig. 5.6

Constraints: Primary Key: TrainNo+AvailDate, Foreign Key-> TrainNo

5.7 tb_Station

Fig. 5.7

Constraints: Primary Key: StationId

5.8 tb_2passenger

Fig. 5.8
DWDS(Railway Reservation System) 15

Constraints: Primary Key: TicketId+SeatNo, Foreign Key-> TicketId

5.9 tb_2Train

Fig. 5.9

Constraints: Primary Key: TrainNo, Foreign Key-> TrainNo

5.10 tb_Reservation

Fig. 5.9

Constraints: Primary Key: UserName+TravelDate+TrainNo+TicketId+SeatNo,

Foreign Key-> UserName, TicketId, TrainNo, SeatNo.
DWDS(Railway Reservation System) 16

5.11 Database Diagram

6. DDL Statements
6.1 Create Database

create database dbRailway4404

6.2 Create Tables

6.2.1 tb_1passenger

Use dbRailway4404
go

Create table tb_1passenger

DWDS(Railway Reservation System) 17

(TicketId varchar (20) not null PRIMARY KEY,

IdSource varchar(20) not null,
IdDestination varchar(20) not null,
TrainClass varchar (20))

6.2.2 tb_2passenger

use dbRailway4404
go

create table tb_2passenger

( TicketId varchar(20) not null,
SeatNo int not null ,
NamePasngr varchar(50) not null,
AgePasngr int not null,
GenderPasngr varchar(6) not null,
StatusTicket varchar(15) not null,
Primary Key (TicketId, SeatNo)
, Foreign Key (TicketId) REFERENCES tb_1passenger (TicketId))
on update cascade

6.2.3 tb_1Train

use dbRailway4404
go

create table tb_1Train

(TrainNo varchar(10) not null,
TrainName varchar(50) not null,
IdSource varchar(20) not null,
IdDestination varchar (20) not null,
Class1Seat int not null,
Class2Seat int not null,
Class3Seat int not null,
Class1Fare float not null,
Class2Fare float not null,
Class3Fare float not null,
Primary Key (TrainNo))

6.2.4 tb_2Train

use dbRailway4404
go

create table tb_2Train

(TrainNo varchar(10) not null,
AvailSun varchar(5),
AvailMon varchar(5),
AvailTue varchar (5),
AvailWed varchar (5),
AvailThr varchar (5),
DWDS(Railway Reservation System) 18

AvailFri varchar (5),

AvailSat varchar (5),
Primary Key (TrainNo),
Foreign Key (TrainNo) REFERENCES tb_1Train(TrainNo)
on update cascade on delete cascade
)

6.2.5 tb_Reservation

use dbRailway4404
go

create table tb_Reservation

(UserName varchar (15) not null,
TicketId varchar(20) not null,
TrainNo varchar(10) not null,
TravelDate date not null,
BookingDate date not null,
Status varchar(15) not null,
SeatNo int not null,
Primary Key (UserName, TicketId, TrainNo, TravelDate,SeatNO),
Foreign Key (UserName) REFERENCES tb_UserRegister(UserName)
on update cascade on delete cascade,
Foreign Key (TicketId) REFERENCES tb_1passenger (TicketId)
on update cascade,
Foreign Key (TrainNo) References tb_1Train (TrainNo)
on update cascade on delete cascade,
Foreign Key (SeatNo) REFERENCES tb_2passenger (SeatNo)
on update cascade
)

6.2.6 tb_Route

Use dbRailway4404
go

create table tb_Route

( TrainNo varchar(10) not null,
StopNo int not null,
TimeArrival time(7) not null,
TimeDeparture time(7) not null,
SourceDistance float not null
Primary Key (TrainNo,StopNo),
Foreign Key (TrainNo) References tb_1Train (TrainNo)
on update cascade on delete cascade
)

6.2.7 tb_Station

Use dbRailway4404
go

create table tb_Station

( StationId varchar(10) not null,
DWDS(Railway Reservation System) 19

StationName varchar (20), not null,

Primary Key (StationId)
)

6.2.8 tb_StationTrain

Use dbRailway4404
go

create table tb_StatusTrain

(TrainNo varchar(10) not null,
TotalSeatC1 int not null,
TotalSeatC2 int not null,
TotalSeatC3 int not null,
BookSeatC1 int not null,
BookSeatC2 int not null,
BookSeatC3 int not null,
WaitSeatC1 int not null,
WaitSeatC2 int not null,
WaitSeatC3 int not null,
Primary Key (TrainNo, AvailDate),
Foreign Key (TrainNo) References tb_1Train (TrainNo)
on update cascade on delete cascade
)

6.2.9 tb_TrainStation

Use dbRailway4404
go

create table tb_TrainStation

(TrainNo varchar (10) not null
StationId varchar(10) not null,
StopNo not null,
Primary Key (TrainNo, StationId, StopNo)
Foreign Key (TrainNo) References tb_1Train (TrainNo)
on update cascade on delete cascade
Foreign Key (StationId) References tb_Station (StationId)
on update cascade on delete no action
Foreign Key (StopNo) References tb_Route (StopNo)
)

6.2.10 tb_UserRegister

Use dbRailway4404
go

create table tb_TrainStation

(UserName varchar(15) not null,
Password varchar(20)not null,
Name varchar (50) not null,
Age int not null,
Gender varchar (6) not null,
Birth date not null,
Address varchar(50) not null,
DWDS(Railway Reservation System) 20

Email varchar(40) not null,

Contact varchar(20) not null,
Status varchar (20) not null,
Primary Key (UserName)
)

7. DML Statements
7.1 Search Queries

For updating, deleting the values of tupples, we first search it from the table, if values match
to the searching keyword, then required action take place.

Function for selecting data from database table,

Public Sub bind()
Dim str As String =
ConfigurationManager.ConnectionStrings("train").ConnectionString
Dim con As New SqlConnection(str)
Dim cmd As New SqlCommand("select tb_1Train.*,
tb_2Train.AvailSun, tb_2Train.AvailMon, tb_2Train.AvailTue,
tb_2Train.AvailWed, tb_2Train.AvailThr, tb_2Train.AvailFri,
tb_2Train.AvailSat FROM tb_1Train INNER JOIN tb_2Train ON
tb_1Train.TrainNo = tb_2Train.TrainNo", con)
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
GridView1.DataSource = dr
GridView1.DataBind()
dr.Close()
con.Close()
End Sub

Function for updating data into database table,

Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e
As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles
GridView1.RowUpdating
Dim row As GridViewRow = GridView1.Rows(e.RowIndex)

Dim trainno As String = CStr(row.Cells(0).Text)

Dim trainname As String = CType(row.Cells(1).Controls(0),
TextBox).Text
Dim sourceid As String = CType(row.Cells(2).Controls(0),
TextBox).Text
Dim destinationid As String = CType(row.Cells(3).Controls(0),
TextBox).Text
Dim seatac1 As String = CType(row.Cells(4).Controls(0),
TextBox).Text
Dim seatac2 As String = CType(row.Cells(5).Controls(0),
TextBox).Text
Dim seatac3 As String = CType(row.Cells(6).Controls(0),
TextBox).Text
DWDS(Railway Reservation System) 21

Dim fareac1 As String = CType(row.Cells(7).Controls(0),

TextBox).Text
Dim fareac2 As String = CType(row.Cells(8).Controls(0),
TextBox).Text
Dim fareac3 As String = CType(row.Cells(9).Controls(0),
TextBox).Text
Dim sun As Boolean = CType(row.Cells(10).Controls(0),
TextBox).Text
Dim mon As Boolean = CType(row.Cells(11).Controls(0),
TextBox).Text
Dim tue As Boolean = CType(row.Cells(12).Controls(0),
TextBox).Text
Dim wed As Boolean = CType(row.Cells(13).Controls(0),
TextBox).Text
Dim thu As Boolean = CType(row.Cells(14).Controls(0),
TextBox).Text
Dim fri As Boolean = CType(row.Cells(15).Controls(0),
TextBox).Text
Dim sat As Boolean = CType(row.Cells(16).Controls(0),
TextBox).Text

Dim st As String =
ConfigurationManager.ConnectionStrings("train").ConnectionString

Dim con As New SqlConnection(st)

Dim sql As String = "update a set a.TrainName = '" &
trainname & "', a.IdSource = '" & sourceid & "', a.IdDestination = '"
& destinationid & "', a.Class1Seat = '" & seatac1 & "', a.Class2Seat
= '" & seatac2 & "', a.Class3Seat= '" & seatac3 & "', a.Class1Fare=
'" & fareac1 & "', a.Class2Fare= '" & fareac2 & "', a.Class3Fare= '"
& fareac3 & "' FROM tb_1Train a INNER JOIN tb_2Train b ON a.TrainNo =
'" & trainno & "'"
Dim sql1 As String = "update b set b.AvailSun = '" & sun &
"', b.AvailMon = '" & mon & "', b.AvailTue = '" & tue & "',
b.AvailWed = '" & wed & "', b.AvailThr = '" & thu & "', b.AvailFri=
'" & fri & "', b.AvailSat = '" & sat & "'FROM tb_1Train a INNER JOIN
tb_2Train b on b.TrainNo = '" & trainno & "'"

Dim cmd As New SqlCommand(sql, con)

Dim cmd1 As New SqlCommand(sql1, con)

con.Open()
Dim da As SqlDataReader = cmd.ExecuteReader()
'cmd.ExecuteNonQuery()
da.Close()

Dim dr As SqlDataReader = cmd1.ExecuteReader()

'cmd1.ExecuteNonQuery()
dr.Close()
con.Close()
GridView1.EditIndex = -1
bind()
End Sub

Function for deleting data from database table,

Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e
As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles
GridView1.RowDeleting
DWDS(Railway Reservation System) 22

Dim row As GridViewRow = GridView1.Rows(e.RowIndex)

Dim trainno As String = CStr(row.Cells(0).Text)

Dim trainname As String = CType(row.Cells(1).Controls(0),
TextBox).Text
Dim sourceid As String = CType(row.Cells(2).Controls(0),
TextBox).Text
Dim destinationid As String = CType(row.Cells(3).Controls(0),
TextBox).Text
Dim seatac1 As String = CType(row.Cells(4).Controls(0),
TextBox).Text
Dim seatac2 As String = CType(row.Cells(5).Controls(0),
TextBox).Text
Dim seatac3 As String = CType(row.Cells(6).Controls(0),
TextBox).Text
Dim fareac1 As String = CType(row.Cells(7).Controls(0),
TextBox).Text
Dim fareac2 As String = CType(row.Cells(8).Controls(0),
TextBox).Text
Dim fareac3 As String = CType(row.Cells(9).Controls(0),
TextBox).Text
Dim sun As Boolean = CType(row.Cells(10).Controls(0),
TextBox).Text
Dim mon As Boolean = CType(row.Cells(11).Controls(0),
TextBox).Text
Dim tue As Boolean = CType(row.Cells(12).Controls(0),
TextBox).Text
Dim wed As Boolean = CType(row.Cells(13).Controls(0),
TextBox).Text
Dim thu As Boolean = CType(row.Cells(14).Controls(0),
TextBox).Text
Dim fri As Boolean = CType(row.Cells(15).Controls(0),
TextBox).Text
Dim sat As Boolean = CType(row.Cells(16).Controls(0),
TextBox).Text

Dim st As String =
ConfigurationManager.ConnectionStrings("train").ConnectionString

Dim con As New SqlConnection(st)

Dim sql As String = "delete from tb_1Train.TrainNo = '" &
trainno & "'"
''Dim sql1 As String = "update b set b.AvailSun = '" & sun &
"', b.AvailMon = '" & mon & "', b.AvailTue = '" & tue & "',
b.AvailWed = '" & wed & "', b.AvailThr = '" & thu & "', b.AvailFri=
'" & fri & "', b.AvailSat = '" & sat & "'FROM tb_1Train a INNER JOIN
tb_2Train b on b.TrainNo = '" & trainno & "'"

Dim cmd As New SqlCommand(sql, con)

Dim cmd1 As New SqlCommand(sql1, con)

con.Open()
Dim da As SqlDataReader = cmd.ExecuteReader()
'cmd.ExecuteNonQuery()
da.Close()

'Dim dr As SqlDataReader = cmd1.ExecuteReader()

''cmd1.ExecuteNonQuery()
'dr.Close()
con.Close()
DWDS(Railway Reservation System) 23

GridView1.EditIndex = -1

bind()
End Sub

7.2 Booking Queries

7.3 Cancellation Queries

7.4 Insert Queries

7.4.1 Insert Query 1

Dim str As String =

ConfigurationManager.ConnectionStrings("train").ConnectionString

Dim con As New SqlConnection(Str)

Dim sql1, sql2 As String

sql1 = "insert into tb_1Train values('" & txtTrainNo.Text & "', '" &
txtTName.Text & "', '" & txtSource.Text & "', '" &
txtDestination.Text & "', '" & seat1 & "', '" & seat2 & "', '" &
seat3 & "', '" & fare1 & "', '" & fare2 & "', '" & fare3 & "')"

sql2 = "insert into tb_2Train values('" & txtTrainNo.Text & "', '" &
sunday & "', '" & monday & "', '" & tuesday & "', '" & wednesday &
"','" & thursday & "','" & friday & "','" & saturday & "')"

Dim cmd As New SqlCommand(sql1, con)

Dim cmd1 As New SqlCommand(sql2, con)
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
dr.Close()
Dim dr1 As SqlDataReader = cmd1.ExecuteReader()
dr1.Close()
con.Close()

7.4.2 Insert Query 2

Dim st As String =
ConfigurationManager.ConnectionStrings("train").ConnectionString
Dim con As New SqlConnection(st)
Dim sql1 As String

sql1 = "insert into tb_Route values('" & txttrainNo.Text & "', '" &
txtstopNo.Text & "','" & txtarrival.Text & "','" & txtdepart.Text &
"', '" & txtdist.Text & "') "

Dim cmd As New SqlCommand(sql1, con)

con.Open()
Dim da As SqlDataReader = cmd.ExecuteReader()
da.Close()
DWDS(Railway Reservation System) 24

con.Close()

7.5 Update Queries

7.5.1 Update Query 1

Dim st As String =
ConfigurationManager.ConnectionStrings("train").ConnectionString

Dim con As New SqlConnection(st)

Dim sql As String = "update a set a.TrainName = '" & trainname & "',
a.IdSource = '" & sourceid & "', a.IdDestination = '" & destinationid
& "', a.Class1Seat = '" & seatac1 & "', a.Class2Seat = '" & seatac2 &
"', a.Class3Seat= '" & seatac3 & "', a.Class1Fare= '" & fareac1 & "',
a.Class2Fare= '" & fareac2 & "', a.Class3Fare= '" & fareac3 & "' FROM
tb_1Train a INNER JOIN tb_2Train b ON a.TrainNo = '" & trainno & "'"

Dim cmd As New SqlCommand(sql, con)

con.Open()

Dim da As SqlDataReader = cmd.ExecuteReader()

da.Close()

7.5.2 Update Query 2

Dim st As String =
ConfigurationManager.ConnectionStrings("train").ConnectionString

Dim con As New SqlConnection(st)

Dim sql1 As String = "update b set b.AvailSun = '" & sun & "',
b.AvailMon = '" & mon & "', b.AvailTue = '" & tue & "', b.AvailWed =
'" & wed & "', b.AvailThr = '" & thu & "', b.AvailFri= '" & fri & "',
b.AvailSat = '" & sat & "'FROM tb_1Train a INNER JOIN tb_2Train b on
b.TrainNo = '" & trainno & "'"

Dim cmd1 As New SqlCommand(sql1, con)

con.Open()

Dim dr As SqlDataReader = cmd1.ExecuteReader()

dr.Close()

con.Close()
DWDS(Railway Reservation System) 25

8. Reports

8.1 Report1: All Trains

In this Report, all train data are shown in this report like train no, train name, no days train is
available, from where train starts and reaches. All details are shown in this report. This report
is build with the help of crystal report.

8.2 Report 2: Passengers

In this report, details of all passengers are shown. It will help the admin to maintain the
records of users.
DWDS(Railway Reservation System) 26

9. Toolbar

10. Conclusion

Working on the Project was really a learning experience and we have come a long way in
building our concepts of Computer Engineering. The “Railway Reservation System”
developed is purely based on database (SQL). The overall purpose of this system is to
computerized the whole process and thus prevent the intervening errors.

During the course of this assignment developer has gone through many obstacles which made
her to research and though help in increasing knowledge. After applying all the concepts like
ERD, mapping and normalization techniques now developer is very well clear with all these
concepts and fundamentals which will be going to help in the future.

11. References
DWDS(Railway Reservation System) 27

Book References:

1. Connolly, Thomas and Begg, Carolyn (ed.) (2008) Database Systems, New Delhi:
Dorling Kindersley(India) Pvt. Ltd.

2. Molinaro, Anthony (ed.) (2006) (indain-reprint) SQL Cookbook, New Delhi: Shroff
Publishers & Distributors Pvt. Ltd.

Website References

3. sql server 2008 - How To update Two tables at a time using a single StoreProcedure?
- Stack Overflow. 2013. sql server 2008 - How To update Two tables at a time using a
single StoreProcedure? - Stack Overflow. [ONLINE] Available
at: http://stackoverflow.com/questions/9511899/how-to-update-two-tables-at-a-time-
using-a-single-storeprocedure. [Accessed 3 April 2013].

4. Update Multiple tables at a time . 2013. Update Multiple tables at a time . [ONLINE]
Available at: http://social.msdn.microsoft.com/Forums/en-
US/sqlgetstarted/thread/0335f90f-c9b4-4983-9eb6-39259b6af394/. [Accessed 7 April
2013].