Database.txt

-- Create the database
CREATE DATABASE IF NOT EXISTS ticket_booking_db;

-- Use the database
USE ticket_booking_db;

-- Create the Bus table
CREATE TABLE IF NOT EXISTS Bus (
    BusID INT PRIMARY KEY AUTO_INCREMENT,
    NumberPlate VARCHAR(15) UNIQUE,
    NumberOfSeats INT
);

-- Create the Route table
CREATE TABLE IF NOT EXISTS Route (
    RouteID INT PRIMARY KEY AUTO_INCREMENT,
    StartLocation VARCHAR(50),
    EndLocation VARCHAR(50)
);

-- Create the Trip table
CREATE TABLE IF NOT EXISTS Trip (
    TripID INT PRIMARY KEY AUTO_INCREMENT,
    BusID INT,
    RouteID INT,
    StartTime TIMESTAMP,
    EndTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Price DECIMAL(10, 2),
    FOREIGN KEY (BusID) REFERENCES Bus(BusID),
    FOREIGN KEY (RouteID) REFERENCES Route(RouteID)
);

-- Create the DiscountPass table
CREATE TABLE IF NOT EXISTS DiscountPass (
    DiscountPassID INT PRIMARY KEY AUTO_INCREMENT,
    PassName VARCHAR(30) UNIQUE,
    DiscountPercentage FLOAT
);



-- Create the Customer table
CREATE TABLE IF NOT EXISTS Customer (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerName VARCHAR(30),
    CustomerNumber CHAR(10) UNIQUE,
    CustomerEmail VARCHAR(20) UNIQUE,
    CustomerDOB DATE,
    Password VARCHAR(15),
    DiscountPassID INT,
    FOREIGN KEY (DiscountPassID) REFERENCES DiscountPass(DiscountPassID)
);

-- Create the Passenger table
CREATE TABLE IF NOT EXISTS Passenger (
    PassengerID INT PRIMARY KEY AUTO_INCREMENT,
    PassengerName VARCHAR(30),
    PassengerNumber CHAR(10) UNIQUE,
    PassengerEmail VARCHAR(20) UNIQUE,
    PassengerDOB DATE,
    DiscountPassID INT,
    AssociatedWith INT,
    FOREIGN KEY (DiscountPassID) REFERENCES DiscountPass(DiscountPassID),
    FOREIGN KEY (AssociatedWith) REFERENCES Customer(CustomerID)
);

-- Create the Ticket table
CREATE TABLE IF NOT EXISTS Ticket (
    TicketID INT PRIMARY KEY AUTO_INCREMENT,
    TripID INT,
    BookedBy INT,
    BookedFor INT,
    BookTime TIMESTAMP,
    FOREIGN KEY (TripID) REFERENCES Trip(TripID),
    FOREIGN KEY (BookedBy) REFERENCES Customer(CustomerID),
    FOREIGN KEY (BookedFor) REFERENCES Passenger(PassengerID)
);

-- Create table Admin
CREATE TABLE IF NOT EXISTS Admin (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(50) NOT NULL
);
-- Insert base user root
INSERT INTO Admin (username, password) VALUES ('root', '(7f*H7jTek6f7($Z');


-- Views

-- View to get all BusID
CREATE VIEW AllBusIDs AS 
SELECT BusID FROM Bus;

-- View to get all RouteID
CREATE VIEW AllRouteIDs AS 
SELECT RouteID FROM Route;

-- View to get all DiscountPassID
CREATE VIEW AllDiscountPassIDs AS 
SELECT DiscountPassID FROM DiscountPass;

-- Trigger
DELIMITER $$

CREATE TRIGGER after_customer_insert
AFTER INSERT ON Customer
FOR EACH ROW
BEGIN
    INSERT INTO Passenger (PassengerName, PassengerNumber, PassengerEmail, PassengerDOB, DiscountPassID, AssociatedWith)
    VALUES (NEW.CustomerName, NEW.CustomerNumber, NEW.CustomerEmail, NEW.CustomerDOB, NEW.DiscountPassID, NEW.CustomerID);
END$$

DELIMITER ;
-- As all customers are passengers

-- View to get all Bus
CREATE VIEW AllBus AS 
SELECT * FROM Bus;

-- View to get all Route
CREATE VIEW AllRoute AS 
SELECT * FROM Route;

-- View to get all DiscountPass
CREATE VIEW AllDiscountPass AS 
SELECT * FROM DiscountPass;

--  Values to Insert

