CREATE TABLE TRAIN (
Train_code NUMBER(5) PRIMARY KEY CHECK (LENGTH(train_code) = 5),
Train_name VARCHAR2(50) 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 TIMESTAMP NOT NULL,
Destination_end_time TIMESTAMP NOT NULL,
CHECK (source_start_time < destination_end_time)
);
CREATE TABLE Station (
Station_Code VARCHAR2(10) PRIMARY KEY,
Station_name VARCHAR(50) NOT NULL,
Train_Code NUMBER(5),
Available_Platform_No NUMBER(1),
Assign_Platform_No NUMBER(1),
Train_Name VARCHAR2(50),
Train_Arrival TIMESTAMP,
Train_Departure TIMESTAMP,
CONSTRAINT FK_Station_Train_Code FOREIGN KEY (Train_Code) REFERENCES Train
(Train_Code),
CONSTRAINT CHK_Arrival_Before_Departure CHECK (Train_Arrival < Train_Departure),
CONSTRAINT CHK_Arrival_Not_Null CHECK (Train_Arrival IS NOT NULL),
CONSTRAINT CHK_Departure_Not_Null CHECK (Train_Departure IS NOT NULL)
);
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 Booked_seat (
Train_code NUMBER(5),
Pnr_no NUMBER(5),
Customer_code NUMBER,
Station_code VARCHAR2(10),
Seat_no NUMBER NOT NULL,
Ticket_price FLOAT NOT NULL CHECK (Ticket_price > 0),
Travel_date TIMESTAMP NOT NULL,
Status VARCHAR2(20) CHECK (Status IN ('Booked', 'Available', 'Cancelled')),
Refund FLOAT,
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;
CREATE SEQUENCE Train_seq
START WITH 10000
INCREMENT BY 1;
INSERT INTO TRAIN (Train_code, Train_name, Source_code, Destination_code,
Source_name, Destination_name, Train_type, Source_start_time, Destination_end_time)
VALUES(Train_seq.NEXTVAL, 'Rajdhani Express', 'NDLS', 'HWH', 'New Delhi', 'Howrah',
'superfast', TIMESTAMP '2024-01-27 01:12:10', TIMESTAMP '2024-01-27 23:55:10');
INSERT INTO TRAIN (Train_code, Train_name, Source_code, Destination_code,
Source_name, Destination_name, Train_type, Source_start_time, Destination_end_time)
VALUES(Train_seq.NEXTVAL, 'Shatabdi Express', 'NDLS', 'JBP', 'New Delhi', 'Jaipur',
'express', TIMESTAMP '2024-01-27 02:38:10', TIMESTAMP '2024-01-27 22:00:10');
INSERT INTO TRAIN (Train_code, Train_name, Source_code, Destination_code,
Source_name, Destination_name, Train_type, Source_start_time, Destination_end_time)
VALUES(Train_seq.NEXTVAL, 'Intercity Express', 'MAS', 'MYS', 'Chennai', 'Mysore',
'passenger', TIMESTAMP '2024-01-27 03:00:10', TIMESTAMP '2024-01-27 16:00:10');
INSERT INTO TRAIN (Train_code, Train_name, Source_code, Destination_code,
Source_name, Destination_name, Train_type, Source_start_time, Destination_end_time)
VALUES(Train_seq.NEXTVAL, 'Duronto Express', 'BCT', 'NDLS', 'Mumbai Central', 'New
Delhi', 'superfast', TIMESTAMP '2024-01-27 05:38:10', TIMESTAMP '2024-01-27
22:00:10');
INSERT INTO Station (Station_Code, Station_name, Train_Code, Available_Platform_No,
Assign_Platform_No,Train_Name, Train_Arrival, Train_Departure)
VALUES('NDLS1234', 'Hazrat Nizamuddin', 10001, 1, 1,'Rajdhani Express', TIMESTAMP
'2022-01-27 01:12:00', TIMESTAMP '2022-01-27 01:30:00');
INSERT INTO Station (Station_Code, Station_name, Train_Code, Available_Platform_No,
Assign_Platform_No, Train_Name, Train_Arrival, Train_Departure)
VALUES('HWH6789', 'Kolkata', NULL, Null, Null ,'Shatabdi Express', TIMESTAMP '2022-
01-27 15:00:00', TIMESTAMP '2022-01-27 15:15:00');
INSERT INTO Station (Station_Code, Station_name, Train_Code, Available_Platform_No,
Assign_Platform_No, Train_Name, Train_Arrival, Train_Departure)
VALUES('MAS1122', 'Chennai',Null , 3, Null,'Intercity Express', TIMESTAMP '2022-01-
27 08:30:00', TIMESTAMP '2022-01-27 08:45:00');
INSERT INTO Station (Station_Code, Station_name, Train_Code, Available_Platform_No,
Assign_Platform_No, Train_Name, Train_Arrival, Train_Departure)
VALUES('BZA4567', 'Vijaywada', NULL, 4, Null,'Duronto Express', TIMESTAMP '2022-01-
27 05:38:10', TIMESTAMP '2022-01-27 06:01:00');
INSERT INTO Customer (Customer_code, Customer_name,
Customer_age,Customer_mob,Customer_email,Customer_gender)
VALUES(789, 'Arnav', 19, 8988567425, 'arnav.123@example.com','M');
INSERT INTO Customer (Customer_code, Customer_name,
Customer_age,Customer_mob,Customer_email,Customer_gender)
VALUES(788, 'Aaariya', 19, 8988566425, 'arnav.823@example.com','M');
INSERT INTO Customer (Customer_code, Customer_name,
Customer_age,Customer_mob,Customer_email,Customer_gender)
VALUES(745, 'Aryan', 19, 8984567425, 'arnav.163@example.com','M');
INSERT INTO Booked_seat (Train_code, Pnr_no, Customer_code, Station_code, Seat_no,
Ticket_price, Travel_date)
VALUES(10001, Pnr_no_sequence.NEXTVAL, 789, 'NDLS1234', 1, 25.50, TO_DATE('2024-02-
01', 'YYYY-MM-DD'));
INSERT INTO Booked_seat (Train_code, Pnr_no, Customer_code, Station_code, Seat_no,
Ticket_price, Travel_date)
VALUES(10002, Pnr_no_sequence.NEXTVAL, 788, 'NDLS1234', 1, 25.50, TO_DATE('2024-02-
01', 'YYYY-MM-DD'));
INSERT INTO Booked_seat (Train_code, Pnr_no, Customer_code, Station_code, Seat_no,
Ticket_price, Travel_date)
VALUES(10003, Pnr_no_sequence.NEXTVAL, 745, 'NDLS1234', 1, 25.50, TO_DATE('2024-02-
01', 'YYYY-MM-DD'));
CREATE INDEX idx_station_platform ON Station (Available_Platform_No);
SELECT * FROM TRAIN;
CREATE OR REPLACE VIEW Train_Platform_Details AS
SELECT
t.Train_Code,
s.Station_Code,
s.Station_name,
s.Train_Name,
s.Available_Platform_No,
s.Assign_Platform_No,
s.Train_Arrival,
s.Train_Departure,
t.Source_code,
t.Destination_code,
t.Source_name,
t.Destination_name,
t.Train_type,
t.Source_start_time,
t.Destination_end_time
FROM
Station s
JOIN
Train t ON s.Train_name = t.Train_name;
CREATE OR REPLACE TRIGGER station_platform_assignment
BEFORE UPDATE ON Station
FOR EACH ROW
--DECLARE
-- v_platform_assigned BOOLEAN;
BEGIN
-- Check if a platform is already assigned
IF :old.Assign_Platform_No IS NOT NULL
THEN
-- v_platform_assigned := TRUE;
-- Print a message indicating that the platform is already assigned
DBMS_OUTPUT.PUT_LINE(' Warning: Platform ' || :new.Assign_Platform_No || '
is already assigned for ' || :NEW.Station_code);
-- ELSE
-- v_platform_assigned := FALSE;
END IF;
END;
/
select * from station;
CREATE OR REPLACE PACKAGE platform_mgmt AS
PROCEDURE assign_platform(train_code IN NUMBER, station_code IN VARCHAR2);
FUNCTION get_train_assign_platform(train_code IN NUMBER, station_code IN
VARCHAR2) RETURN NUMBER;
END platform_mgmt;
/
CREATE OR REPLACE PACKAGE BODY platform_mgmt AS
--*** PROCEDURE ***
-- Assigns an available platform to a train at a given station if it is available
otherwise it shows message
--that platform is not available for given station code ,It also handles
exceptions.
PROCEDURE assign_platform(train_code IN NUMBER, station_code IN VARCHAR2) AS
v_platform_no NUMBER;
train_code1 NUMBER := train_code;
station_code1 VARCHAR2(50) := station_code;
BEGIN
-- Find the available platform for the station
SELECT Available_Platform_No INTO v_platform_no
FROM Station
WHERE Station_Code = station_code1 AND Train_Code IS NULL;
-- Handle specific outcomes
IF v_platform_no IS NOT NULL THEN
UPDATE Station
SET Train_Code = train_code1, Assign_Platform_No = v_platform_no
WHERE Station_Code = station_code1;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Train ' || train_code || ' assigned to platform ' ||
v_platform_no || ' at station ' || station_code);
ELSE
DBMS_OUTPUT.PUT_LINE('No available platforms at ' || station_code);
END IF;
-- Defining exceptions
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No available platforms at ' || station_code);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple available platforms found for station ' ||
station_code || '. Please specify a platform selection criteria.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error assigning platform: ' || SQLERRM);
ROLLBACK; -- Rollback changes if an error occurs
END assign_platform;
-- Function to get the assigned platform number for a given train at a given
station. Returns 0 if no platform is assigned.
FUNCTION get_train_assign_platform(train_code IN NUMBER, station_code IN VARCHAR2)
RETURN NUMBER AS
platform_no NUMBER;
train_code1 NUMBER := train_code;
station_code1 VARCHAR2(50) := station_code;
BEGIN
--Selecting an assigned platform from the station table and return the platform
number.
SELECT Assign_Platform_No INTO platform_no
FROM Station
WHERE Train_Code = train_code1 AND Station_Code = station_code1;
RETURN platform_no;
-- If platform number is not assigned then it returns 0 as return_type is
number.
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0; -- Return 0 when no data is found
END get_train_assign_platform;
END platform_mgmt;
/
declare
platform_no1 number;
begin
platform_mgmt.assign_platform(10003,'MAS1122');
platform_no1:= platform_mgmt.get_train_Assign_platform(10001,'NDLS1234');
if (platform_no1=0)
then
dbms_output.put_line( 'for train '||10001||' platform_no is not assigned for
station_code '||'NDLS1234');
else
dbms_output.put_line( 'for train '||10001||' platform_n0 '||platform_no1||' is
assigned for station_code '||'NDLS1234');
end if;
end;
/
CREATE OR REPLACE TRIGGER check_delay_trigger
BEFORE INSERT OR UPDATE ON Station
FOR EACH ROW
DECLARE
v_arrival_delay_minutes NUMBER;
v_departure_delay_minutes NUMBER;
BEGIN
-- Calculate delay in arrival and departure
v_arrival_delay_minutes := EXTRACT(MINUTE FROM (:NEW.Train_Arrival
- :OLD.Train_Arrival)) +
EXTRACT(HOUR FROM (:NEW.Train_Arrival
- :OLD.Train_Arrival)) * 60;
v_departure_delay_minutes := EXTRACT(MINUTE FROM (:NEW.Train_Departure
- :OLD.Train_Departure)) +
EXTRACT(HOUR FROM (:NEW.Train_Departure
- :OLD.Train_Departure)) * 60;
-- Check if delay exceeds 15 minutes
IF v_arrival_delay_minutes > 15 OR v_departure_delay_minutes > 15 THEN
-- Print a message indicating the delay
DBMS_OUTPUT.PUT_LINE('Warning: Delay detected at station '
|| :NEW.Station_Code ||
' for train ' || :NEW.Train_Code ||
'. Arrival delay: ' || v_arrival_delay_minutes || '
minutes, ' ||
'Departure delay: ' || v_departure_delay_minutes || '
minutes');
END IF;
END;
/
UPDATE Station
SET Train_Arrival = TIMESTAMP '2022-01-27 08:45:00',
Train_Departure = TIMESTAMP '2022-01-27 09:15:00'
WHERE Station_Code = 'MAS1122';
CREATE OR REPLACE PROCEDURE update_departure_time(
p_train_code IN NUMBER,
p_station_code IN VARCHAR2,
p_new_departure_time IN TIMESTAMP,
p_message OUT VARCHAR2
)
IS
v_train_count NUMBER;
BEGIN
-- Check if the station code and train code exist
SELECT COUNT(*)
INTO v_train_count
FROM Station
WHERE Station_Code = p_station_code AND Train_Code = p_train_code;
IF v_train_count = 0 THEN
p_message := 'Error: Train ' || p_train_code || ' not found at station ' ||
p_station_code;
RETURN;
END IF;
-- Update the departure time for the specified train at the given station
UPDATE Station
SET Train_Departure = p_new_departure_time
WHERE Station_Code = p_station_code AND Train_Code = p_train_code;
p_message := 'Departure time updated successfully for Train ' || p_train_code ||
' at station ' || p_station_code;
EXCEPTION
WHEN OTHERS THEN
p_message := 'Error updating departure time: ' || SQLERRM;
ROLLBACK; -- Rollback changes if an error occurs
END;
/
DECLARE
v_message VARCHAR2(100);
BEGIN
-- Call the procedure with specific parameters
update_departure_time(12345, 'NDLS1234', TIMESTAMP '2022-01-27 01:45:00',
v_message);
-- Display the result message
DBMS_OUTPUT.PUT_LINE(v_message);
END;
/
CREATE OR REPLACE FUNCTION get_station_details(p_station_code IN VARCHAR2)
RETURN SYS_REFCURSOR
IS
v_result_cursor SYS_REFCURSOR;
BEGIN
OPEN v_result_cursor FOR
SELECT s.Station_Code,
s.Station_name,
s.Train_Code,
s.Available_Platform_No,
s.Assign_Platform_No,
s.Train_Name,
s.Train_Arrival,
s.Train_Departure
FROM Station s
WHERE s.Station_Code = p_station_code;
IF NOT v_result_cursor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Station not found: ' || p_station_code);
END IF;
RETURN v_result_cursor;
END get_station_details;
/
DECLARE
v_station_cursor SYS_REFCURSOR;
v_station_code VARCHAR2(10) := 'MAS1122'; -- Replace with the desired station
code
station_code VARCHAR2(10); -- Adjust based on your actual column type
station_name VARCHAR2(50); -- Adjust based on your actual column type
train_code NUMBER(5); -- Adjust based on your actual column type
available_platform NUMBER(1); -- Adjust based on your actual column type
assign_platform NUMBER(1); -- Adjust based on your actual column type
train_name VARCHAR2(50); -- Adjust based on your actual column type
train_arrival TIMESTAMP; -- Adjust based on your actual column type
train_departure TIMESTAMP; -- Adjust based on your actual column type
BEGIN
v_station_cursor := get_station_details(v_station_code);
IF v_station_cursor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Station details for ' || v_station_code || ':');
LOOP
FETCH v_station_cursor INTO
station_code, station_name, train_code, available_platform,
assign_platform, train_name, train_arrival, train_departure;
EXIT WHEN v_station_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Train ' || TO_CHAR(train_code) || ' (' || train_name ||
') arriving on Platform ' || TO_CHAR(assign_platform) ||
' at ' || TO_CHAR(train_arrival, 'YYYY-MM-DD
HH24:MI:SS') || ' and departing at ' || TO_CHAR(train_departure, 'YYYY-MM-DD
HH24:MI:SS'));
END LOOP;
CLOSE v_station_cursor;
END IF;
END;
/
CREATE OR REPLACE VIEW Delayed_Stations AS
SELECT
Station_Code,
Train_Code,
Train_Name,
Train_Arrival,
Train_Departure
FROM
Station
WHERE
EXTRACT(MINUTE FROM (Train_Arrival - Train_Arrival)) > 15
OR EXTRACT(MINUTE FROM (Train_Departure - Train_Departure)) > 15;
CREATE VIEW Customer_Details_View
AS SELECT Customer_code, Customer_name, Customer_age, Customer_mob,
Customer_email, Customer_gender
FROM Customer;
CREATE OR REPLACE PROCEDURE Cancel_Booking(p_pnr_no IN NUMBER) AS
BEGIN
DELETE FROM Booked_seat WHERE Pnr_no = p_pnr_no;
COMMIT; -- Commit the transaction to make the changes permanent
DBMS_OUTPUT.PUT_LINE('Booking Cancelled' );
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('PNR number not found: ' || p_pnr_no);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error canceling booking: ' || SQLCODE || ' - ' ||
SQLERRM);
ROLLBACK; -- Rollback changes if an error occurs
END;
/
CREATE OR REPLACE PROCEDURE Update_Customer_Details_Proc(
p_customer_code IN NUMBER,
p_customer_name IN VARCHAR2,
p_customer_age IN NUMBER,
p_customer_mob IN NUMBER,
p_customer_email IN VARCHAR2,
p_customer_gender IN CHAR,
p_result OUT VARCHAR2
) AS
rows_updated NUMBER;
BEGIN
UPDATE Customer
SET Customer_name = p_customer_name,
Customer_age = p_customer_age,
Customer_mob = p_customer_mob,
Customer_email = p_customer_email,
Customer_gender = p_customer_gender
WHERE Customer_code = p_customer_code;
rows_updated := SQL%ROWCOUNT;
COMMIT;
p_result := 'Rows affected: '|| rows_updated;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
p_result := 'Error updating customer details: ' || SQLCODE || ' - ' ||
SQLERRM;
END Update_Customer_Details_Proc;
/
DECLARE
v_result VARCHAR2(200);
BEGIN
Update_Customer_Details_Proc(
p_customer_code => 789,
p_customer_name => 'shweta',
p_customer_age => 25,
p_customer_mob => 9876543210,
p_customer_email => 'updated.email@example.com',
p_customer_gender =>'F',
p_result => v_result
);
DBMS_OUTPUT.PUT_LINE(v_result);
END;
/
BEGIN
Cancel_Booking(10000);
END;
/
CREATE OR REPLACE FUNCTION CalculateTicketPrice(
p_customer_code IN NUMBER
) RETURN FLOAT IS
v_customer_age NUMBER;
v_ticket_price FLOAT;
v_full_ticket_price FLOAT := 500.00; -- Replace with your full ticket price
BEGIN
-- Assuming you have a Customer table with a Customer_age column
SELECT Customer_age INTO v_customer_age
FROM Customer
WHERE Customer_code = p_customer_code;
-- Calculate ticket price based on age
IF v_customer_age < 8 THEN
v_ticket_price := 0; -- Ticket is free for children below 8 years old
ELSIF v_customer_age >= 8 AND v_customer_age <= 60 THEN
v_ticket_price := v_full_ticket_price; -- Full ticket price for adults between
8 and 60 years old
ELSE
v_ticket_price := v_full_ticket_price / 2; -- Half ticket price for seniors
above 60 years old
END IF;
RETURN v_ticket_price;
END CalculateTicketPrice;
/
CREATE OR REPLACE PROCEDURE UpdateTicketPrice(p_customer_id IN NUMBER, p_pnr_no IN
NUMBER) IS
v_new_ticket_price FLOAT;
BEGIN
-- Call the CalculateTicketPrice function to get the updated ticket price
v_new_ticket_price := CalculateTicketPrice(p_customer_id);
-- Update the Booked_seat table with the new ticket price
UPDATE Booked_seat
SET Ticket_price = v_new_ticket_price
WHERE Pnr_no = p_pnr_no;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Ticket price for PNR ' || p_pnr_no || ' updated
successfully.');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Customer with ID ' || p_customer_id || ' not
found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END UpdateTicketPrice;
/
select * from Customer;
select * from Booked_seat;
CREATE OR REPLACE FUNCTION Calculate_Refund(
p_pnr_no IN NUMBER
) RETURN FLOAT AS
v_ticket_price FLOAT;
v_refund_amount FLOAT;
v_travel_date TIMESTAMP; -- Define the travel_date variable
BEGIN
-- Get the ticket price and travel date for the given PNR number
SELECT Ticket_price, Travel_date INTO v_ticket_price, v_travel_date
FROM Booked_seat
WHERE Pnr_no = p_pnr_no;
-- Calculate the refund amount (e.g., 80% refund for cancellations made 48
hours before travel)
-- Replace the cancellation policy logic as per your requirements
-- This example assumes an 80% refund if cancellation is made 48 hours before
travel
-- Adjust the cancellation policy according to your business rules
-- Here, we're assuming travel_date is the date and time of the booked journey
-- You may need to adjust this logic based on your actual data and business
rules
-- For simplicity, this example uses a fixed refund policy
SELECT CASE
WHEN v_travel_date - SYSTIMESTAMP > INTERVAL '2' DAY THEN --
Assuming 48 hours before travel for the refund
v_ticket_price * 0.8 -- 80% refund
ELSE
0 -- No refund for cancellations made less than 48 hours before
travel
END INTO v_refund_amount
FROM dual; -- Use dual for single-row queries in PL/SQL
RETURN v_refund_amount;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0; -- Return 0 if PNR number not found
WHEN OTHERS THEN
RETURN 0; -- Return 0 for any other errors
END;
/
DECLARE
v_refund_amount FLOAT;
BEGIN
v_refund_amount := Calculate_Refund(10000); -- Replace with the actual PNR
number
DBMS_OUTPUT.PUT_LINE('Refund Amount: ' || v_refund_amount);
END;
/
