DatabaseCreation: console.sql

CREATE TABLE Passenger
(
    PassengerId    BIGSERIAL   NOT NULL,
    FirstName      varchar(30) NOT NULL,
    LastName       varchar(30) NOT NULL,
    PassportNumber varchar(30) NOT NULL UNIQUE,
    Nationality    varchar(20) NOT NULL,
    DateOfBirth    date        NOT NULL,
    Gender         varchar(10) NOT NULL,
    PhoneNumber    bigint UNIQUE,
    Email          varchar(40) UNIQUE,
    SeatNumber     int,
    PRIMARY KEY (PassengerId)
);

CREATE TABLE Airport
(
    AirportId         BIGSERIAL   NOT NULL,
    AirportName       varchar(50) NOT NULL UNIQUE,
    IataCode          varchar(5)  NOT NULL UNIQUE,
    Location          text        NOT NULL,
    ContactEmail      varchar(40) UNIQUE,
    ContactPhone      bigint UNIQUE,
    OperationalStatus varchar(20) NOT NULL,
    CONSTRAINT chk_airport_status CHECK (OperationalStatus IN ('Open', 'Closed', 'Under Maintenance', 'Restricted')),
    PRIMARY KEY (AirportId)
);

CREATE TABLE AircraftType
(
    AircraftTypeId BIGSERIAL   NOT NULL,
    Manifacturer   varchar(50) NOT NULL,
    Model          varchar(30) NOT NULL,
    Category       varchar(30) NOT NULL,
    PRIMARY KEY (AircraftTypeId)
);

