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

-- Table: Course
CREATE TABLE "spider__college_3"."Course" (
    "CID" TEXT NOT NULL,
    "CName" TEXT,
    "Credits" BIGINT,
    "Instructor" BIGINT,
    "Days" TEXT,
    "Hours" TEXT,
    "DNO" BIGINT,
    CONSTRAINT "Course_pkey" PRIMARY KEY ("CID")
);

-- Table: Department
CREATE TABLE "spider__college_3"."Department" (
    "DNO" BIGINT NOT NULL,
    "Division" TEXT,
    "DName" TEXT,
    "Room" TEXT,
    "Building" TEXT,
    "DPhone" BIGINT,
    CONSTRAINT "Department_pkey" PRIMARY KEY ("DNO")
);

-- Table: Enrolled_in
CREATE TABLE "spider__college_3"."Enrolled_in" (
    "StuID" BIGINT,
    "CID" TEXT,
    "Grade" TEXT
);

-- Table: Faculty
CREATE TABLE "spider__college_3"."Faculty" (
    "FacID" BIGINT NOT NULL,
    "Lname" TEXT,
    "Fname" TEXT,
    "Rank" TEXT,
    "Sex" TEXT,
    "Phone" BIGINT,
    "Room" TEXT,
    "Building" TEXT,
    CONSTRAINT "Faculty_pkey" PRIMARY KEY ("FacID")
);

-- Table: Gradeconversion
CREATE TABLE "spider__college_3"."Gradeconversion" (
    "lettergrade" TEXT NOT NULL,
    "gradepoint" DOUBLE PRECISION,
    CONSTRAINT "Gradeconversion_pkey" PRIMARY KEY ("lettergrade")
);

-- Table: Member_of
CREATE TABLE "spider__college_3"."Member_of" (
    "FacID" BIGINT,
    "DNO" BIGINT,
    "Appt_Type" TEXT
);

-- Table: Minor_in
CREATE TABLE "spider__college_3"."Minor_in" (
    "StuID" BIGINT,
    "DNO" BIGINT
);

-- Table: Student
CREATE TABLE "spider__college_3"."Student" (
    "StuID" BIGINT NOT NULL,
    "LName" TEXT,
    "Fname" TEXT,
    "Age" BIGINT,
    "Sex" TEXT,
    "Major" BIGINT,
    "Advisor" BIGINT,
    "city_code" TEXT,
    CONSTRAINT "Student_pkey" PRIMARY KEY ("StuID")
);

-- Foreign Keys
ALTER TABLE "spider__college_3"."Course" ADD CONSTRAINT "fk_Course_0" FOREIGN KEY ("DNO") REFERENCES "spider__college_3"."Department" ("DNO") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_3"."Course" ADD CONSTRAINT "fk_Course_1" FOREIGN KEY ("Instructor") REFERENCES "spider__college_3"."Faculty" ("FacID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_3"."Enrolled_in" ADD CONSTRAINT "fk_Enrolled_in_0" FOREIGN KEY ("Grade") REFERENCES "spider__college_3"."Gradeconversion" ("lettergrade") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_3"."Enrolled_in" ADD CONSTRAINT "fk_Enrolled_in_1" FOREIGN KEY ("CID") REFERENCES "spider__college_3"."Course" ("CID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_3"."Enrolled_in" ADD CONSTRAINT "fk_Enrolled_in_2" FOREIGN KEY ("StuID") REFERENCES "spider__college_3"."Student" ("StuID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_3"."Member_of" ADD CONSTRAINT "fk_Member_of_0" FOREIGN KEY ("DNO") REFERENCES "spider__college_3"."Department" ("DNO") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_3"."Member_of" ADD CONSTRAINT "fk_Member_of_1" FOREIGN KEY ("FacID") REFERENCES "spider__college_3"."Faculty" ("FacID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_3"."Minor_in" ADD CONSTRAINT "fk_Minor_in_0" FOREIGN KEY ("DNO") REFERENCES "spider__college_3"."Department" ("DNO") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__college_3"."Minor_in" ADD CONSTRAINT "fk_Minor_in_1" FOREIGN KEY ("StuID") REFERENCES "spider__college_3"."Student" ("StuID") ON DELETE NO ACTION ON UPDATE NO ACTION;
