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

-- Table: city
CREATE TABLE "bird__olympics"."city" (
    "id" BIGINT NOT NULL,
    "city_name" TEXT,
    CONSTRAINT "city_pkey" PRIMARY KEY ("id")
);

-- Table: competitor_event
CREATE TABLE "bird__olympics"."competitor_event" (
    "event_id" BIGINT,
    "competitor_id" BIGINT,
    "medal_id" BIGINT
);

-- Table: event
CREATE TABLE "bird__olympics"."event" (
    "id" BIGINT NOT NULL,
    "sport_id" BIGINT,
    "event_name" TEXT,
    CONSTRAINT "event_pkey" PRIMARY KEY ("id")
);

-- Table: games
CREATE TABLE "bird__olympics"."games" (
    "id" BIGINT NOT NULL,
    "games_year" BIGINT,
    "games_name" TEXT,
    "season" TEXT,
    CONSTRAINT "games_pkey" PRIMARY KEY ("id")
);

-- Table: games_city
CREATE TABLE "bird__olympics"."games_city" (
    "games_id" BIGINT,
    "city_id" BIGINT
);

-- Table: games_competitor
CREATE TABLE "bird__olympics"."games_competitor" (
    "id" BIGINT NOT NULL,
    "games_id" BIGINT,
    "person_id" BIGINT,
    "age" BIGINT,
    CONSTRAINT "games_competitor_pkey" PRIMARY KEY ("id")
);

-- Table: medal
CREATE TABLE "bird__olympics"."medal" (
    "id" BIGINT NOT NULL,
    "medal_name" TEXT,
    CONSTRAINT "medal_pkey" PRIMARY KEY ("id")
);

-- Table: noc_region
CREATE TABLE "bird__olympics"."noc_region" (
    "id" BIGINT NOT NULL,
    "noc" TEXT,
    "region_name" TEXT,
    CONSTRAINT "noc_region_pkey" PRIMARY KEY ("id")
);

-- Table: person
CREATE TABLE "bird__olympics"."person" (
    "id" BIGINT NOT NULL,
    "full_name" TEXT,
    "gender" TEXT,
    "height" BIGINT,
    "weight" BIGINT,
    CONSTRAINT "person_pkey" PRIMARY KEY ("id")
);

-- Table: person_region
CREATE TABLE "bird__olympics"."person_region" (
    "person_id" BIGINT,
    "region_id" BIGINT
);

-- Table: sport
CREATE TABLE "bird__olympics"."sport" (
    "id" BIGINT NOT NULL,
    "sport_name" TEXT,
    CONSTRAINT "sport_pkey" PRIMARY KEY ("id")
);

-- Foreign Keys
ALTER TABLE "bird__olympics"."competitor_event" ADD CONSTRAINT "fk_competitor_event_0" FOREIGN KEY ("medal_id") REFERENCES "bird__olympics"."medal" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__olympics"."competitor_event" ADD CONSTRAINT "fk_competitor_event_1" FOREIGN KEY ("event_id") REFERENCES "bird__olympics"."event" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__olympics"."competitor_event" ADD CONSTRAINT "fk_competitor_event_2" FOREIGN KEY ("competitor_id") REFERENCES "bird__olympics"."games_competitor" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__olympics"."event" ADD CONSTRAINT "fk_event_0" FOREIGN KEY ("sport_id") REFERENCES "bird__olympics"."sport" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__olympics"."games_city" ADD CONSTRAINT "fk_games_city_0" FOREIGN KEY ("games_id") REFERENCES "bird__olympics"."games" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__olympics"."games_city" ADD CONSTRAINT "fk_games_city_1" FOREIGN KEY ("city_id") REFERENCES "bird__olympics"."city" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__olympics"."games_competitor" ADD CONSTRAINT "fk_games_competitor_0" FOREIGN KEY ("person_id") REFERENCES "bird__olympics"."person" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__olympics"."games_competitor" ADD CONSTRAINT "fk_games_competitor_1" FOREIGN KEY ("games_id") REFERENCES "bird__olympics"."games" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__olympics"."person_region" ADD CONSTRAINT "fk_person_region_0" FOREIGN KEY ("region_id") REFERENCES "bird__olympics"."noc_region" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__olympics"."person_region" ADD CONSTRAINT "fk_person_region_1" FOREIGN KEY ("person_id") REFERENCES "bird__olympics"."person" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