INSERT INTO DiscountPass (PassName, DiscountPercentage)
VALUES
  ('No Pass', 0.0),
  ('Womens Pass', 10.0),
  ('Veteran Pass', 20.0),
  ('Army Pass', 15.0),
  ('Disabled Pass', 30.0),
  ('Senior Citizen Pass', 15.0),
  ('Student Pass', 25.0),
  ('Family Pass', 12.0),
  ('Group Pass', 8.0),
  ('Loyalty Pass', 5.0),
  ('Employee Pass', 30.0),
  ('Referral Pass', 10.0),
  ('Birthday Pass', 20.0),
  ('Anniversary Pass', 15.0),
  ('Weekend Pass', 8.0),
  ('Weekday Pass', 5.0),
  ('Early Bird Pass', 10.0),
  ('Night Owl Pass', 15.0),
  ('Seasonal Pass', 20.0),
  ('Holiday Pass', 25.0),
  ('Special Occasion Pass', 12.0),
  ('Student Family Pass', 18.0),
  ('Senior Couple Pass', 22.0),
  ('Military Family Pass', 25.0),
  ('Disabled Companion Pass', 35.0),
  ('Frequent Traveler Pass', 10.0);

INSERT INTO Bus (NumberPlate, NumberOfSeats)
VALUES
  ('MH01XY1234', 50),
  ('GJ02AB4567', 45),
  ('DL03CD7890', 60),
  ('UP04EF1234', 55),
  ('RJ05GH5678', 40),
  ('MP06IJ7890', 50),
  ('WB07KL1234', 45),
  ('TN08MN4567', 60),
  ('AP09OP7890', 55),
  ('KA10QR1234', 40),
  ('KL11ST5678', 50),
  ('TS12UV7890', 45),
  ('CG13WX1234', 60),
  ('OR14YZ4567', 55),
  ('JH15AB7890', 40),
  ('JK16CD1234', 50),
  ('HP17EF4567', 45),
  ('PB18GH7890', 60),
  ('CH19IJ1234', 55),
  ('AR20KL4567', 40),
  ('MH22YZ5678', 48),
  ('GJ23AB1234', 52),
  ('DL24CD4567', 45),
  ('UP25EF7890', 50),
  ('RJ26GH1234', 42);

  INSERT INTO Route (StartLocation, EndLocation)
VALUES
  ('Mumbai', 'Delhi'),
  ('Bangalore', 'Chennai'),
  ('Kolkata', 'Hyderabad'),
  ('Pune', 'Goa'),
  ('Jaipur', 'Agra'),
  ('Ahmedabad', 'Surat'),
  ('Lucknow', 'Kanpur'),
  ('Bhopal', 'Indore'),
  ('Nagpur', 'Raipur'),
  ('Patna', 'Ranchi'),
  ('Chandigarh', 'Shimla'),
  ('Guwahati', 'Shillong'),
  ('Kochi', 'Thiruvananthapuram'),
  ('Bhubaneswar', 'Puri'),
  ('Srinagar', 'Leh'),
  ('Darjeeling', 'Gangtok'),
  ('Udaipur', 'Jodhpur'),
  ('Varanasi', 'Allahabad'),
  ('Mysore', 'Bangalore'),
  ('Amritsar', 'Chandigarh'),
  ('Mumbai', 'Pune'),
  ('Bangalore', 'Hyderabad'),
  ('Kolkata', 'Chennai'),
  ('Delhi', 'Jaipur'),
  ('Ahmedabad', 'Indore');

  INSERT INTO Customer (CustomerName, CustomerNumber, CustomerEmail, CustomerDOB, Password, DiscountPassID)
