TRAINS INFORMATION

This set of sample tables is used to provide information about trains, stations where trains stop and passengers.

The following are the table used in this.

TRAINS table

Contains information about trains.

Name DataType Description Constraints
Train No. numeric(4) Train Number primary key
Name varchar2(20) Train Name Not Null
Froms varchar2(20) Starting station -
Tos varchar2(20) Destination station -
Type char(1) Type of train 'p'- passenger, 'e' -express,'s'-superfast Check
Freq varchar2(6) On which days the train runs. If it is 'D',it means train runs daily. Otherwise numbers realted to weeks on which train runs are placed. For example, 15 means Sunday(1) and Thursday (5) -

TRAINSTATUS

Contains information about berths that are available in various classes.

Name DataType Description Constraints
Train No. numeric(4) Train Number Forign key - references trains.trainno
JDate Date Journey date -
Class char(2) Travelling Class -
TotBerths numeric(4) Total No. of Berths -
AvaBerths numeric(4) Available Berths -

STATIONS

Contains information about at what time trains reach stations and stay for how much time.

Name DataType Description Constraints
Train No. numeric(4) Train Number Foreign key (Trains.trainno),pkey
Station varchar2(20) Station Name -
Haltno numeric(2) Halting No -
Arrtime char(4) Arrival Time -
Depttime char(4) Depacture Time -
Nomin numeric(2) How many mintues the train stays at the station -

PASSENGERS

Contains information about passengers.

Name DataType Description Constraints
PNR no. numeric(5) A unique number to identifiy each passenger Primary key
Train No numeric(4) Train Number Foreign Key(Trains.trainno)
Name varchar2(20) Passenger name -
Age numeric(3) Passenger age -
JDate date Journey Date -
Class char(2) Traveling class -
Coachno char(3) Coach number -
Berthno char(2) Berth Number -
Gender char(1) Gender - M/F -

SQL Commands to create tables

The following sql commands can be used to create tables related to this applicaion. 
REM ******* Drop all tables first *************

DROP TABLE PASSENGERS CASCADE CONSTRAINTS;
DROP TABLE TRAINSTATUS CASCADE CONSTRAINTS;
DROP TABLE STATIONS CASCADE CONSTRAINTS;
DROP TABLE TRAINS CASCADE CONSTRAINTS;

rem  ********* TRAINS ****************

CREATE TABLE TRAINS
(
 TRAINNO NUMBER(4)     CONSTRAINT TRAINS_PK  PRIMARY KEY,
 NAME    VARCHAR2(20)  CONSTRAINT TRAINS_NAME_NN NOT NULL,
 FROMS   VARCHAR2(20),
 TOS     VARCHAR2(20),
 TYPE    CHAR(1)       CONSTRAINT TRAINS_TYPE_CHK  CHECK ( TYPE IN ('P','E','S')),
 FREQ    VARCHAR2(6)
);


rem ********* TRAINSTATUS **************

CREATE TABLE TRAINSTATUS
(
 TRAINNO  NUMBER(4)  CONSTRAINT TRAINSTATUS_TRAINNO_FK REFERENCES TRAINS(TRAINNO),
 JDATE    DATE,
 CLASS  CHAR(2) CONSTRAINT TRAINSTATUS_CLASS_CHK
                      CHECK( CLASS IN   ('A1','A2','A3','CC','S1','S2','S3') ),
 TOTBERTHS NUMBER(4),
 AVABERTHS NUMBER(4),
 CONSTRAINT  TRAINSTAUS_PK PRIMARY KEY(TRAINNO,JDATE,CLASS),
 CONSTRAINT TRAINSTATUS_BERTHS_CHK CHECK ( TOTBERTHS >= AVABERTHS) 
);


rem ********* STATIONS **************  
  
create table STATIONS
(
  TRAINNO  NUMBER(4)  CONSTRAINT STATIONS_TRAINNO_FK  REFERENCES TRAINS(TRAINNO),
  STATION  VARCHAR2(20),
  HALTNO   NUMBER(2),
  ARRTIME  CHAR(4),
  DEPTTIME CHAR(4),
  NOMIN    NUMBER(2),
  CONSTRAINT STATIONS_PK PRIMARY KEY (TRAINNO,HALTNO)
);
 

