CREATE TABLE users (
user_Id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL
);
CREATE TABLE trains (
train_id INT PRIMARY KEY AUTO_INCREMENT,
train_name VARCHAR(100),
source VARCHAR(50),
destination VARCHAR(50),
departure_time DATETIME,
arrival_time DATETIME
);
CREATE TABLE coaches (
coach_id INT PRIMARY KEY AUTO_INCREMENT,
train_id INT,
coach_type VARCHAR(20),
available_seats INT,
FOREIGN KEY (train_id) REFERENCES trains(train_id)
);
CREATE TABLE reservations (
reservation_id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(50),
passenger_name VARCHAR(50),
age INT,
gender VARCHAR(10),
train_id INT,
coach_type VARCHAR(20),
seat_number INT,
departure_city VARCHAR(50),
arrival_city VARCHAR(50),
status VARCHAR(20),
meal_option VARCHAR(20),
ticket_price DECIMAL(10, 2),
FOREIGN KEY (train_id) REFERENCES trains(train_id)
);
INSERT INTO users (username, password) VALUES ('user1', 'password1'), ('user2',
'password2');
INSERT INTO trains (train_name, source, destination, departure_time, arrival_time)
VALUES
('Train A', 'CityA', 'CityB', '2024-12-01 10:00:00', '2024-12-01 14:00:00'),
('Train B', 'CityA', 'CityC', '2024-12-01 15:00:00', '2024-12-01 19:00:00');
INSERT INTO coaches (train_id, coach_type, available_seats)
VALUES
(1, 'Sleeper', 100),
(1, 'AC', 50),
(2, 'Sleeper', 100),
(2, 'AC', 50);
