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

-- Table: aircraft
CREATE TABLE "spider__flight_1"."aircraft" (
    "aid" TEXT NOT NULL,
    "name" TEXT,
    "distance" TEXT,
    CONSTRAINT "aircraft_pkey" PRIMARY KEY ("aid")
);

-- Table: certificate
CREATE TABLE "spider__flight_1"."certificate" (
    "eid" TEXT NOT NULL,
    "aid" TEXT NOT NULL,
    CONSTRAINT "certificate_pkey" PRIMARY KEY ("eid", "aid")
);

-- Table: employee
CREATE TABLE "spider__flight_1"."employee" (
    "eid" TEXT NOT NULL,
    "name" TEXT,
    "salary" TEXT,
    CONSTRAINT "employee_pkey" PRIMARY KEY ("eid")
);

-- Table: flight
CREATE TABLE "spider__flight_1"."flight" (
    "flno" TEXT NOT NULL,
    "origin" TEXT,
    "destination" TEXT,
    "distance" TEXT,
    "departure_date" DATE,
    "arrival_date" DATE,
    "price" TEXT,
    "aid" TEXT,
    CONSTRAINT "flight_pkey" PRIMARY KEY ("flno")
);

-- Foreign Keys
ALTER TABLE "spider__flight_1"."certificate" ADD CONSTRAINT "fk_certificate_0" FOREIGN KEY ("aid") REFERENCES "spider__flight_1"."aircraft" ("aid") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__flight_1"."certificate" ADD CONSTRAINT "fk_certificate_1" FOREIGN KEY ("eid") REFERENCES "spider__flight_1"."employee" ("eid") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__flight_1"."flight" ADD CONSTRAINT "fk_flight_0" FOREIGN KEY ("aid") REFERENCES "spider__flight_1"."aircraft" ("aid") ON DELETE NO ACTION ON UPDATE NO ACTION;
