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;
