/* Day 6 - DartAir schema
Tim Pierson, Dartmouth CS61, Spring 2026
*/

DROP SCHEMA IF EXISTS DartAir;
CREATE SCHEMA DartAir;
USE DartAir;


CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    FullName VARCHAR(100) NOT NULL,
    Salary DECIMAL(10, 2),
    HireDate DATE,
    EmployeeType ENUM('Pilot', 'Mechanic', 'Accountant', 'Other') NOT NULL DEFAULT 'Other'
);

-- Subclass Table: Pilots
CREATE TABLE Pilots (
    EmployeeID INT PRIMARY KEY,
    LicenseType VARCHAR(50),
    FlightHours INT DEFAULT 0,
    MedicalType VARCHAR(50),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) ON DELETE CASCADE
);

-- Subclass Table: Mechanics
CREATE TABLE Mechanics (
    EmployeeID INT PRIMARY KEY,
    CertificationType VARCHAR(50), -- e.g., 'Airframe', 'Powerplant'
    LastSafetyTraining DATE,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) ON DELETE CASCADE
);

-- Subclass Table: Accountants
CREATE TABLE Accountants (
    EmployeeID INT PRIMARY KEY,
    Title VARCHAR(50), -- e.g., 'Payroll', 'Audit'
    CPADate DATE,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) ON DELETE CASCADE
);