VALUES
    ('Olivia Martinez', '9876543211', 'oliviam@example.com', '1995-11-23', 'password123', 5),
    ('Ethan Brown', '1234567891', 'ethanb@example.com', '1988-04-15', 'password456', 2),
    ('Sophia Lee', '7894561231', 'sophial@example.com', '1992-07-08', 'password789', 1),
    ('Noah Davis', '3456789013', 'noahd@example.com', '1991-02-21', 'password101', 10),
    ('Ava Miller', '5678901235', 'avam@example.com', '1993-09-12', 'password111', 15),
    ('William Johnson', '9012345679', 'williamj@example.com', '1987-03-25', 'password122', 8),
    ('Mia Carter', '2345678902', 'miac@example.com', '1994-06-18', 'password133', 3),
    ('James Smith', '6789012346', 'jamess@example.com', '1989-11-11', 'password144', 12),
    ('Evelyn Anderson', '8901234568', 'evelyn@example.com', '1990-05-04', 'password155', 18),
    ('Benjamin Wilson', '1234567892', 'benjaminw@example.com', '1992-08-20', 'password166', 6),
    ('Charlotte Thompson', '9876543212', 'charlottet@example.com', '1986-02-13', 'password177', 1),
    ('Oliver White', '4567891232', 'oliverw@example.com', '1993-10-07', 'password188', 13),
    ('Abigail Brown', '7894561233', 'abigailb@example.com', '1988-03-28', 'password199', 4),
    ('Lucas Davis', '3456789014', 'lucasd@example.com', '1991-11-15', 'password200', 11),
    ('Emily Miller', '5678901236', 'emilym@example.com', '1994-04-09', 'password211', 16),
    ('Logan Johnson', '9012345670', 'loganj@example.com', '1987-07-22', 'password222', 9),
    ('Grace Carter', '2345678903', 'gracec@example.com', '1995-01-16', 'password233', 7),
    ('Henry Smith', '6789012347', 'henrys@example.com', '1990-09-05', 'password244', 14),
    ('Amelia Anderson', '8901234569', 'amelia@example.com', '1991-02-28', 'password255', 19),
    ('Ethan Wilson', '1234567893', 'ethanw@example.com', '1993-11-13', 'password266', 20);


    INSERT INTO Passenger (PassengerName, PassengerNumber, PassengerEmail, PassengerDOB, DiscountPassID, AssociatedWith) VALUES
('John Doe', '1234667890', 'john.doe@passengers.com', '1990-01-01', 1, 1),
('Jane Smith', '9876548211', 'jane.smith@passengers.com', '1992-02-02', 2, 2),
('Michael Johnson', '1357944681', 'michael.johnson@passengers.com', '1988-03-03', 3, 3),
('Emily Brown', '2468077572', 'emily.brown@passengers.com', '1995-04-04', 3, 4),
('David Lee', '5678901735', 'david.lee@passengers.com', '1985-05-05', 1, 5),
('Olivia Miller', '4371098766', 'olivia.miller@passengers.com', '1998-06-06', 2, 6),
('William Taylor', '7890723457', 'william.taylor@passengers.com', '1982-07-07', 3, 7),
('Sophia Anderson', '3579746812', 'sophia.anderson@passengers.com', '2000-08-08', 1, 8),
('James Wilson', '6105273848', 'james.wilson@passengers.com', '1979-09-09', 2, 9),
('Ava White', '924683973', 'ava.white@passengers.com', '2002-10-10', 3, 10),
('Benjamin Hall', '7472583692', 'benjamin.hall@passengers.com', '1986-11-11', 1, 11),
('Charlotte Clark', '8569014726', 'charlotte.clark@passengers.com', '1999-12-12', 2, 12),
('Thomas Harris', '2585690148', 'thomas.harris@passengers.com', '1983-01-13', 3, 13),
('Amelia Jones', '9014525837', 'amelia.jones@passengers.com', '2001-02-14', 1, 14),
('Ethan Carter', '4725536903', 'ethan.carter@passengers.com', '1978-03-15', 2, 15),
('Grace Miller', '2583590149', 'grace.miller@passengers.com', '2003-04-16', 3, 16),
('Noah Davis', '8369015727', 'noah.davis@passengers.com', '1985-05-17', 1, 17),
('Mia Rodriguez', '1475583694', 'mia.rodriguez@passengers.com', '1997-06-18', 2, 18),
('Oliver Martinez', '5536901473', 'oliver.martinez@passengers.com', '1981-07-19', 3, 19),
('Lily Hernandez', '3650147259', 'lily.hernandez@passengers.com', '2000-08-20', 1, 20);


INSERT INTO Admin (username, password) VALUES
('SystemAdmin', 'securePass123'),
('NetworkMaster', 'adminSecure456'),
('DatabaseGuardian', 'passwordMaster789'),
('AppController', 'secureAppAccess'),
('ServerSupervisor', 'superSecurePass'),
('SecurityOfficer', 'adminSecurity123'),
('DataAnalystLead', 'dataSecure456'),
('CloudEngineer', 'cloudPass789'),
('ITManager', 'itAdmin123'),
('DevOpsLead', 'devOpsSecure456'),
('SystemArchitect', 'architectPass789'),
('NetworkSpecialist', 'networkAdmin123'),
('DatabaseExpert', 'dbExpertSecure456'),
('AppDeveloper', 'appDevPass789'),
('ServerTechnician', 'serverTech123'),
('SecurityAnalyst', 'securityAnalyst456'),
('DataScientist', 'dataScientistPass789'),
('CloudArchitect', 'cloudArch123'),
('ITSupportLead', 'itSupportSecure456');