rem ********* PASSENGERS **************

 
CREATE TABLE PASSENGERS
(
  PNR     NUMBER(5) CONSTRAINT PASSENGERS_PK PRIMARY KEY,
  TRAINNO NUMBER(4) CONSTRAINT PASSENGERS_TRAINNO_FK REFERENCES TRAINS(TRAINNO),
  NAME    VARCHAR2(20),
  AGE     NUMBER(3),
  JDATE   DATE,
  CLASS   CHAR(2),
  BOGNO   CHAR(3),
  BERTHNO NUMBER(2),
  GENDER  CHAR(1) CONSTRAINT PASSENGERS_GENDER CHECK ( GENDER IN ('M','F'))
);

Insert data into sample tables

REM  ********* DELETE ALL ROWS BEFORE INSERTING NEW ROWS ************

DELETE FROM STATIONS;
DELETE FROM TRAINSTATUS;
DELETE FROM PASSENGERS;
DELETE FROM TRAINS;


REM  ****** TRAINS ******

INSERT INTO TRAINS VALUES(7007,'GODAVARI','VSKP','HYD','E','D');
INSERT INTO TRAINS VALUES(287, 'RATNACHAL','VSKP','VJW','S','D');
INSERT INTO TRAINS VALUES(1020,'KONARK','BNSR','MUM','E','D');
INSERT INTO TRAINS VALUES(6003,'HOWRAH MAIL','HWR','CHENNAI','E','D');
INSERT INTO TRAINS VALUES(6004,'CHENNAI MAIL','CHENNAI','HWR','E','D');
INSERT INTO TRAINS VALUES(7488,'TIRUMALA','VSKP','TPT','E','D');
INSERT INTO TRAINS VALUES(8563,'PRASANTHI','VSKP','BANG','S','D');
INSERT INTO TRAINS VALUES(2703,'PALAKNAMA','HWR','SEC','S','D');
INSERT INTO TRAINS VALUES(2704,'PALAKNAMA','SEC','HWR','S','D');



REM ************ STATIONS ***************

INSERT INTO STATIONS  VALUES(7007,'VSKP',1,NULL,'1700',NULL);
INSERT INTO STATIONS  VALUES(7007,'RAJ',2,'2130','2145',15);
INSERT INTO STATIONS  VALUES(7007,'VJW',3,'0130','0145',15);
INSERT INTO STATIONS  VALUES(7007,'KZP',4,'0430','0445',15);
INSERT INTO STATIONS  VALUES(7007,'HYD',5,'0630', NULL,NULL);

INSERT INTO STATIONS  VALUES(7488,'VSKP',1, NULL,'1430',NULL);
INSERT INTO STATIONS  VALUES(7488,'SMKT',2,'1730','1745',15);
INSERT INTO STATIONS  VALUES(7488,'RAJ',3, '1830','1845',15);
INSERT INTO STATIONS  VALUES(7488,'VJW',4, '1230','1245',15);
INSERT INTO STATIONS  VALUES(7488,'GDR',5, '0430','0445',15);
INSERT INTO STATIONS  VALUES(7488,'TPT',6, '0730',NULL,NULL);

INSERT INTO STATIONS  VALUES(2704,'SEC',1,'1630',NULL,NULL);
INSERT INTO STATIONS  VALUES(2704,'VJW',2,'2230','2245',15);
INSERT INTO STATIONS  VALUES(2704,'RAJ',3,'0330','345',15);
INSERT INTO STATIONS  VALUES(2704,'VSKP',4,'0530','0600',30);
INSERT INTO STATIONS  VALUES(2704,'HWR',5,'1330',NULL,NULL);

INSERT INTO STATIONS  VALUES(1020,'BNSR',1,'1630','NULL',NULL);
INSERT INTO STATIONS  VALUES(1020,'VZM',2,'1830','1845',15);
INSERT INTO STATIONS  VALUES(1020,'VSKP',3,'2130','2200',30);
INSERT INTO STATIONS  VALUES(1020,'VJW',4,'0230','0300',30);
INSERT INTO STATIONS  VALUES(1020,'MUM',5,'1130','NULL',NULL);

REM ************ TRAINSTATUS ***************

