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

-- Table: advisor
CREATE TABLE "spider__college_2"."advisor" (
    "s_ID" TEXT NOT NULL,
    "i_ID" TEXT,
    CONSTRAINT "advisor_pkey" PRIMARY KEY ("s_ID")
);

-- Table: classroom
CREATE TABLE "spider__college_2"."classroom" (
    "building" TEXT NOT NULL,
    "room_number" TEXT NOT NULL,
    "capacity" NUMERIC(4),
    CONSTRAINT "classroom_pkey" PRIMARY KEY ("building", "room_number")
);

-- Table: course
CREATE TABLE "spider__college_2"."course" (
    "course_id" TEXT NOT NULL,
    "title" TEXT,
    "dept_name" TEXT,
    "credits" NUMERIC(2),
    CONSTRAINT "course_pkey" PRIMARY KEY ("course_id")
);

-- Table: department
CREATE TABLE "spider__college_2"."department" (
    "dept_name" TEXT NOT NULL,
    "building" TEXT,
    "budget" NUMERIC(12,2),
    CONSTRAINT "department_pkey" PRIMARY KEY ("dept_name")
);

-- Table: instructor
CREATE TABLE "spider__college_2"."instructor" (
    "ID" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "dept_name" TEXT,
    "salary" NUMERIC(8,2),
    CONSTRAINT "instructor_pkey" PRIMARY KEY ("ID")
);

-- Table: prereq
CREATE TABLE "spider__college_2"."prereq" (
    "course_id" TEXT NOT NULL,
    "prereq_id" TEXT NOT NULL,
    CONSTRAINT "prereq_pkey" PRIMARY KEY ("course_id", "prereq_id")
);

-- Table: section
CREATE TABLE "spider__college_2"."section" (
    "course_id" TEXT NOT NULL,
    "sec_id" TEXT NOT NULL,
    "semester" TEXT NOT NULL,
    "year" NUMERIC(4) NOT NULL,
    "building" TEXT,
    "room_number" TEXT,
    "time_slot_id" TEXT,
    CONSTRAINT "section_pkey" PRIMARY KEY ("course_id", "sec_id", "semester", "year")
);

-- Table: student
CREATE TABLE "spider__college_2"."student" (
    "ID" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "dept_name" TEXT,
    "tot_cred" NUMERIC(3),
    CONSTRAINT "student_pkey" PRIMARY KEY ("ID")
);

-- Table: takes
CREATE TABLE "spider__college_2"."takes" (
    "ID" TEXT NOT NULL,
    "course_id" TEXT NOT NULL,
    "sec_id" TEXT NOT NULL,
    "semester" TEXT NOT NULL,
    "year" NUMERIC(4) NOT NULL,
    "grade" TEXT,
    CONSTRAINT "takes_pkey" PRIMARY KEY ("ID", "course_id", "sec_id", "semester", "year")
);

-- Table: teaches
CREATE TABLE "spider__college_2"."teaches" (
    "ID" TEXT NOT NULL,
    "course_id" TEXT NOT NULL,
    "sec_id" TEXT NOT NULL,
    "semester" TEXT NOT NULL,
    "year" NUMERIC(4) NOT NULL,
    CONSTRAINT "teaches_pkey" PRIMARY KEY ("ID", "course_id", "sec_id", "semester", "year")
);

-- Table: time_slot
CREATE TABLE "spider__college_2"."time_slot" (
    "time_slot_id" TEXT NOT NULL,
    "day" TEXT NOT NULL,
    "start_hr" NUMERIC(2) NOT NULL,
    "start_min" NUMERIC(2) NOT NULL,
    "end_hr" NUMERIC(2),
    "end_min" NUMERIC(2),
    CONSTRAINT "time_slot_pkey" PRIMARY KEY ("time_slot_id", "day", "start_hr", "start_min")
);

-- Foreign Keys
ALTER TABLE "spider__college_2"."advisor" ADD CONSTRAINT "fk_advisor_0" FOREIGN KEY ("s_ID") REFERENCES "spider__college_2"."student" ("ID") ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE "spider__college_2"."advisor" ADD CONSTRAINT "fk_advisor_1" FOREIGN KEY ("i_ID") REFERENCES "spider__college_2"."instructor" ("ID") ON DELETE SET NULL ON UPDATE NO ACTION;
ALTER TABLE "spider__college_2"."course" ADD CONSTRAINT "fk_course_0" FOREIGN KEY ("dept_name") REFERENCES "spider__college_2"."department" ("dept_name") ON DELETE SET NULL ON UPDATE NO ACTION;
ALTER TABLE "spider__college_2"."instructor" ADD CONSTRAINT "fk_instructor_0" FOREIGN KEY ("dept_name") REFERENCES "spider__college_2"."department" ("dept_name") ON DELETE SET NULL ON UPDATE NO ACTION;
ALTER TABLE "spider__college_2"."prereq" ADD CONSTRAINT "fk_prereq_0" FOREIGN KEY ("prereq_id") REFERENCES "spider__college_2"."course" ("course_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_2"."prereq" ADD CONSTRAINT "fk_prereq_1" FOREIGN KEY ("course_id") REFERENCES "spider__college_2"."course" ("course_id") ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE "spider__college_2"."section" ADD CONSTRAINT "fk_section_0" FOREIGN KEY ("building", "building", "room_number", "room_number") REFERENCES "spider__college_2"."classroom" ("building", "building", "room_number", "room_number") ON DELETE SET NULL ON UPDATE NO ACTION;
ALTER TABLE "spider__college_2"."section" ADD CONSTRAINT "fk_section_1" FOREIGN KEY ("course_id") REFERENCES "spider__college_2"."course" ("course_id") ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE "spider__college_2"."student" ADD CONSTRAINT "fk_student_0" FOREIGN KEY ("dept_name") REFERENCES "spider__college_2"."department" ("dept_name") ON DELETE SET NULL ON UPDATE NO ACTION;
ALTER TABLE "spider__college_2"."takes" ADD CONSTRAINT "fk_takes_0" FOREIGN KEY ("ID") REFERENCES "spider__college_2"."student" ("ID") ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE "spider__college_2"."takes" ADD CONSTRAINT "fk_takes_1" FOREIGN KEY ("course_id", "course_id", "course_id", "course_id", "sec_id", "sec_id", "sec_id", "sec_id", "semester", "semester", "semester", "semester", "year", "year", "year", "year") REFERENCES "spider__college_2"."section" ("course_id", "course_id", "course_id", "course_id", "sec_id", "sec_id", "sec_id", "sec_id", "semester", "semester", "semester", "semester", "year", "year", "year", "year") ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE "spider__college_2"."teaches" ADD CONSTRAINT "fk_teaches_0" FOREIGN KEY ("ID") REFERENCES "spider__college_2"."instructor" ("ID") ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE "spider__college_2"."teaches" ADD CONSTRAINT "fk_teaches_1" FOREIGN KEY ("course_id", "course_id", "course_id", "course_id", "sec_id", "sec_id", "sec_id", "sec_id", "semester", "semester", "semester", "semester", "year", "year", "year", "year") REFERENCES "spider__college_2"."section" ("course_id", "course_id", "course_id", "course_id", "sec_id", "sec_id", "sec_id", "sec_id", "semester", "semester", "semester", "semester", "year", "year", "year", "year") ON DELETE CASCADE ON UPDATE NO ACTION;
