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

-- Table: College
CREATE TABLE "spider__soccer_2"."College" (
    "cName" TEXT NOT NULL,
    "state" TEXT,
    "enr" NUMERIC(5),
    CONSTRAINT "College_pkey" PRIMARY KEY ("cName")
);

-- Table: Player
CREATE TABLE "spider__soccer_2"."Player" (
    "pID" NUMERIC(5) NOT NULL,
    "pName" TEXT,
    "yCard" TEXT,
    "HS" NUMERIC(5),
    CONSTRAINT "Player_pkey" PRIMARY KEY ("pID")
);

-- Table: Tryout
CREATE TABLE "spider__soccer_2"."Tryout" (
    "pID" NUMERIC(5) NOT NULL,
    "cName" TEXT NOT NULL,
    "pPos" TEXT,
    "decision" TEXT,
    CONSTRAINT "Tryout_pkey" PRIMARY KEY ("pID", "cName")
);

-- Foreign Keys
ALTER TABLE "spider__soccer_2"."Tryout" ADD CONSTRAINT "fk_Tryout_0" FOREIGN KEY ("cName") REFERENCES "spider__soccer_2"."College" ("cName") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__soccer_2"."Tryout" ADD CONSTRAINT "fk_Tryout_1" FOREIGN KEY ("pID") REFERENCES "spider__soccer_2"."Player" ("pID") ON DELETE NO ACTION ON UPDATE NO ACTION;
