exercise2_airline.sql

-- Exercise 2: Airline Database

-- Drop tables if they exist
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS flights;
DROP TABLE IF EXISTS aircrafts;
DROP TABLE IF EXISTS customers;

-- Create tables
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20),
    total_mileage INT
);

CREATE TABLE aircrafts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    total_seats INT NOT NULL
);

CREATE TABLE flights (
    flight_number VARCHAR(10) PRIMARY KEY,
    aircraft_id INT NOT NULL,
    mileage INT NOT NULL,
    FOREIGN KEY (aircraft_id) REFERENCES aircrafts(id)
);

CREATE TABLE bookings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    flight_number VARCHAR(10) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (flight_number) REFERENCES flights(flight_number)
);

-- Optional: Insert sample data
-- INSERT INTO customers (...) VALUES (...);
-- INSERT INTO aircrafts (...) VALUES (...);
-- INSERT INTO flights (...) VALUES (...);
-- INSERT INTO bookings (...) VALUES (...);