CREATE DATABASE railway_reservation;

USE railway reservation
CREATE TABLE trains (
train_id INT PRIMARY KEY AUTO_INCREMENT,
train_name VARCHAR(255) NOT NULL,
 departure_station VARCHAR(255) NOT NULL,
    arrival station VARCHAR(255) NOT NULL,
    departure time DATETIME NOT NULL,

arrival time DATETIME NOT NULL,
total seats INT NOT NULL
);
CREATE TABLE passengers (
    
passenger_id INT PRIMARY KEY AUTO_INCREMENT,
 first_name VARCHAR(255) NOT NULL,
 last_name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
 phone_number VARCHAR(15) NOT NULL
);
CREATE TABLE reservations (
reservation_id INT PRIMARY KEY AUTO_INCREMENT,
train_id INT,
 passenger_id INT,
reservation_date DATETIME NOT NULL,
FOREIGN KEY (train_id) REFERENCES trains (train_id),
 FOREIGN KEY (passenger_id) REFERENCES passengers (passenger_id)
    CREATE TABLE reservations (

 reservation id INT PRIMARY KEY AUTO INCREMENT,
 train id _INT,
 passenger _id INT,
reservation date DATETINE NOT NULL,

FOREIGN KEY (train_id) REFERENCES trains (train_id),
FOREIGN KEY (passenger_id) REFERENCES passengers(passenger_id)
);
INSERT INTO trains (train_nane, departure station, arrival station, departure_tine, arrival_time, total seats)
('Express 101', 'Station A', 'Station B', '2023-11-23 10:00:00', '2023-11-23 14:00:00, 200),

("Local 202", "Station B', 'Station C, 2023-11-23 15:00:00', '2023-11-23 18:00:00', 150),

('Superfast 303', 'Station C', 'Station D', '2023-11-23 20:00:00", "2023-11-23 23:00:00, 300); I
VALUES
    INSERT INTO passengers (first_name, last name, enail, phone number)
("John", "Doe", "john.doe@example.com", "123-456-7890"), ("Jane", "Smith", "jane.smith@example.com', '987-654-3210"),
 ("Bob", "Johnson', "bob.johnson@example.com", "555-123-4567")

    INSERT INTO reservations (train_id, passenger id, reservation date) VALUES
 (1, 1, 2023-11-23 08:30:00'),
(2, 2, 2023-11-23 14:45:00),
    (3,3, 2023-11-23 19:30:00'
    );