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

-- Table: college
CREATE TABLE "spider__decoration_competition"."college" (
    "College_ID" BIGINT NOT NULL,
    "Name" TEXT,
    "Leader_Name" TEXT,
    "College_Location" TEXT,
    CONSTRAINT "college_pkey" PRIMARY KEY ("College_ID")
);

-- Table: member
CREATE TABLE "spider__decoration_competition"."member" (
    "Member_ID" BIGINT NOT NULL,
    "Name" TEXT,
    "Country" TEXT,
    "College_ID" BIGINT,
    CONSTRAINT "member_pkey" PRIMARY KEY ("Member_ID")
);

-- Table: round
CREATE TABLE "spider__decoration_competition"."round" (
    "Round_ID" BIGINT NOT NULL,
    "Member_ID" BIGINT NOT NULL,
    "Decoration_Theme" TEXT,
    "Rank_in_Round" BIGINT,
    CONSTRAINT "round_pkey" PRIMARY KEY ("Round_ID", "Member_ID")
);

-- Foreign Keys
ALTER TABLE "spider__decoration_competition"."member" ADD CONSTRAINT "fk_member_0" FOREIGN KEY ("College_ID") REFERENCES "spider__decoration_competition"."college" ("College_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__decoration_competition"."round" ADD CONSTRAINT "fk_round_0" FOREIGN KEY ("Member_ID") REFERENCES "spider__decoration_competition"."member" ("Member_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
