{"aircraft": {"columns": [[0, "Aircraft_ID", "INT", 0, null, 1], [1, "Order_Year", "INT", 0, null, 0], [2, "Manufacturer", "TEXT", 0, null, 0], [3, "Model", "TEXT", 0, null, 0], [4, "Fleet_Series", "TEXT", 0, null, 0], [5, "Powertrain", "TEXT", 0, null, 0], [6, "Fuel_Propulsion", "TEXT", 0, null, 0]], "primary_keys": ["Aircraft_ID"], "foreign_keys": [], "unique_constraints": [[[0, 0, "Aircraft_ID"]]], "check_constraints": []}, "pilot": {"columns": [[0, "Pilot_ID", "INT", 0, null, 1], [1, "Pilot_name", "TEXT", 0, null, 0], [2, "Rank", "INT", 0, null, 0], [3, "Age", "INT", 0, null, 0], [4, "Nationality", "TEXT", 0, null, 0], [5, "Position", "TEXT", 0, null, 0], [6, "Join_Year", "INT", 0, null, 0], [7, "Team", "TEXT", 0, null, 0]], "primary_keys": ["Pilot_ID"], "foreign_keys": [], "unique_constraints": [[[0, 0, "Pilot_ID"]]], "check_constraints": []}, "pilot_record": {"columns": [[0, "Record_ID", "INT", 0, null, 0], [1, "Pilot_ID", "INT", 0, null, 1], [2, "Aircraft_ID", "INT", 0, null, 2], [3, "Date", "TEXT", 0, null, 3]], "primary_keys": ["Pilot_ID", "Aircraft_ID", "Date"], "foreign_keys": [[0, 0, "aircraft", "Aircraft_ID", "Aircraft_ID", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "pilot", "Pilot_ID", "Pilot_ID", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 1, "Pilot_ID"], [1, 2, "Aircraft_ID"], [2, 3, "Date"]]], "check_constraints": []}}
CREATE TABLE aircraft (
    Aircraft_ID INT   PRIMARY KEY,
    Order_Year INT,
    Manufacturer TEXT,
    Model TEXT,
    Fleet_Series TEXT,
    Powertrain TEXT,
    Fuel_Propulsion TEXT,
    UNIQUE (Aircraft_ID)
);

CREATE TABLE pilot (
    Pilot_ID INT   PRIMARY KEY,
    Pilot_name TEXT,
    Rank INT,
    Age INT,
    Nationality TEXT,
    Position TEXT,
    Join_Year INT,
    Team TEXT,
    UNIQUE (Pilot_ID)
);

CREATE TABLE pilot_record (
    Record_ID INT,
    Pilot_ID INT   PRIMARY KEY,
    Aircraft_ID INT   PRIMARY KEY,
    Date TEXT   PRIMARY KEY,
    PRIMARY KEY (Pilot_ID, Aircraft_ID, Date),
    UNIQUE (Pilot_ID, Aircraft_ID, Date),
    FOREIGN KEY (Aircraft_ID) REFERENCES aircraft(Aircraft_ID),
    FOREIGN KEY (Pilot_ID) REFERENCES pilot(Pilot_ID)
);