Back-End

CREATE TABLE TRAINS (
    TRAIN_NUMBER INT NOT NULL,
    TRAIN_NAME NVARCHAR (MAX) NOT NULL,
    FROM_STATION NVARCHAR (MAX) NOT NULL,
    DEPARTURE_TIME NVARCHAR (MAX) NOT NULL,
    TO_STATION  NVARCHAR (MAX) NOT NULL,
    ARRIVAL_TIME NVARCHAR (MAX) NOT NULL,
    PRIMARY KEY (TRAIN_NUMBER)
);

BULK INSERT TRAINS
from 'C:\Users\AKASH KRISHNA\Desktop\trains.txt'
with
(
fieldterminator=',',
rowterminator='\n'
)
ALTER TABLE TRAINS
ADD BASE_FARE INT IDENTITY(500,1) NOT NULL
SELECT * FROM TRAINS


CREATE TABLE DETAILS
(PNR_NO INT NOT NULL,
NAME NVARCHAR (MAX) NOT NULL,
AGE NVARCHAR(MAX) NOT NULL,
GENDER NVARCHAR (MAX) NOT NULL,
BERTH NVARCHAR (MAX) NOT NULL,
NATIONALITY NVARCHAR(MAX) NOT NULL,
CHARGED_AS NVARCHAR(MAX) NOT NULL,
SEAT NVARCHAR(MAX) NOT NULL
);

CREATE TABLE HISTORY (
    PNR_NUM INT IDENTITY(341725600,1) NOT NULL,
    BOOKED_TRAIN NVARCHAR(MAX) NOT NULL,
    JOURNEY_DATE NVARCHAR (MAX) NOT NULL,
	BOOKED_DATE NVARCHAR(MAX) NOT NULL,
	TICKET_STATUS NVARCHAR(MAX) NOT NULL,
	TOTAL_FARE NVARCHAR(MAX) NOT NULL,
NO_PASSENGERS INT NOT NULL,
QUOTA NVARCHAR (MAX) NOT NULL,
CLASS NVARCHAR (MAX) NOT NULL,
REFUNDED_AMOUNT NVARCHAR(MAX),
    PRIMARY KEY (PNR_NUM)
);


CREATE VIEW RECORDS
AS SELECT PNR_NUM, BOOKED_TRAIN, JOURNEY_DATE, BOOKED_DATE, TICKET_STATUS, TOTAL_FARE FROM HISTORY

CREATE VIEW CANCELLED
AS SELECT PNR_NUM, BOOKED_TRAIN, JOURNEY_DATE, BOOKED_DATE, TICKET_STATUS,REFUNDED_AMOUNT FROM HISTORY