-- Schema: bird__airline
CREATE SCHEMA IF NOT EXISTS "bird__airline";

-- Table: Air Carriers
CREATE TABLE "bird__airline"."Air Carriers" (
    "Code" BIGINT NOT NULL,
    "Description" TEXT,
    CONSTRAINT "Air Carriers_pkey" PRIMARY KEY ("Code")
);

-- Table: Airlines
CREATE TABLE "bird__airline"."Airlines" (
    "FL_DATE" TEXT,
    "OP_CARRIER_AIRLINE_ID" BIGINT,
    "TAIL_NUM" TEXT,
    "OP_CARRIER_FL_NUM" BIGINT,
    "ORIGIN_AIRPORT_ID" BIGINT,
    "ORIGIN_AIRPORT_SEQ_ID" BIGINT,
    "ORIGIN_CITY_MARKET_ID" BIGINT,
    "ORIGIN" TEXT,
    "DEST_AIRPORT_ID" BIGINT,
    "DEST_AIRPORT_SEQ_ID" BIGINT,
    "DEST_CITY_MARKET_ID" BIGINT,
    "DEST" TEXT,
    "CRS_DEP_TIME" BIGINT,
    "DEP_TIME" BIGINT,
    "DEP_DELAY" BIGINT,
    "DEP_DELAY_NEW" BIGINT,
    "ARR_TIME" BIGINT,
    "ARR_DELAY" BIGINT,
    "ARR_DELAY_NEW" BIGINT,
    "CANCELLED" BIGINT,
    "CANCELLATION_CODE" TEXT,
    "CRS_ELAPSED_TIME" BIGINT,
    "ACTUAL_ELAPSED_TIME" BIGINT,
    "CARRIER_DELAY" BIGINT,
    "WEATHER_DELAY" BIGINT,
    "NAS_DELAY" BIGINT,
    "SECURITY_DELAY" BIGINT,
    "LATE_AIRCRAFT_DELAY" BIGINT
);

-- Table: Airports
CREATE TABLE "bird__airline"."Airports" (
    "Code" TEXT NOT NULL,
    "Description" TEXT,
    CONSTRAINT "Airports_pkey" PRIMARY KEY ("Code")
);

-- Foreign Keys
ALTER TABLE "bird__airline"."Airlines" ADD CONSTRAINT "fk_Airlines_0" FOREIGN KEY ("OP_CARRIER_AIRLINE_ID") REFERENCES "bird__airline"."Air Carriers" ("Code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__airline"."Airlines" ADD CONSTRAINT "fk_Airlines_1" FOREIGN KEY ("DEST") REFERENCES "bird__airline"."Airports" ("Code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__airline"."Airlines" ADD CONSTRAINT "fk_Airlines_2" FOREIGN KEY ("ORIGIN") REFERENCES "bird__airline"."Airports" ("Code") ON DELETE NO ACTION ON UPDATE NO ACTION;