CREATE TABLE Aircraft
(
    AircraftId         BIGSERIAL   NOT NULL,
    AircraftModel      varchar(20) NOT NULL,
    Manufacturer       varchar(50) NOT NULL,
    Capacity           int         NOT NULL,
    RegistrationNumber varchar(20) NOT NULL UNIQUE,
    Status             varchar(20) NOT NULL,
    LastInspectionDate date,
    AircraftTypeId     BIGINT,
    CONSTRAINT chk_aircraft_capacity CHECK (Capacity > 0),
    CONSTRAINT chk_aircraft_status CHECK (Status IN ('Active', 'Inactive', 'Under Maintenance', 'Retired', 'Grounded')),
    PRIMARY KEY (AircraftId),
    FOREIGN KEY (AircraftTypeId) REFERENCES AircraftType (AircraftTypeId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Hangar
(
    HangarId       BIGSERIAL   NOT NULL,
    HangarName     varchar(30) NOT NULL UNIQUE,
    AirportId      BIGINT,
    HangarCapacity int,
    HangarType     varchar(50) NOT NULL,
    Status         varchar(20) NOT NULL,
    CONSTRAINT chk_hangar_capacity CHECK (HangarCapacity IS NULL OR HangarCapacity > 0),
    CONSTRAINT chk_hangar_status CHECK (Status IN ('Open', 'Closed', 'Under Maintenance', 'Full')),
    PRIMARY KEY (HangarId),
    FOREIGN KEY (AirportId) REFERENCES Airport (AirportId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Airline
(
    AirlineId BIGSERIAL   NOT NULL,
    Name      varchar(20) NOT NULL UNIQUE,
    Country   varchar(20) NOT NULL,
    Contact   bigint      NOT NULL UNIQUE,
    Email     varchar(30) UNIQUE,
    PRIMARY KEY (AirlineId)
);

CREATE TABLE Flights
(
    FlightId             BIGSERIAL   NOT NULL,
    FlightNumber         int         NOT NULL UNIQUE,
    AircraftId           BIGINT,
    ArrivalTime          timestamp   NOT NULL,
    DepartureTime        timestamp   NOT NULL,
    OriginAirportId      BIGINT,
    DestinationAirportId BIGINT,
    FlightClass          varchar(20) NOT NULL,
    OperatingStatus      varchar(20) NOT NULL,
    AirlineId            BIGINT,
    CONSTRAINT chk_flight_arrival_after_departure CHECK (ArrivalTime > DepartureTime),
    CONSTRAINT chk_flight_class CHECK (FlightClass IN ('Economy', 'Premium Economy', 'Business', 'First Class')),
    CONSTRAINT chk_flight_status CHECK (OperatingStatus IN ('Scheduled', 'Boarding', 'Departed', 'En Route', 'Landed', 'Arrived','Delayed', 'Cancelled', 'Diverted')),
    PRIMARY KEY (FlightId),
    FOREIGN KEY (AircraftId) REFERENCES Aircraft (AircraftId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (OriginAirportId) REFERENCES Airport (AirportId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (DestinationAirportId) REFERENCES Airport (AirportId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (AirlineId) REFERENCES Airline (AirlineId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Gate
(
    GateId     BIGSERIAL   NOT NULL,
    GateCode   int         NOT NULL,
    TerminalId BIGINT      NOT NULL,
    Status     varchar(20) NOT NULL,
    CONSTRAINT chk_gate_status CHECK (Status IN ('Open', 'Closed', 'Boarding', 'Occupied', 'Under Maintenance')),
    UNIQUE (GateCode, TerminalId),
    PRIMARY KEY (GateId)
);

CREATE TABLE Runway
(
    RunwayId     BIGSERIAL   NOT NULL,
    RunwayName   varchar(20) NOT NULL,
    AirportId    BIGINT,
    RunwayLength int,
    RunwayType   varchar(20) NOT NULL,
    Status       varchar(20) NOT NULL,
    CONSTRAINT chk_runway_length CHECK (RunwayLength IS NULL OR RunwayLength > 0),
    CONSTRAINT chk_runway_status CHECK (Status IN ('Open', 'Closed', 'Under Maintenance', 'Restricted', 'Occupied')),
    UNIQUE (RunwayName, AirportId),
    PRIMARY KEY (RunwayId),
    FOREIGN KEY (AirportId) REFERENCES Airport (AirportId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Taxiway
(
    TaxiwayId     BIGSERIAL   NOT NULL,
    TaxiwayName   varchar(10) NOT NULL,
    AirportId     BIGINT,
    TaxiwayLength int         NOT NULL,
    Direction     varchar(20) NOT NULL,
    Status        varchar(20) NOT NULL,
    CONSTRAINT chk_taxiway_length CHECK (TaxiwayLength > 0),
    CONSTRAINT chk_taxiway_status CHECK (Status IN ('Open', 'Closed', 'Restricted', 'Under Maintenance', 'Occupied', 'Available', 'Blocked', 'Inactive')),
    UNIQUE (TaxiwayName, AirportId),
    PRIMARY KEY (TaxiwayId),
    FOREIGN KEY (AirportId) REFERENCES Airport (AirportId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Terminal
(
    TerminalId     BIGSERIAL   NOT NULL,
    TerminalName   varchar(50) NOT NULL,
    TerminalCode   varchar(10) NOT NULL,
    AirportId      BIGINT,
    TerminalStatus varchar(20) NOT NULL,
    Capacity       int,
    CONSTRAINT chk_terminal_capacity CHECK (Capacity IS NULL OR Capacity > 0),
    CONSTRAINT chk_terminal_status CHECK (TerminalStatus IN ('Open', 'Closed', 'Under Maintenance', 'Restricted')),
    UNIQUE (TerminalCode, AirportId),
    PRIMARY KEY (TerminalId),
    FOREIGN KEY (AirportId) REFERENCES Airport (AirportId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE AircraftMaintenance
(
    MaintenanceId   BIGSERIAL   NOT NULL,
    AircraftId      BIGINT,
    MaintenanceType varchar(20) NOT NULL,
    StartDate       timestamp   NOT NULL,
    EndDate         timestamp,
    DurationMinutes int,
    WorkDescription text,
    Status          varchar(20) NOT NULL,
    MaintenanceCost int,
    AirportId       BIGINT,
    CONSTRAINT chk_maintenance_duration CHECK (DurationMinutes IS NULL OR DurationMinutes > 0),
    CONSTRAINT chk_maintenance_cost CHECK (MaintenanceCost IS NULL OR MaintenanceCost >= 0),
    CONSTRAINT chk_maintenance_end_after_start CHECK (EndDate IS NULL OR EndDate > StartDate),
    CONSTRAINT chk_maintenance_status CHECK (Status IN ('Scheduled', 'In Progress', 'Completed', 'Cancelled', 'On Hold', 'Pending')),
    PRIMARY KEY (MaintenanceId),
    FOREIGN KEY (AircraftId) REFERENCES Aircraft (AircraftId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (AirportId) REFERENCES Airport (AirportId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Arrivals
(
    ArrivalId       BIGSERIAL   NOT NULL,
    FlightId        BIGINT,
    GateId          BIGINT,
    RunwayId        BIGINT,
    Status          varchar(20) NOT NULL,
    ArrivalTime     timestamp,
    OriginAirportId BIGINT,
    TaxiwayId       BIGINT,
    PRIMARY KEY (ArrivalId),
    FOREIGN KEY (FlightId) REFERENCES Flights (FlightId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (GateId) REFERENCES Gate (GateId) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (RunwayId) REFERENCES Runway (RunwayId) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (OriginAirportId) REFERENCES Airport (AirportId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (TaxiwayId) REFERENCES Taxiway (TaxiwayId) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Departures
(
    DepartureId          BIGSERIAL   NOT NULL,
    FlightId             BIGINT,
    GateId               BIGINT,
    RunwayId             BIGINT,
    Status               varchar(20) NOT NULL,
    DepartureTime        timestamp,
    DestinationAirportId BIGINT,
    TaxiwayId            BIGINT,
    PRIMARY KEY (DepartureId),
    FOREIGN KEY (FlightId) REFERENCES Flights (FlightId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (GateId) REFERENCES Gate (GateId) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (RunwayId) REFERENCES Runway (RunwayId) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (DestinationAirportId) REFERENCES Airport (AirportId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (TaxiwayId) REFERENCES Taxiway (TaxiwayId) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Luggage
(
    LuggageId   BIGSERIAL   NOT NULL,
    LuggageType varchar(20) NOT NULL,
    Weight      numeric,
    "Size"      varchar(20),
    Status      varchar(20) NOT NULL,
    CheckedAt   timestamp,
    FlightId    BIGINT,
    CONSTRAINT chk_luggage_weight CHECK (Weight IS NULL OR Weight > 0),
    CONSTRAINT chk_luggage_status CHECK (Status IN ('Checked In', 'Loaded', 'In Transit', 'Arrived', 'Claimed', 'Lost', 'Delayed', 'Damaged')),
    CONSTRAINT chk_luggage_type CHECK (LuggageType IN ('Carry-On', 'Checked', 'Oversized', 'Fragile', 'Special')),
    PRIMARY KEY (LuggageId),
    FOREIGN KEY (FlightId) REFERENCES Flights (FlightId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Ticket
(
    TicketId    BIGSERIAL   NOT NULL,
    FlightId    BIGINT,
    SeatNumber  int         NOT NULL,
    Class       varchar(20) NOT NULL,
    BookingDate timestamp   NOT NULL,
    Status      varchar(20) NOT NULL,
    TicketPrice numeric     NOT NULL,
    PassengerId BIGINT,
    LuggageId   BIGINT,
    CONSTRAINT chk_ticket_price CHECK (TicketPrice > 0),
    CONSTRAINT chk_ticket_seat CHECK (SeatNumber > 0),
    CONSTRAINT chk_ticket_class CHECK (Class IN ('Economy', 'Premium Economy', 'Business', 'First Class')),
    CONSTRAINT chk_ticket_status CHECK (Status IN ('Booked', 'Confirmed', 'Cancelled', 'Used', 'Refunded', 'Pending')),
    UNIQUE (FlightId, SeatNumber),
    PRIMARY KEY (TicketId),
    FOREIGN KEY (FlightId) REFERENCES Flights (FlightId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (PassengerId) REFERENCES Passenger (PassengerId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (LuggageId) REFERENCES Luggage (LuggageId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE BoardingPass
(
    PassId       BIGSERIAL   NOT NULL,
    TicketId     BIGINT UNIQUE,
    BoardingTime timestamp   NOT NULL,
    Class        varchar(10) NOT NULL,
    Status       varchar(20) NOT NULL,
    PrintedAt    timestamp,
    GateId       BIGINT,
    LuggageId    BIGINT,
    CONSTRAINT chk_boardingpass_class CHECK (Class IN ('Economy', 'Premium Economy', 'Business', 'First Class')),
    CONSTRAINT chk_boardingpass_status CHECK (Status IN ('Issued', 'Scanned', 'Boarded', 'Cancelled', 'Expired')),
    PRIMARY KEY (PassId),
    FOREIGN KEY (TicketId) REFERENCES Ticket (TicketId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (GateId) REFERENCES Gate (GateId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (LuggageId) REFERENCES Luggage (LuggageId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE SecurityCheck
(
    CheckId     BIGSERIAL   NOT NULL,
    CheckTime   timestamp   NOT NULL,
    CheckType   varchar(20) NOT NULL,
    TerminalId  BIGINT,
    Status      varchar(20) NOT NULL,
    Notes       text,
    PassengerId BIGINT,
    CONSTRAINT chk_security_status CHECK (Status IN ('Passed', 'Failed', 'In Progress')),
    CONSTRAINT chk_security_type CHECK (CheckType IN ('Baggage', 'Passenger', 'Document', 'Full Body', 'Random')),
    PRIMARY KEY (CheckId),
    FOREIGN KEY (TerminalId) REFERENCES Terminal (TerminalId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (PassengerId) REFERENCES Passenger (PassengerId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Employee
(
    EmployeeId  BIGSERIAL   NOT NULL,
    FirstName   varchar(30) NOT NULL,
    LastName    varchar(30) NOT NULL,
    Position    varchar(20) NOT NULL,
    Email       varchar(20) UNIQUE,
    Phone       bigint      NOT NULL UNIQUE,
    HireDate    date        NOT NULL,
    Salary      numeric     NOT NULL,
    DateOfBirth date        NOT NULL,
    Status      varchar(20) NOT NULL,
    QuitDate    date,
    AirportId   BIGINT,
    CONSTRAINT chk_employee_salary CHECK (Salary > 0),
    CONSTRAINT chk_employee_hire_age CHECK (HireDate >= DateOfBirth + INTERVAL '18 years'),
    CONSTRAINT chk_employee_quit_after_hire CHECK (QuitDate IS NULL OR QuitDate > HireDate),
    CONSTRAINT chk_employee_status CHECK (Status IN ('Active', 'Inactive', 'Paid leave', 'Unpaid leave', 'Retired')),
    FOREIGN KEY (AirportId) REFERENCES Airport (AirportId) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (EmployeeId)
);

CREATE TABLE MechanicEmployee
(
    EmployeeId    BIGINT PRIMARY KEY,
    MaintenanceId BIGINT,
    FOREIGN KEY (EmployeeId) REFERENCES Employee (EmployeeId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (MaintenanceId) REFERENCES AircraftMaintenance (MaintenanceId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE WorksOn
(
    EmployeeId    BIGINT NOT NULL,
    MaintenanceId BIGINT NOT NULL,
    PRIMARY KEY (EmployeeId, MaintenanceId),
    FOREIGN KEY (EmployeeId) REFERENCES MechanicEmployee (EmployeeId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (MaintenanceId) REFERENCES AircraftMaintenance (MaintenanceId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE GateEmployee
(
    EmployeeId BIGINT PRIMARY KEY,
    FOREIGN KEY (EmployeeId) REFERENCES Employee (EmployeeId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE SecurityEmployee
(
    EmployeeId BIGINT PRIMARY KEY,
    FOREIGN KEY (EmployeeId) REFERENCES Employee (EmployeeId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Ownership
(
    OwnerId    BIGSERIAL NOT NULL,
    StartDate  timestamp NOT NULL,
    AircraftId BIGINT,
    EndDate    timestamp NOT NULL,
    AirlineId  BIGINT,
    CONSTRAINT chk_ownership_end_after_start CHECK (EndDate > StartDate),
    PRIMARY KEY (OwnerId),
    FOREIGN KEY (AircraftId) REFERENCES Aircraft (AircraftId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (AirlineId) REFERENCES Airline (AirlineId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE AirportService
(
    ServiceId      BIGSERIAL   NOT NULL,
    ServiceName    varchar(30) NOT NULL UNIQUE,
    ServiceType    varchar(20) NOT NULL,
    TerminalId     BIGINT,
    OperatingHours varchar(10),
    Status         varchar(20) NOT NULL,
    CONSTRAINT chk_airportservice_status CHECK (Status IN ('Active', 'Inactive', 'Cancelled')),
    PRIMARY KEY (ServiceId),
    FOREIGN KEY (TerminalId) REFERENCES Terminal (TerminalId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE StaysAt
(
    HangarId   BIGINT NOT NULL,
    AircraftId BIGINT NOT NULL,
    PRIMARY KEY (HangarId, AircraftId),
    FOREIGN KEY (HangarId) REFERENCES Hangar (HangarId) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (AircraftId) REFERENCES Aircraft (AircraftId) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE WorksOnGate
(
    GateId                 BIGINT NOT NULL,
    GateEmployeeEmployeeId BIGINT NOT NULL,
    Date                   DATE,
    StartTime              TIME,
    EndTime                TIME,
    PRIMARY KEY (GateId, GateEmployeeEmployeeId),
    FOREIGN KEY (GateId) REFERENCES Gate (GateId) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (GateEmployeeEmployeeId) REFERENCES GateEmployee (EmployeeId) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE WorksOnSecurityCheck
(
    SecurityEmployeeEmployeeId BIGINT NOT NULL,
    SecurityCheckCheckId       BIGINT NOT NULL,
    PRIMARY KEY (SecurityEmployeeEmployeeId, SecurityCheckCheckId),
    FOREIGN KEY (SecurityEmployeeEmployeeId) REFERENCES SecurityEmployee (EmployeeId) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (SecurityCheckCheckId) REFERENCES SecurityCheck (CheckId) ON DELETE CASCADE ON UPDATE CASCADE
);