CREATE TABLE Course(
    id integer PRIMARY KEY,    -- the same as number(38)
    department varchar2(4),
    code varchar2(3),
    name varchar2(40) NOT NULL,
    description varchar2(250),
    hours integer DEFAULT 3,
    crossListId integer,
    UNIQUE (department, code),
    CHECK (crossListId <> id)
    );

CREATE TABLE Prerequisite(
    courseId integer,
    prerequisiteId integer
    );

CREATE TABLE Section(
    id integer PRIMARY KEY,
    courseId integer REFERENCES Course(id),
    letter char(2),
    semester varchar2(2) CHECK (semester in ('FA', 'SP', 'IN')),
    year varchar2(4),
    professor varchar2(15),
    UNIQUE (courseId, letter, semester, year)
    );

CREATE TABLE Student(
    id integer PRIMARY KEY,
    firstName varchar2(15),
    lastName varchar2(15),
    gender char(1) CHECK (gender IN ('m', 'f')),
    birthdate date,
    email varchar2(20) UNIQUE,
    mentorId integer
    );

CREATE TABLE Enrollment(
    studentId integer REFERENCES Student(id) ON DELETE CASCADE,
    sectionId integer REFERENCES Section(id),
    grade varchar2(2)
    );

CREATE TABLE Assignment(
    id integer,
    sectionId integer REFERENCES Section(id),
    type varchar2(5),
    totalPoints integer,
    PRIMARY KEY (id, sectionId, type)
    );

CREATE TABLE AssignmentType(
    sectionId integer REFERENCES Section(id),
    type varchar2(5),
    weight number(2,2)
    );
    
CREATE TABLE StudentAssignment(
    studentId integer REFERENCES Student(id) ON DELETE CASCADE,
    assignmentId integer,
    assignmentType varchar2(5),
    sectionId integer REFERENCES Section(id),
    score number(5,2)
    );

CREATE TABLE Team(
    id integer,
    sectionId integer REFERENCES Section(id),
    name varchar2(20),
    PRIMARY KEY (id, sectionId)
    );
    
CREATE TABLE StudentTeam(
    studentID integer REFERENCES Student(id) ON DELETE CASCADE,
    teamId integer,
    sectionId integer REFERENCES Section(id)
    );

CREATE TABLE TeamAssignment(
    teamId integer,
    assignmentId integer,
    assignmentType varchar2(5),
    sectionId integer REFERENCES Section(id),
    score number(5,2)
    );