Scribd
Upload
6 views5 pages

code (1)

The document outlines the SQL schema for a train reservation system, including the creation of tables for Train, Customer, Station, and Booked_seat, each with specific constraints and relationships. It also includes a sequence for generating PNR numbers and examples of inserting records into the Booked_seat table. Additionally, there are procedures for calculating and updating ticket prices for customers.

Uploaded by

itsshweta2812
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views5 pages

code (1)

The document outlines the SQL schema for a train reservation system, including the creation of tables for Train, Customer, Station, and Booked_seat, each with specific constraints and relationships. It also includes a sequence for generating PNR numbers and examples of inserting records into the Booked_seat table. Additionally, there are procedures for calculating and updating ticket prices for customers.

Uploaded by

itsshweta2812
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

CREATE TABLE TRAIN(

Train_code number(5) primary key check(length(train_code)=5),

Train_name varchar2(50) unique not null,

source_code varchar2(4) check(length(source_code)<=4),

destination_code varchar2(4) check(length(destination_code)<=4),

source_name varchar2(50) not null,

destination_name varchar2(50) not null,

train_type varchar2(10) check (train_type in('superfast','express','passenger')),

source_start_time date not null,

destination_end_time date not null ,

check( source_start_time<destination_end_time)

);

CREATE TABLE Customer

(
Customer_code NUMBER PRIMARY KEY,

Customer_name VARCHAR(100) NOT NULL,

Customer_age NUMBER(3) CHECK(Customer_age>0 and Customer_age<=120) NOT NULL,

Customer_mob NUMBER(10) NOT NULL CHECK(LENGTH(Customer_mob) >=10),

Customer_email VARCHAR(255) CHECK (Customer_email LIKE '%@%'),

Customer_gender CHAR(1) CHECK(Customer_gender IN ('M','F','O'))

);

CREATE TABLE Station (

Station_Code VARCHAR2(10) PRIMARY KEY,

Station_name VARCHAR(50) NOT NULL,

Train_Code NUMBER(5),

Platform_No NUMBER,

Train_Name VARCHAR2(50),

Train_Arrival DATE,

Train_Departure DATE,
Schedule_arrival DATE,

Train_delay DATE,

CONSTRAINT FK_Station_Train_Code FOREIGN KEY (Train_Code) REFERENCES Train (Train_Code),

--CONSTRAINT FK_Station_Train_Name FOREIGN KEY (Train_Name) REFERENCES Train (Train_Name),

CONSTRAINT CHK_Arrival_Before_Departure CHECK (Train_Arrival < Train_Departure),

CONSTRAINT CHK_Platform_Positive CHECK (Platform_No > 0),

CONSTRAINT CHK_Arrival_Not_Null CHECK (Train_Arrival IS NOT NULL),

CONSTRAINT CHK_Departure_Not_Null CHECK (Train_Departure IS NOT NULL)

);

CREATE TABLE Booked_seat(

Train_code number(5),

Pnr_no NUMBER,

Customer_code number,

Station_code varchar2(10) ,

Seat_no NUMBER NOT NULL check(seat_no between 1 and 140),

Ticket_price FLOAT NOT NULL CHECK (ticket_price>0),

Travel_date DATE NOT NULL,

CONSTRAINT PK_Booked_Seat PRIMARY KEY (Pnr_no),


CONSTRAINT FK_Booked_Seat_Train FOREIGN KEY (Train_code) REFERENCES Train (Train_code),

CONSTRAINT FK_Booked_seat_Customer FOREIGN KEY (Customer_code) REFERENCES Customer


(Customer_code),

CONSTRAINT FK_Booked_seat_Station FOREIGN KEY (Station_code) REFERENCES Station (Station_code)

);

create sequence Pnr_no_sequence

start with 10000

increment by 1

nocache

nocycle;

-- Inserting 5 records into the Booked_seat table using the Pnr_no_sequence

INSERT INTO Booked_seat (Train_code, Pnr_no, Customer_code, Station_code, Seat_no, Ticket_price,


Travel_date)

VALUES

(10001, Pnr_no_sequence.NEXTVAL, 101, 'STA001', 1, 25.50, TO_DATE('2024-02-01', 'YYYY-MM-DD')),

(10002, Pnr_no_sequence.NEXTVAL, 102, 'STA002', 2, 30.00, TO_DATE('2024-02-05', 'YYYY-MM-DD')),

(10003, Pnr_no_sequence.NEXTVAL, 103, 'STA003', 3, 45.75, TO_DATE('2024-02-10', 'YYYY-MM-DD')),

(10004, Pnr_no_sequence.NEXTVAL, 104, 'STA004', 4, 28.80, TO_DATE('2024-02-15', 'YYYY-MM-DD')),

(10005, Pnr_no_sequence.NEXTVAL, 105, 'STA005', 5, 50.25, TO_DATE('2024-02-20', 'YYYY-MM-DD'));

-- Call the CalculateTicketPrice function to get the ticket price for a specific customer

DECLARE
v_customer_id NUMBER := 123; -- Replace with the actual customer ID

v_ticket_price FLOAT;

BEGIN

v_ticket_price := CalculateTicketPrice(v_customer_id);

DBMS_OUTPUT.PUT_LINE('Ticket Price for Customer ' || v_customer_id || ': $' || v_ticket_price);

END;

-- Call the UpdateTicketPrice procedure to update the ticket price in the Booked_seat table

DECLARE

v_customer_id_to_update NUMBER := 123; -- Replace with the actual customer ID

v_pnr_to_update NUMBER := 456; -- Replace with the actual PNR number

BEGIN

UpdateTicketPrice(v_customer_id_to_update, v_pnr_to_update);

END;

You might also like