CREATE TABLE t_Member ( created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP, id INTEGER GENERATED ALWAYS AS IDENTITY, date_birth DATE, is_active BOOLEAN DEFAULT TRUE, name TEXT, surname TEXT, username TEXT, password_hash TEXT, 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 t_Member_Message ( sent_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP, id INTEGER GENERATED ALWAYS AS IDENTITY, member_id1 INTEGER, member_id2 INTEGER, message TEXT, CONSTRAINT pk_member_message PRIMARY KEY(id, member_id1, member_id2), CONSTRAINT fk_member_message_member_id1 FOREIGN KEY(member_id1) REFERENCES t_Member (id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_member_message_member_id2 FOREIGN KEY(member_id2) REFERENCES t_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 t_Student ( id INTEGER, index TEXT, CONSTRAINT pk_student PRIMARY KEY(id), CONSTRAINT fk_student_id FOREIGN KEY(id) REFERENCES t_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 t_Teacher ( id INTEGER, date_registration DATE, CONSTRAINT pk_teacher PRIMARY KEY(id), CONSTRAINT fk_teacher FOREIGN KEY(id) REFERENCES t_Member (id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT nn_teacher_date_registration CHECK(date_registration IS NOT NULL) ); CREATE TABLE t_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 t_Student(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_teaching_assistant_teacher_id FOREIGN KEY(teacher_id) REFERENCES t_Teacher(id) ON DELETE SET NULL ON UPDATE CASCADE ); CREATE TABLE __t_Academic_Program ( created_at DATE, updated_at DATE, numeric_id SMALLINT, duration_years UDV_ACADEMIC_PROGRAM_DURATION_YEARS, code TEXT, name TEXT, 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) ); CREATE TABLE t_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 t_Student(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_program_enrollment_academic_program_code FOREIGN KEY(academic_program_code) REFERENCES __t_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 t_Course ( credits INT, created_at DATE, code TEXT, name TEXT, 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 __t_Curriculum ( academic_program_code TEXT, course_code TEXT, semester UDV_CURRICULUM_SEMESTER, mandatory BOOLEAN, CONSTRAINT pk_curriculum PRIMARY KEY(academic_program_code, course_code), CONSTRAINT fk_curriculum_academic_program_code FOREIGN KEY(academic_program_code) REFERENCES __t_Academic_Program(code) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_curriculum_course_code FOREIGN KEY(course_code) REFERENCES t_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 t_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 t_Course(code) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_course_prerequisite_successor_course_code FOREIGN KEY(successor_course_code) REFERENCES t_Course(code) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT ck_course_prerequisite_different_courses CHECK(predecessor_course_code <> successor_course_code) ); CREATE TABLE t_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 t_Course(code) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_course_equivalence_course2_code FOREIGN KEY(course2_code) REFERENCES t_Course(code) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT ck_course_equivalence_different_courses CHECK(course1_code <> course2_code) ); CREATE TABLE __t_Course_Edition ( id INTEGER GENERATED ALWAYS AS IDENTITY, academic_year SMALLINT, semester UDV_COURSE_EDITION_SEMESTER, course_code TEXT, CONSTRAINT pk_course_edition PRIMARY KEY(id), CONSTRAINT fk_course_edition FOREIGN KEY(course_code) REFERENCES t_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) ); CREATE TABLE t_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 t_Teacher(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_teaches_course_edition_id FOREIGN KEY(course_edition_id) REFERENCES __t_Course_Edition(id) ON DELETE SET NULL ON UPDATE CASCADE ); CREATE TABLE t_Announcement ( sent_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP, id INTEGER GENERATED ALWAYS AS IDENTITY, teaches_teacher_id INTEGER, teaches_course_edition_id INTEGER, message TEXT, 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 t_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 t_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 t_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 t_Announcement(id, teaches_teacher_id, teaches_course_edition_id) ON DELETE SET NULL ON UPDATE CASCADE, ); CREATE TABLE t_Lecture ( created_at TIMESTAMP WITHOUT TIME ZONE, id INTEGER GENERATED ALWAYS AS IDENTITY, course_edition_id INTEGER, title TEXT, content_url TEXT, CONSTRAINT pk_lecture PRIMARY KEY(id), CONSTRAINT fk_lecture FOREIGN KEY(course_edition_id) REFERENCES __t_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 __t_Semester_Enrollment ( id INTEGER GENERATED ALWAYS AS IDENTITY, program_enrollment_id INTEGER, academic_year SMALLINT, semester UDV_SEMESTER_ENROLLMENT_SEMESTER, CONSTRAINT pk_semester_enrollment PRIMARY KEY(id), CONSTRAINT fk_semester_enrollment FOREIGN KEY(program_enrollment_id) REFERENCES t_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) ); CREATE TABLE t_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 __t_Semester_Enrollment(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_course_enrollment_ceid FOREIGN KEY(course_edition_id) REFERENCES __t_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 t_Exercise ( id INTEGER GENERATED ALWAYS AS IDENTITY, course_edition_id INTEGER, deadline DATE, max_grade INTEGER, created_at DATE, title TEXT, task TEXT, expected_result TEXT, CONSTRAINT pk_exercise PRIMARY KEY(id), CONSTRAINT fk_exercise FOREIGN KEY(course_edition_id) REFERENCES __t_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 t_Exercise_Submission ( submitted_at TIMESTAMP WITHOUT TIME ZONE, graded_at TIMESTAMP WITHOUT TIME ZONE, grade INTEGER, id INTEGER GENERATED ALWAYS AS IDENTITY, exercise_id INTEGER, course_enrollment_id INTEGER, content TEXT, CONSTRAINT pk_exercise_submission PRIMARY KEY(id, exercise_id, course_enrollment_id), CONSTRAINT fk_exercise_submission_exercise_id FOREIGN KEY(exercise_id) REFERENCES t_Exercise(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_exercise_submission_ceid FOREIGN KEY(course_enrollment_id) REFERENCES t_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 t_Survey ( open_until TIMESTAMP WITHOUT TIME ZONE, id INTEGER GENERATED ALWAYS AS IDENTITY, course_edition_id INTEGER, title TEXT, CONSTRAINT pk_survey PRIMARY KEY(id), CONSTRAINT fk_survey FOREIGN KEY(course_edition_id) REFERENCES __t_Course_Edition(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT nn_survey_title CHECK(title IS NOT NULL) ); CREATE TABLE t_Survey_Option ( survey_id INTEGER, capacity INTEGER, option TEXT, CONSTRAINT pk_survey_option PRIMARY KEY(option, survey_id), CONSTRAINT fk_survey_option FOREIGN KEY(survey_id) REFERENCES t_Survey(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT ck_survey_option_capacity CHECK(capacity > 0) ); CREATE TABLE t_Survey_Response ( responded_at TIMESTAMP WITHOUT TIME ZONE, survey_id INTEGER, course_enrollment_id INTEGER, option_text TEXT, CONSTRAINT pk_survey_response PRIMARY KEY(option_text, survey_id, course_enrollment_id), CONSTRAINT fk_survey_response_survey_id FOREIGN KEY(survey_id) REFERENCES t_Survey(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_survey_response_ceid FOREIGN KEY(course_enrollment_id) REFERENCES t_Course_Enrollment(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT nn_survey_response_responded_at CHECK(responded_at IS NOT NULL) ); CREATE TABLE __t_Student_Grade ( course_enrollment_id INTEGER, graded_at DATE, aggr_course_edition_res_id INTEGER, grade UDV_STUDENT_GRADE_GRADE, CONSTRAINT pk_student_grade PRIMARY KEY(course_enrollment_id), CONSTRAINT fk_student_grade FOREIGN KEY(course_enrollment_id) REFERENCES t_Course_Enrollment(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_student_aceri FOREIGN KEY(aggr_course_edition_res_id) REFERENCES t_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) ); CREATE TABLE t_Exam ( scheduled_at TIMESTAMP WITHOUT TIME ZONE, id INTEGER GENERATED ALWAYS AS IDENTITY, course_edition_id INTEGER, duration_minutes SMALLINT, type EXAM_TYPE, CONSTRAINT pk_exam PRIMARY KEY(id), CONSTRAINT fk_exam FOREIGN KEY(course_edition_id) REFERENCES __t_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 t_Exam_Attempt ( submitted_at TIMESTAMP WITHOUT TIME ZONE, exam_id INTEGER, course_enrollment_id INTEGER, total_points FLOAT, attempt_number SMALLINT, CONSTRAINT pk_exam_attempt PRIMARY KEY(attempt_number, exam_id, course_enrollment_id), CONSTRAINT fk_exam_attempt_exam_id FOREIGN KEY(exam_id) REFERENCES t_Exam(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_exam_attempt_ceid FOREIGN KEY(course_enrollment_id) REFERENCES t_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 t_Exam_Problem ( id INTEGER GENERATED ALWAYS AS IDENTITY, exam_id INTEGER, points FLOAT, description TEXT, expected TEXT, CONSTRAINT pk_exam_problem PRIMARY KEY(id, exam_id), CONSTRAINT fk_exam_problem FOREIGN KEY(exam_id) REFERENCES t_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 t_Student_Answer ( exam_id INTEGER, exam_problem_id INTEGER, exam_attempt_ceid INTEGER, points_acquired FLOAT, exam_attempt_attempt_number SMALLINT, answer TEXT, 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 t_Exam(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_student_answer_exam_problem FOREIGN KEY(exam_id, exam_problem_id) REFERENCES t_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 t_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 t_Exam_Results ( exported_at TIMESTAMP WITHOUT TIME ZONE, teaches_teacher_id INTEGER, teaches_course_edition_id INTEGER, exam_id INTEGER, id INTEGER GENERATED ALWAYS AS IDENTITY, data jsonb, CONSTRAINT pk_exam_results PRIMARY KEY (id), CONSTRAINT fk_exam_results_teaches FOREIGN KEY (teaches_teacher_id, teaches_course_edition_id) REFERENCES t_Teaches (teacher_id, course_edition_id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_exam_results_exam FOREIGN KEY (exam_id) REFERENCES t_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 t_Aggregated_Course_Edition_Results ( exported_at TIMESTAMP WITHOUT TIME ZONE, teaches_teacher_id INTEGER, teaches_course_edition_id INTEGER, id INTEGER GENERATED ALWAYS AS IDENTITY, data jsonb, CONSTRAINT pk_aggregated_course_edition_results PRIMARY KEY (id), CONSTRAINT fk_aggregated_results_teaches FOREIGN KEY (teaches_teacher_id, teaches_course_edition_id) REFERENCES t_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)) );