DatabaseCreation: DDL_manual.sql

CREATE TABLE Member (
    id              INTEGER         GENERATED ALWAYS AS IDENTITY,

    name            TEXT,
    surname         TEXT,
    username        TEXT,
    password_hash   TEXT,
    date_birth      DATE,
    is_active       BOOLEAN         DEFAULT TRUE,
    created_at      TIMESTAMP WITHOUT TIME ZONE     DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP WITHOUT TIME ZONE     DEFAULT CURRENT_TIMESTAMP,


    CONSTRAINT pk_member                            PRIMARY KEY(id),

    CONSTRAINT nn_member_name                       CHECK(name IS NOT NULL),
    CONSTRAINT nn_member_surname                    CHECK(surname IS NOT NULL),
    CONSTRAINT uq_member_username                   UNIQUE(username),
    CONSTRAINT nn_member_username                   CHECK(username IS NOT NULL),
    CONSTRAINT nn_member_password_hash              CHECK(password_hash IS NOT NULL),
    CONSTRAINT nn_member_date_birth                 CHECK(date_birth IS NOT NULL),
    CONSTRAINT nn_member_created_at                 CHECK(created_at IS NOT NULL),

    CONSTRAINT ck_member_created_before_updated     CHECK(created_at <= updated_at)
);

