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

-- Table: RA
CREATE TABLE "bird__cs_semester"."RA" (
    "student_id" BIGINT NOT NULL,
    "capability" BIGINT,
    "prof_id" BIGINT NOT NULL,
    "salary" TEXT,
    CONSTRAINT "RA_pkey" PRIMARY KEY ("student_id", "prof_id")
);

-- Table: course
CREATE TABLE "bird__cs_semester"."course" (
    "course_id" BIGINT NOT NULL,
    "name" TEXT,
    "credit" BIGINT,
    "diff" BIGINT,
    CONSTRAINT "course_pk" PRIMARY KEY ("course_id")
);

-- Table: prof
CREATE TABLE "bird__cs_semester"."prof" (
    "prof_id" BIGINT NOT NULL,
    "gender" TEXT,
    "first_name" TEXT,
    "last_name" TEXT,
    "email" TEXT,
    "popularity" BIGINT,
    "teachingability" BIGINT,
    "graduate_from" TEXT,
    CONSTRAINT "prof_pk" PRIMARY KEY ("prof_id")
);

-- Table: registration
CREATE TABLE "bird__cs_semester"."registration" (
    "course_id" BIGINT NOT NULL,
    "student_id" BIGINT NOT NULL,
    "grade" TEXT,
    "sat" BIGINT,
    CONSTRAINT "registration_pkey" PRIMARY KEY ("course_id", "student_id")
);

-- Table: student
CREATE TABLE "bird__cs_semester"."student" (
    "student_id" BIGINT NOT NULL,
    "f_name" TEXT,
    "l_name" TEXT,
    "phone_number" TEXT,
    "email" TEXT,
    "intelligence" BIGINT,
    "gpa" DOUBLE PRECISION,
    "type" TEXT,
    CONSTRAINT "student_pkey" PRIMARY KEY ("student_id")
);

-- Foreign Keys
ALTER TABLE "bird__cs_semester"."RA" ADD CONSTRAINT "fk_RA_0" FOREIGN KEY ("student_id") REFERENCES "bird__cs_semester"."student" ("student_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__cs_semester"."registration" ADD CONSTRAINT "fk_registration_0" FOREIGN KEY ("student_id") REFERENCES "bird__cs_semester"."student" ("student_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__cs_semester"."registration" ADD CONSTRAINT "fk_registration_1" FOREIGN KEY ("course_id") REFERENCES "bird__cs_semester"."course" ("course_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
