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

-- Table: CLASS
CREATE TABLE "spider__college_1"."CLASS" (
    "CLASS_CODE" TEXT NOT NULL,
    "CRS_CODE" TEXT,
    "CLASS_SECTION" TEXT,
    "CLASS_TIME" TEXT,
    "CLASS_ROOM" TEXT,
    "PROF_NUM" BIGINT,
    CONSTRAINT "CLASS_pkey" PRIMARY KEY ("CLASS_CODE")
);

-- Table: COURSE
CREATE TABLE "spider__college_1"."COURSE" (
    "CRS_CODE" TEXT NOT NULL,
    "DEPT_CODE" TEXT,
    "CRS_DESCRIPTION" TEXT,
    "CRS_CREDIT" DOUBLE PRECISION,
    CONSTRAINT "COURSE_pkey" PRIMARY KEY ("CRS_CODE")
);

-- Table: DEPARTMENT
CREATE TABLE "spider__college_1"."DEPARTMENT" (
    "DEPT_CODE" TEXT NOT NULL,
    "DEPT_NAME" TEXT,
    "SCHOOL_CODE" TEXT,
    "EMP_NUM" BIGINT,
    "DEPT_ADDRESS" TEXT,
    "DEPT_EXTENSION" TEXT,
    CONSTRAINT "DEPARTMENT_pkey" PRIMARY KEY ("DEPT_CODE")
);

-- Table: EMPLOYEE
CREATE TABLE "spider__college_1"."EMPLOYEE" (
    "EMP_NUM" BIGINT NOT NULL,
    "EMP_LNAME" TEXT,
    "EMP_FNAME" TEXT,
    "EMP_INITIAL" TEXT,
    "EMP_JOBCODE" TEXT,
    "EMP_HIREDATE" TIMESTAMP,
    "EMP_DOB" TIMESTAMP,
    CONSTRAINT "EMPLOYEE_pkey" PRIMARY KEY ("EMP_NUM")
);

-- Table: ENROLL
CREATE TABLE "spider__college_1"."ENROLL" (
    "CLASS_CODE" TEXT,
    "STU_NUM" BIGINT,
    "ENROLL_GRADE" TEXT
);

-- Table: PROFESSOR
CREATE TABLE "spider__college_1"."PROFESSOR" (
    "EMP_NUM" BIGINT,
    "DEPT_CODE" TEXT,
    "PROF_OFFICE" TEXT,
    "PROF_EXTENSION" TEXT,
    "PROF_HIGH_DEGREE" TEXT
);

-- Table: STUDENT
CREATE TABLE "spider__college_1"."STUDENT" (
    "STU_NUM" BIGINT NOT NULL,
    "STU_LNAME" TEXT,
    "STU_FNAME" TEXT,
    "STU_INIT" TEXT,
    "STU_DOB" TIMESTAMP,
    "STU_HRS" BIGINT,
    "STU_CLASS" TEXT,
    "STU_GPA" DOUBLE PRECISION,
    "STU_TRANSFER" NUMERIC,
    "DEPT_CODE" TEXT,
    "STU_PHONE" TEXT,
    "PROF_NUM" BIGINT,
    CONSTRAINT "STUDENT_pkey" PRIMARY KEY ("STU_NUM")
);

-- Foreign Keys
ALTER TABLE "spider__college_1"."CLASS" ADD CONSTRAINT "fk_CLASS_0" FOREIGN KEY ("PROF_NUM") REFERENCES "spider__college_1"."EMPLOYEE" ("EMP_NUM") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_1"."CLASS" ADD CONSTRAINT "fk_CLASS_1" FOREIGN KEY ("CRS_CODE") REFERENCES "spider__college_1"."COURSE" ("CRS_CODE") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_1"."COURSE" ADD CONSTRAINT "fk_COURSE_0" FOREIGN KEY ("DEPT_CODE") REFERENCES "spider__college_1"."DEPARTMENT" ("DEPT_CODE") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_1"."DEPARTMENT" ADD CONSTRAINT "fk_DEPARTMENT_0" FOREIGN KEY ("EMP_NUM") REFERENCES "spider__college_1"."EMPLOYEE" ("EMP_NUM") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_1"."ENROLL" ADD CONSTRAINT "fk_ENROLL_0" FOREIGN KEY ("STU_NUM") REFERENCES "spider__college_1"."STUDENT" ("STU_NUM") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_1"."ENROLL" ADD CONSTRAINT "fk_ENROLL_1" FOREIGN KEY ("CLASS_CODE") REFERENCES "spider__college_1"."CLASS" ("CLASS_CODE") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_1"."PROFESSOR" ADD CONSTRAINT "fk_PROFESSOR_0" FOREIGN KEY ("DEPT_CODE") REFERENCES "spider__college_1"."DEPARTMENT" ("DEPT_CODE") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_1"."PROFESSOR" ADD CONSTRAINT "fk_PROFESSOR_1" FOREIGN KEY ("EMP_NUM") REFERENCES "spider__college_1"."EMPLOYEE" ("EMP_NUM") ON DELETE NO ACTION ON UPDATE NO ACTION;