CREATE TABLE Member_Message (
    id              INTEGER         GENERATED ALWAYS AS IDENTITY,

    member_id1      INTEGER,
    member_id2      INTEGER,

    message         TEXT,
    sent_at         TIMESTAMP WITHOUT TIME ZONE     DEFAULT CURRENT_TIMESTAMP,


    CONSTRAINT pk_member_message            PRIMARY KEY(id, member_id1, member_id2),

    CONSTRAINT fk_member_message_member_id1       FOREIGN KEY(member_id1)
        REFERENCES Member (id)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_member_message_member_id2       FOREIGN KEY(member_id2)
        REFERENCES Member (id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_member_message_message    CHECK(message IS NOT NULL),
    CONSTRAINT nn_member_message_sent_at    CHECK(sent_at IS NOT NULL)
);

CREATE TABLE Student (
    id      INTEGER,

    index   TEXT,


    CONSTRAINT pk_student       PRIMARY KEY(id),

    CONSTRAINT fk_student_id    FOREIGN KEY(id)
        REFERENCES Member (id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT uq_student_index UNIQUE(index),
    CONSTRAINT nn_student_index CHECK(index IS NOT NULL),
    CONSTRAINT ck_student_index CHECK(index ~ '^\d{10}$')
);

CREATE TABLE Teacher (
    id                  INTEGER,

    date_registration   DATE,


    CONSTRAINT pk_teacher   PRIMARY KEY(id),

    CONSTRAINT fk_teacher   FOREIGN KEY(id)
        REFERENCES Member (id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_teacher_date_registration CHECK(date_registration IS NOT NULL)
);

CREATE TABLE Teaching_Assistant (
    id          INTEGER         GENERATED ALWAYS AS IDENTITY,
    student_id  INTEGER,
    teacher_id  INTEGER,


    CONSTRAINT pk_teaching_assistant            PRIMARY KEY(id, student_id, teacher_id),

    CONSTRAINT fk_teaching_assistant_student_id FOREIGN KEY(student_id)
        REFERENCES Student(id)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_teaching_assistant_teacher_id FOREIGN KEY(teacher_id)
        REFERENCES Teacher(id)
            ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Academic_Program (
    code            TEXT,

    name            TEXT,
    numeric_id      SMALLINT,
    duration_years  SMALLINT,
    created_at      DATE,
    updated_at      DATE,


    CONSTRAINT pk_academic_program      PRIMARY KEY(code),

    CONSTRAINT nn_academic_program_name                     CHECK(name IS NOT NULL),
    CONSTRAINT nn_academic_program_created_at               CHECK(created_at IS NOT NULL),
    CONSTRAINT nn_academic_program_updated_at               CHECK(updated_at IS NOT NULL),
    CONSTRAINT ck_academic_program_updated_after_created    CHECK(updated_at >= created_at),
    CONSTRAINT ck_academic_program_duration_years           CHECK (duration_years >= 1 AND duration_years <= 6)
);

CREATE TABLE Program_Enrollment (
    id                      INTEGER         GENERATED ALWAYS AS IDENTITY,

    student_id              INTEGER,

    date_enrollment         DATE,
    date_disenrollment      DATE,
    finished                BOOLEAN,

    academic_program_code   TEXT,


    CONSTRAINT pk_program_enrollment_id                     PRIMARY KEY(id),

    CONSTRAINT fk_program_enrollment_student_id             FOREIGN KEY(student_id)
        REFERENCES Student(id)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_program_enrollment_academic_program_code  FOREIGN KEY(academic_program_code)
        REFERENCES Academic_Program(code)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT uq_program_enrollment_1                      UNIQUE(student_id, academic_program_code, date_enrollment),
    CONSTRAINT nn_program_enrollment_student_id             CHECK(student_id IS NOT NULL),
    CONSTRAINT nn_program_enrollment_academic_program_code  CHECK(academic_program_code IS NOT NULL),
    CONSTRAINT nn_program_enrollment_date_enrollment        CHECK(date_enrollment IS NOT NULL)
);

CREATE TABLE Course (
    code        TEXT,

    name        TEXT,
    credits     INT,
    created_at  DATE,


    CONSTRAINT pk_course            PRIMARY KEY(code),

    CONSTRAINT nn_course_name       CHECK(name IS NOT NULL),
    CONSTRAINT nn_course_credits    CHECK(credits IS NOT NULL),
    CONSTRAINT nn_course_created_at CHECK(created_at IS NOT NULL),
    CONSTRAINT ck_course_credits    CHECK(credits >= 0)
);

CREATE TABLE Curriculum (
    academic_program_code   TEXT,
    course_code             TEXT,

    semester                SMALLINT,
    mandatory               BOOLEAN,


    CONSTRAINT pk_curriculum    PRIMARY KEY(academic_program_code, course_code),

    CONSTRAINT fk_curriculum_academic_program_code FOREIGN KEY(academic_program_code)
        REFERENCES Academic_Program(code)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_curriculum_course_code FOREIGN KEY(course_code)
        REFERENCES Course(code)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_curriculum_semester       CHECK(semester IS NOT NULL),
    CONSTRAINT nn_curriculum_mandatory      CHECK(mandatory IS NOT NULL)
);

CREATE TABLE Course_Prerequisite (
    predecessor_course_code TEXT,
    successor_course_code   TEXT,


    CONSTRAINT pk_course_prerequisite       PRIMARY KEY(predecessor_course_code, successor_course_code),

    CONSTRAINT fk_course_prerequisite_predecessor_course_code   FOREIGN KEY(predecessor_course_code)
        REFERENCES Course(code)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_course_prerequisite_successor_course_code     FOREIGN KEY(successor_course_code)
        REFERENCES Course(code)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT ck_course_prerequisite_different_courses         CHECK(predecessor_course_code <> successor_course_code)
);

CREATE TABLE Course_Equivalence (
    course1_code TEXT,
    course2_code TEXT,


    CONSTRAINT pk_course_equivalence               PRIMARY KEY(course1_code, course2_code),

    CONSTRAINT fk_course_equivalence_course1_code  FOREIGN KEY(course1_code)
        REFERENCES Course(code)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_course_equivalence_course2_code  FOREIGN KEY(course2_code)
        REFERENCES Course(code)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT ck_course_equivalence_different_courses         CHECK(course1_code <> course2_code)
);

CREATE TABLE Course_Edition (
    id              INTEGER         GENERATED ALWAYS AS IDENTITY,

    course_code     TEXT,
    academic_year   SMALLINT,
    semester        SMALLINT,


    CONSTRAINT pk_course_edition                PRIMARY KEY(id),

    CONSTRAINT fk_course_edition                FOREIGN KEY(course_code)
        REFERENCES Course(code)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT uq_course_edition_ccays          UNIQUE(course_code, academic_year, semester),
    CONSTRAINT nn_course_edition_academic_year  CHECK(academic_year IS NOT NULL),
    CONSTRAINT nn_course_edition_semester       CHECK(semester IS NOT NULL),
    CONSTRAINT ck_course_edition_semester       CHECK(semester >= 1 AND semester <= 2)
);

CREATE TABLE Teaches (
    teacher_id          INTEGER,
    course_edition_id   INTEGER,


    CONSTRAINT pk_teaches                   PRIMARY KEY(teacher_id, course_edition_id),

    CONSTRAINT fk_teaches_teacher_id        FOREIGN KEY(teacher_id)
        REFERENCES Teacher(id)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_teaches_course_edition_id FOREIGN KEY(course_edition_id)
        REFERENCES Course_Edition(id)
            ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Announcement (
    id                          INTEGER         GENERATED ALWAYS AS IDENTITY,

    teaches_teacher_id          INTEGER,
    teaches_course_edition_id   INTEGER,

    message                     TEXT,
    sent_at                     TIMESTAMP WITHOUT TIME ZONE     DEFAULT CURRENT_TIMESTAMP,


    CONSTRAINT pk_announcement PRIMARY KEY(id, teaches_teacher_id, teaches_course_edition_id),

    CONSTRAINT fk_announcement_teaches FOREIGN KEY(teaches_teacher_id, teaches_course_edition_id)
        REFERENCES Teaches(teacher_id, course_edition_id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT ck_announcement_message CHECK(message IS NOT NULL),
    CONSTRAINT ck_announcement_sent_at CHECK(sent_at IS NOT NULL)
);

CREATE TABLE Announcement_Replies (
    ann1_id    INTEGER,
    ann1_ttid  INTEGER,
    ann1_ceid  INTEGER,
    ann2_id    INTEGER,
    ann2_ttid  INTEGER,
    ann2_ceid  INTEGER,


    CONSTRAINT pk_announcement_replies
        PRIMARY KEY(
            ann1_id,
            ann1_ttid,
            ann1_ceid,
            ann2_id,
            ann2_ttid,
            ann2_ceid
        ),

    CONSTRAINT fk_announcement_replies_ann1 FOREIGN KEY(ann1_id, ann1_ttid, ann1_ceid)
        REFERENCES Announcement(id, teaches_teacher_id, teaches_course_edition_id)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_announcement_replies_ann2 FOREIGN KEY(ann2_id, ann2_ttid, ann2_ceid)
        REFERENCES Announcement(id, teaches_teacher_id, teaches_course_edition_id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT ck_announcement_replies_different_replies CHECK ( NOT (ann1_id = ann2_id AND ann1_ttid = ann2_ttid AND ann1_ceid = ann2_ceid) )
);

CREATE TABLE Lecture (
    id                  INTEGER         GENERATED ALWAYS AS IDENTITY,

    course_edition_id   INTEGER,

    title               TEXT,
    content_url         TEXT,
    created_at          TIMESTAMP WITHOUT TIME ZONE,


    CONSTRAINT pk_lecture PRIMARY KEY(id),

    CONSTRAINT fk_lecture FOREIGN KEY(course_edition_id)
        REFERENCES Course_Edition(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_lecture_title         CHECK (title IS NOT NULL),
    CONSTRAINT nn_lecture_created_at    CHECK (created_at IS NOT NULL)
);

CREATE TABLE Semester_Enrollment (
    id                      INTEGER         GENERATED ALWAYS AS IDENTITY,

    program_enrollment_id   INTEGER,

    academic_year           SMALLINT,
    semester                SMALLINT,


    CONSTRAINT pk_semester_enrollment PRIMARY KEY(id),

    CONSTRAINT fk_semester_enrollment FOREIGN KEY(program_enrollment_id)
        REFERENCES Program_Enrollment(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT uq_semester_enrollment_peidays       UNIQUE(program_enrollment_id, academic_year, semester),
    CONSTRAINT nn_semester_enrollment_academic_year CHECK(academic_year IS NOT NULL),
    CONSTRAINT nn_semester_enrollment_semester      CHECK(semester IS NOT NULL),
    CONSTRAINT ck_semester_enrollment_academic_year CHECK(academic_year >= 2000),
    CONSTRAINT ck_semester_enrollment_semester      CHECK(semester >= 1 AND semester <= 2)
);

CREATE TABLE Course_Enrollment (
    id                      INTEGER         GENERATED ALWAYS AS IDENTITY,

    semester_enrollment_id  INTEGER,
    course_edition_id       INTEGER,


    CONSTRAINT pk_course_enrollment PRIMARY KEY(id),

    CONSTRAINT fk_course_enrollment_seid FOREIGN KEY(semester_enrollment_id)
        REFERENCES Semester_Enrollment(id)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_course_enrollment_ceid FOREIGN KEY(course_edition_id)
        REFERENCES Course_Edition(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT uq_course_enrollment_course_once_per_semenroll UNIQUE(semester_enrollment_id, course_edition_id)
);

CREATE TABLE Exercise (
    id                  INTEGER         GENERATED ALWAYS AS IDENTITY,

    course_edition_id   INTEGER,

    title               TEXT,
    task                TEXT,
    expected_result     TEXT,
    deadline            DATE,
    max_grade           INTEGER,
    created_at          DATE,


    CONSTRAINT pk_exercise                  PRIMARY KEY(id),

    CONSTRAINT fk_exercise                  FOREIGN KEY(course_edition_id)
        REFERENCES Course_Edition(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_exercise_title                    CHECK(title IS NOT NULL),
    CONSTRAINT nn_exercise_task                     CHECK(task IS NOT NULL),
    CONSTRAINT nn_exercise_expected_result          CHECK(expected_result IS NOT NULL),
    CONSTRAINT nn_exercise_max_grade                CHECK(max_grade IS NOT NULL),
    CONSTRAINT nn_exercise_created_at               CHECK(created_at IS NOT NULL),
    CONSTRAINT ck_exercise_max_grade                CHECK(max_grade > 0),
    CONSTRAINT ck_exercise_deadline_after_created   CHECK (deadline IS NULL OR deadline > created_at)
);

CREATE TABLE Exercise_Submission (
    id                      INTEGER         GENERATED ALWAYS AS IDENTITY,

    exercise_id             INTEGER,
    course_enrollment_id    INTEGER,

    content                 TEXT,
    grade                   INTEGER,
    submitted_at            TIMESTAMP WITHOUT TIME ZONE,
    graded_at               TIMESTAMP WITHOUT TIME ZONE,


    CONSTRAINT pk_exercise_submission   PRIMARY KEY(id, exercise_id, course_enrollment_id),

    CONSTRAINT fk_exercise_submission_exercise_id  FOREIGN KEY(exercise_id)
        REFERENCES Exercise(id)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_exercise_submission_ceid         FOREIGN KEY(course_enrollment_id)
        REFERENCES Course_Enrollment(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_exercise_submission_content                              CHECK(content IS NOT NULL),
    CONSTRAINT nn_exercise_submission_submission_submitted_at              CHECK(submitted_at IS NOT NULL),
    CONSTRAINT ck_exercise_submission_submission_graded_after_submitted    CHECK (graded_at >= submitted_at),
    CONSTRAINT ck_exercise_submission_submission_grade_graded_at           CHECK((grade IS NULL AND graded_at IS NULL) OR (grade IS NOT NULL AND graded_at IS NOT NULL))
);

CREATE TABLE Survey (
    id                  INTEGER         GENERATED ALWAYS AS IDENTITY,

    course_edition_id   INTEGER,
    title               TEXT,
    open_until          TIMESTAMP WITHOUT TIME ZONE,


    CONSTRAINT pk_survey        PRIMARY KEY(id),

    CONSTRAINT fk_survey        FOREIGN KEY(course_edition_id)
        REFERENCES Course_Edition(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_survey_title  CHECK(title IS NOT NULL)
);

CREATE TABLE Survey_Option (
    option      TEXT,

    survey_id   INTEGER,

    capacity    INTEGER,


    CONSTRAINT pk_survey_option PRIMARY KEY(option, survey_id),

    CONSTRAINT fk_survey_option FOREIGN KEY(survey_id)
        REFERENCES Survey(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT ck_survey_option_capacity CHECK(capacity > 0)
);

CREATE TABLE Survey_Response (
    option_text             TEXT,

    survey_id               INTEGER,
    course_enrollment_id    INTEGER,

    responded_at            TIMESTAMP WITHOUT TIME ZONE,


    CONSTRAINT pk_survey_response               PRIMARY KEY(option_text, survey_id, course_enrollment_id),

    CONSTRAINT fk_survey_response_survey_id     FOREIGN KEY(survey_id)
        REFERENCES Survey(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT fk_survey_response_ceid          FOREIGN KEY(course_enrollment_id)
        REFERENCES Course_Enrollment(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_survey_response_responded_at  CHECK(responded_at IS NOT NULL)
);

CREATE TABLE Exam (
    id                  INTEGER         GENERATED ALWAYS AS IDENTITY,

    course_edition_id   INTEGER,

    type                EXAM_TYPE,
    scheduled_at        TIMESTAMP WITHOUT TIME ZONE,
    duration_minutes    SMALLINT,


    CONSTRAINT pk_exam  PRIMARY KEY(id),

    CONSTRAINT fk_exam  FOREIGN KEY(course_edition_id)
        REFERENCES Course_Edition(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_exam_type             CHECK(type IS NOT NULL),
    CONSTRAINT ck_exam_duration_minutes CHECK(duration_minutes > 0)
);

CREATE TABLE Exam_Attempt (
    attempt_number          SMALLINT,
    exam_id                 INTEGER,
    course_enrollment_id    INTEGER,

    total_points            FLOAT,
    submitted_at            TIMESTAMP WITHOUT TIME ZONE,


    CONSTRAINT pk_exam_attempt          PRIMARY KEY(attempt_number, exam_id, course_enrollment_id),

    CONSTRAINT fk_exam_attempt_exam_id  FOREIGN KEY(exam_id)
        REFERENCES Exam(id)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_exam_attempt_ceid     FOREIGN KEY(course_enrollment_id)
        REFERENCES Course_Enrollment(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_exam_attempt_submitted_at CHECK(submitted_at IS NOT NULL),
    CONSTRAINT nn_exam_attempt_total_points CHECK(total_points IS NOT NULL),
    CONSTRAINT ck_exam_attempt_total_points CHECK(total_points >= 0)
);

CREATE TABLE Exam_Problem (
    id          INTEGER                     GENERATED ALWAYS AS IDENTITY,
    exam_id     INTEGER,

    description TEXT,
    expected    TEXT,
    points      FLOAT,


    CONSTRAINT pk_exam_problem              PRIMARY KEY(id, exam_id),

    CONSTRAINT fk_exam_problem              FOREIGN KEY(exam_id)
        REFERENCES Exam(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_exam_problem_description  CHECK(description IS NOT NULL),
    CONSTRAINT nn_exam_problem_expected     CHECK(expected IS NOT NULL),
    CONSTRAINT nn_exam_problem_points       CHECK(points IS NOT NULL),
    CONSTRAINT ck_exam_problem_points       CHECK(points > 0.0)
);

CREATE TABLE Student_Answer (
    exam_id                         INTEGER,
    exam_problem_id                 INTEGER,
    exam_attempt_ceid               INTEGER,

    exam_attempt_attempt_number     SMALLINT,
    answer                          TEXT,
    points_acquired                 FLOAT,


    CONSTRAINT pk_student_answer
        PRIMARY KEY(
            exam_id,
            exam_problem_id,
            exam_attempt_ceid,
            exam_attempt_attempt_number
        ),

    CONSTRAINT fk_student_answer_exam               FOREIGN KEY(exam_id)
        REFERENCES Exam(id)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_student_answer_exam_problem       FOREIGN KEY(exam_id, exam_problem_id)
        REFERENCES Exam_Problem(exam_id, id)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_student_answer_exam_attempt       FOREIGN KEY(exam_id, exam_attempt_ceid, exam_attempt_attempt_number)
        REFERENCES Exam_Attempt(exam_id, course_enrollment_id, attempt_number)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_student_answer_points_acquired    CHECK(points_acquired IS NOT NULL),
    CONSTRAINT ck_student_answer_points_acquired    CHECK(points_acquired >= 0)
);

CREATE TABLE Exam_Results (
    id                          INTEGER                 GENERATED ALWAYS AS IDENTITY,

    data                        jsonb,
    exported_at                 TIMESTAMP WITHOUT TIME ZONE,

    teaches_teacher_id          INTEGER,
    teaches_course_edition_id   INTEGER,
    exam_id                     INTEGER,


    CONSTRAINT pk_exam_results                          PRIMARY KEY (id),

    CONSTRAINT fk_exam_results_teaches                  FOREIGN KEY (teaches_teacher_id, teaches_course_edition_id)
        REFERENCES Teaches (teacher_id, course_edition_id)
            ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_exam_results_exam                     FOREIGN KEY (exam_id)
        REFERENCES Exam (id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT uq_exam_results_ette                     UNIQUE (exported_at, teaches_teacher_id, teaches_course_edition_id, exam_id),
    CONSTRAINT nn_exam_results_exported_at              CHECK (exported_at IS NOT NULL),
    CONSTRAINT nn_exam_results_data                     CHECK (data IS NOT NULL),
    CONSTRAINT ck_exam_results_exported_at              CHECK (exported_at <= CURRENT_TIMESTAMP),
    CONSTRAINT ck_exam_results_data                     CHECK (fn_validate_exam_data(data))
);

CREATE TABLE Aggregated_Course_Edition_Results (
    id                          INTEGER                 GENERATED ALWAYS AS IDENTITY,

    data                        jsonb,
    exported_at                 TIMESTAMP WITHOUT TIME ZONE,

    teaches_teacher_id          INTEGER,
    teaches_course_edition_id   INTEGER,


    CONSTRAINT pk_aggregated_course_edition_results     PRIMARY KEY (id),

    CONSTRAINT fk_aggregated_results_teaches            FOREIGN KEY (teaches_teacher_id, teaches_course_edition_id)
        REFERENCES Teaches (teacher_id, course_edition_id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT uq_aggregated_results_ette               UNIQUE (exported_at, teaches_teacher_id, teaches_course_edition_id),
    CONSTRAINT nn_aggregated_results_exported_at        CHECK (exported_at IS NOT NULL),
    CONSTRAINT nn_aggregated_results_data               CHECK (data IS NOT NULL),
    CONSTRAINT ck_aggregated_results_data               CHECK (fn_validate_aggregated_data(data))
);

CREATE TABLE Student_Grade (
    course_enrollment_id            INTEGER,

    grade                           SMALLINT,
    graded_at                       DATE,

    aggr_course_edition_res_id      INTEGER,


    CONSTRAINT pk_student_grade             PRIMARY KEY(course_enrollment_id),

    CONSTRAINT fk_student_grade             FOREIGN KEY(course_enrollment_id)
        REFERENCES Course_Enrollment(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT fk_student_aceri FOREIGN KEY(aggr_course_edition_res_id)
        REFERENCES Aggregated_Course_Edition_Results(id)
            ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT nn_student_grade_grade       CHECK(grade IS NOT NULL),
    CONSTRAINT nn_student_grade_graded_at   CHECK(graded_at IS NOT NULL),
    CONSTRAINT ck_student_grade_grade       CHECK(grade >= 6 AND grade <= 10)
);