INSERT INTO TRAINSTATUS  VALUES(7007,'2-APR-2002','S2',500,20);
INSERT INTO TRAINSTATUS  VALUES(7007,'2-APR-2002','S1',50,30);
INSERT INTO TRAINSTATUS  VALUES(7007,'2-APR-2002','S3',30,0);
INSERT INTO TRAINSTATUS  VALUES(6003,'1-APR-2002','A2',60,10);
INSERT INTO TRAINSTATUS  VALUES(7007,'4-APR-2002','S2',500,20);
INSERT INTO TRAINSTATUS  VALUES(7007,'3-APR-2002','S1',50,30);
INSERT INTO TRAINSTATUS  VALUES(7007,'3-APR-2002','A1',30,0);

INSERT INTO TRAINSTATUS  VALUES(6003,'1-APR-2002','S2',60,10);
INSERT INTO TRAINSTATUS  VALUES(6003,'1-APR-2002','S1',60,10);
INSERT INTO TRAINSTATUS  VALUES(6003,'1-APR-2002','A1',60,10);

INSERT INTO TRAINSTATUS  VALUES(7488,'30-MAY-2002','A2',60,10);
INSERT INTO TRAINSTATUS  VALUES(7488,'1-APR-2002','S2',150,90);
INSERT INTO TRAINSTATUS  VALUES(7488,'1-APR-2002','S1',60,10);

INSERT INTO TRAINSTATUS  VALUES(2704,'2-APR-2002','S1',70,30);
INSERT INTO TRAINSTATUS  VALUES(2704,'2-APR-2002','A2',40,20);
INSERT INTO TRAINSTATUS  VALUES(2704,'2-APR-2002','S2',50,20);

INSERT INTO TRAINSTATUS  VALUES(1020,'1-APR-2002','S1',60,30);
INSERT INTO TRAINSTATUS  VALUES(1020,'1-APR-2002','S2',60,30);
INSERT INTO TRAINSTATUS  VALUES(1020,'1-APR-2002','A1',60,30);




REM ************ PASSENGERS ***************

INSERT INTO PASSENGERS VALUES(1,7007,'GEORGE KOCH',57,'1-APR-2002','A1','H1',2,'M');

INSERT INTO PASSENGERS VALUES(2,7488,'KEVIN LONEY',45,'2-APR-2002','A1','H1',12,'M');

INSERT INTO PASSENGERS VALUES(3,1020,'ERIC NAGALER',57,'1-APR-2002','A1','S6',32,'M');

INSERT INTO PASSENGERS VALUES(4,8563,'ROBERT LAFORE',45,'2-APR-2002','S1','S10',20,'M');

INSERT INTO PASSENGERS VALUES(5,6004,'PETER NORTON',50,'30-MAY-2002','S2','S3',18,'M');

INSERT INTO PASSENGERS VALUES(6,2704,'STEVE BOBROWSKI',35,'11-APR-2002','A1','H2',30,'M');

INSERT INTO PASSENGERS VALUES(7,6003,'BALAGURU SWAMY',45,'21-APR-2002','A1','H2',13,'M');

INSERT INTO PASSENGERS VALUES(8,8563,'KANITKAR',45,'10-MAY-2002','A2','H1',23,'M');

INSERT INTO PASSENGERS VALUES(9,1020,'AL STEVENS',50,'19-APR-2002','A1','H2',43,'M');

INSERT INTO PASSENGERS VALUES(10,8563,'LAURA LEMAY',43,'25-APR-2002','S5','S10',32,'M');

INSERT INTO PASSENGERS VALUES(11,7488,'ROGERS CADENHEAD',45,'19-APR-2002','A1','H1',43,'M');

INSERT INTO PASSENGERS VALUES(12,6003,'STEPHEN WALTHER',55,'19-APR-2002','A1','H1',43,'M');

INSERT INTO PASSENGERS VALUES(13,6003,'RAMA VALPURI',40,'12-APR-2002','A2','H1',33,'M');

INSERT INTO PASSENGERS VALUES(14,6003,'MARK GRUBER',45,'19-APR-2002','A1','H1',40,'M');

INSERT INTO PASSENGERS VALUES(15,6003,'JASON HUNTER',45,'11-MAY-2002','A3','H2',53,'M');

INSERT INTO PASSENGERS VALUES(16,6003,'DAN APPLEMAN',40,'10-JUN-2002','A2','H1',41,'M');


COMMIT;