DatabaseCreation: DDL_VP.sql

CREATE TABLE Academic_program (
  code       varchar(255) NOT NULL, 
  name       varchar(255) NOT NULL, 
  created_at date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  updated_at date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  PRIMARY KEY (code))
;

CREATE TABLE Aggregated_Course_Edition_Results (
  id                      SERIAL NOT NULL, 
  TeachesTeacherMemberid  int4 NOT NULL, 
  TeachesCourse_Editionid int4 NOT NULL, 
  exported_at             timestamp NOT NULL, 
  data_jsonb              int4 NOT NULL, 
  PRIMARY KEY (id))
;

CREATE TABLE Announcement (
  id                      int4 NOT NULL, 
  TeachesTeacherMemberid2 int4 NOT NULL, 
  TeachesCourse_Editionid int4 NOT NULL, 
  message                 varchar(255) NOT NULL, 
  sent_at                 date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  PRIMARY KEY (id, 
  TeachesTeacherMemberid2, 
  TeachesCourse_Editionid))
;

CREATE TABLE Announcement_Replies (
  Announcementid                       int4 NOT NULL, 
  Announcementid2                      int4 NOT NULL, 
  AnnouncementTeachesTeacherMemberid22 int4 NOT NULL, 
  AnnouncementTeachesTeacherMemberid23 int4 NOT NULL, 
  AnnouncementTeachesCourse_Editionid  int4 NOT NULL, 
  AnnouncementTeachesCourse_Editionid2 int4 NOT NULL, 
  PRIMARY KEY (Announcementid, 
  Announcementid2, 
  AnnouncementTeachesTeacherMemberid22, 
  AnnouncementTeachesTeacherMemberid23, 
  AnnouncementTeachesCourse_Editionid, 
  AnnouncementTeachesCourse_Editionid2))
;

CREATE TABLE Course (
  code       varchar(255) NOT NULL, 
  name       varchar(255) NOT NULL, 
  credits    int4 NOT NULL CHECK(credits > 0), 
  created_at date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  PRIMARY KEY (code))
;

CREATE TABLE Course_Edition (
  id            SERIAL NOT NULL, 
  Coursecode    varchar(255) NOT NULL, 
  academic_year int4 NOT NULL, 
  semester      int4 NOT NULL, 
  PRIMARY KEY (id))
;

CREATE TABLE Course_Enrollment (
  id                    SERIAL NOT NULL, 
  Semester_Enrollmentid int4 NOT NULL, 
  Course_Editionid      int4 NOT NULL, 
  PRIMARY KEY (id))
;

CREATE TABLE Course_Equivalence (
  Coursecode  varchar(255) NOT NULL, 
  Coursecode2 varchar(255) NOT NULL, 
  PRIMARY KEY (Coursecode, 
  Coursecode2))
;

CREATE TABLE Course_Prerequisite (
  Coursecode  varchar(255) NOT NULL, 
  Coursecode2 varchar(255) NOT NULL, 
  PRIMARY KEY (Coursecode, 
  Coursecode2), 
  CONSTRAINT COURSE_CANNOT_BE_ITS_OWN_PREREQUISITE 
    CHECK (course_code <> prerequisite_code))
;

COMMENT ON CONSTRAINT COURSE_CANNOT_BE_ITS_OWN_PREREQUISITE ON Course_Prerequisite IS 'A course cannot be its own prerequisite.'
;

CREATE TABLE Curriculum (
  Academic_programcode varchar(255) NOT NULL, 
  Coursecode           varchar(255) NOT NULL, 
  semester             int4 NOT NULL CHECK(semester BETWEEN 1 AND 8), 
  mandatory            bytea DEFAULT 'FALSE' NOT NULL, 
  PRIMARY KEY (Academic_programcode, 
  Coursecode))
;

CREATE TABLE Exam (
  id               SERIAL NOT NULL, 
  Course_Editionid int4 NOT NULL, 
  scheduled_at     date, 
  duration_minutes int4 CHECK(duration_minutes > 0), 
  PRIMARY KEY (id))
;

CREATE TABLE Exam_Attempt (
  attempt_number      int4 NOT NULL CHECK(attempt_number > 0), 
  Examid              int4 NOT NULL, 
  Course_Enrollmentid int4 NOT NULL, 
  total_points        float4 CHECK(total_points >= 0), 
  submitted_at        date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  PRIMARY KEY (attempt_number, 
  Examid, 
  Course_Enrollmentid))
;

CREATE TABLE Exam_Problem (
  pid         int4 NOT NULL, 
  Examid      int4 NOT NULL, 
  description varchar(255) NOT NULL, 
  expected    varchar(255) NOT NULL, 
  points      float4 NOT NULL CHECK(points > 0), 
  PRIMARY KEY (pid, 
  Examid))
;

CREATE TABLE Exam_Results (
  id                      SERIAL NOT NULL, 
  Examid                  int4 NOT NULL, 
  TeachesTeacherMemberid  int4 NOT NULL, 
  TeachesCourse_Editionid int4 NOT NULL, 
  exported_at             timestamp NOT NULL, 
  data_jsonb              int4 NOT NULL, 
  PRIMARY KEY (id))
;

CREATE TABLE Exercise (
  id               SERIAL NOT NULL, 
  Course_Editionid int4 NOT NULL, 
  title            varchar(255) NOT NULL, 
  task             varchar(255) NOT NULL, 
  expected_result  varchar(255) NOT NULL, 
  deadline         date, 
  max_grade        int4 DEFAULT 5 NOT NULL CHECK(max_grade > 0), 
  created_at       date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  PRIMARY KEY (id))
;

CREATE TABLE Exercise_Submission (
  id                  int4 NOT NULL, 
  Exerciseid          int4 NOT NULL, 
  Course_Enrollmentid int4 NOT NULL, 
  content             varchar(255) NOT NULL, 
  grade               int4 CHECK(grade BETWEEN 1 AND 10), 
  submitted_at        date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  graded_at           date, 
  PRIMARY KEY (id, 
  Exerciseid, 
  Course_Enrollmentid))
;

CREATE TABLE Lecture (
  id               SERIAL NOT NULL, 
  Course_Editionid int4 NOT NULL, 
  title            varchar(255) NOT NULL, 
  content_url      varchar(255), 
  created_at       date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  PRIMARY KEY (id))
;

CREATE TABLE Member (
  id            SERIAL NOT NULL, 
  name          varchar(255) NOT NULL, 
  surname       varchar(255) NOT NULL, 
  username      varchar(255) NOT NULL, 
  password_hash varchar(255) NOT NULL, 
  date_birth    date NOT NULL, 
  created_at    date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  updated_at    date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  PRIMARY KEY (id))
;

CREATE TABLE Member_Message (
  id        SERIAL NOT NULL, 
  message   varchar(255) NOT NULL, 
  sent_at   date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  Memberid  int4 NOT NULL, 
  Memberid2 int4 NOT NULL, 
  PRIMARY KEY (id))
;

CREATE TABLE Program_Enrollment (
  id                   SERIAL NOT NULL, 
  StudentMemberid      int4 NOT NULL UNIQUE, 
  Academic_programcode varchar(255) NOT NULL UNIQUE, 
  date_enrollment      date NOT NULL UNIQUE, 
  date_disenrollment   date, 
  PRIMARY KEY (id), 
  CONSTRAINT CNS_disenrollment_after_enrollment 
    CHECK (date_enrollment < date_disenrollment))
;

COMMENT ON CONSTRAINT CNS_disenrollment_after_enrollment ON Program_Enrollment IS 'Date of disenrollment has to be after the date of enrollment (for a specific academic program).'
;

COMMENT ON TABLE Program_Enrollment IS 'date_enrollment is a part of primary key, because e.g. a student may enroll in program A, then disenroll from it (change program), and then enroll in program A again (only the dates are different).'
;

CREATE TABLE Semester_Enrollment (
  id                   SERIAL NOT NULL, 
  Program_Enrollmentid int4 NOT NULL, 
  academic_year        int4 NOT NULL CHECK(academic_year >= 2000), 
  semester             int4 NOT NULL CHECK(semester BETWEEN 1 AND 2), 
  prev_hash            varchar(255), 
  current_hash         varchar(255) DEFAULT '' NOT NULL, 
  PRIMARY KEY (id))
;

CREATE TABLE Student (
  Memberid int4 NOT NULL, 
  "index"  varchar(255) NOT NULL UNIQUE, 
  PRIMARY KEY (Memberid))
;

CREATE TABLE Student_Answer (
  answer                          varchar(255) NOT NULL, 
  points_acquired                 float4 NOT NULL CHECK(points_acquired >= 0), 
  Exam_Problempid                 int4 NOT NULL, 
  Exam_Attemptattempt_number      int4 NOT NULL, 
  Exam_AttemptCourse_Enrollmentid int4 NOT NULL, 
  Exam_AttemptExamid2             int4 NOT NULL, 
  PRIMARY KEY (Exam_Problempid, 
  Exam_Attemptattempt_number, 
  Exam_AttemptCourse_Enrollmentid, 
  Exam_AttemptExamid2))
;

CREATE TABLE Student_Grade (
  grade                               int4 NOT NULL CHECK(grade BETWEEN 6 AND 10), 
  graded_at                           date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  Course_Enrollmentid                 int4 NOT NULL, 
  Aggregated_Course_Edition_Resultsid int4 NOT NULL, 
  PRIMARY KEY (Course_Enrollmentid))
;

CREATE TABLE Survey (
  id               SERIAL NOT NULL, 
  Course_Editionid int4 NOT NULL, 
  title            varchar(255) NOT NULL, 
  open_until       date, 
  PRIMARY KEY (id))
;

CREATE TABLE Survey_Option (
  "option" varchar(255) NOT NULL, 
  Surveyid int4 NOT NULL, 
  capacity int4 CHECK(capacity > 0), 
  PRIMARY KEY ("option", 
  Surveyid))
;

CREATE TABLE Survey_Response (
  option_text         varchar(255) NOT NULL, 
  Surveyid            int4 NOT NULL, 
  Course_Enrollmentid int4 NOT NULL, 
  responded_at        date DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  PRIMARY KEY (option_text, 
  Surveyid, 
  Course_Enrollmentid))
;

CREATE TABLE Teacher (
  Memberid          int4 NOT NULL, 
  date_registration date DEFAULT CURRENT_DATE NOT NULL, 
  PRIMARY KEY (Memberid))
;

CREATE TABLE Teaches (
  TeacherMemberid  int4 NOT NULL, 
  Course_Editionid int4 NOT NULL, 
  PRIMARY KEY (TeacherMemberid, 
  Course_Editionid))
;

CREATE TABLE Teaching_Assistant (
  id              int4 NOT NULL, 
  StudentMemberid int4 NOT NULL, 
  TeacherMemberid int4 NOT NULL, 
  PRIMARY KEY (id, 
  StudentMemberid, 
  TeacherMemberid))
;

ALTER TABLE Student ADD CONSTRAINT FKStudent782306 FOREIGN KEY (Memberid) REFERENCES Member (id)
;

ALTER TABLE Teacher ADD CONSTRAINT FKTeacher351553 FOREIGN KEY (Memberid) REFERENCES Member (id)
;

ALTER TABLE Teaches ADD CONSTRAINT FKTeaches706203 FOREIGN KEY (TeacherMemberid) REFERENCES Teacher (Memberid)
;

ALTER TABLE Teaches ADD CONSTRAINT FKTeaches334626 FOREIGN KEY (Course_Editionid) REFERENCES Course_Edition (id)
;

ALTER TABLE Course_Enrollment ADD CONSTRAINT FKCourse_Enr486954 FOREIGN KEY (Course_Editionid) REFERENCES Course_Edition (id)
;

ALTER TABLE Member_Message ADD CONSTRAINT FKMember_Mes36195 FOREIGN KEY (Memberid) REFERENCES Member (id)
;

ALTER TABLE Member_Message ADD CONSTRAINT FKMember_Mes61215 FOREIGN KEY (Memberid2) REFERENCES Member (id)
;

ALTER TABLE Program_Enrollment ADD CONSTRAINT FKProgram_En889357 FOREIGN KEY (StudentMemberid) REFERENCES Student (Memberid)
;

ALTER TABLE Program_Enrollment ADD CONSTRAINT FKProgram_En384401 FOREIGN KEY (Academic_programcode) REFERENCES Academic_program (code)
;

ALTER TABLE Course_Equivalence ADD CONSTRAINT FKCourse_Equ56181 FOREIGN KEY (Coursecode) REFERENCES Course (code)
;

ALTER TABLE Course_Equivalence ADD CONSTRAINT FKCourse_Equ535651 FOREIGN KEY (Coursecode2) REFERENCES Course (code)
;

ALTER TABLE Announcement ADD CONSTRAINT FKAnnounceme407182 FOREIGN KEY (TeachesTeacherMemberid2, TeachesCourse_Editionid) REFERENCES Teaches (TeacherMemberid, Course_Editionid)
;

ALTER TABLE Exam_Results ADD CONSTRAINT FKExam_Resul860551 FOREIGN KEY (Examid) REFERENCES Exam (id)
;

ALTER TABLE Exam_Results ADD CONSTRAINT FKExam_Resul975811 FOREIGN KEY (TeachesTeacherMemberid, TeachesCourse_Editionid) REFERENCES Teaches (TeacherMemberid, Course_Editionid)
;

ALTER TABLE Aggregated_Course_Edition_Results ADD CONSTRAINT FKAggregated438385 FOREIGN KEY (TeachesTeacherMemberid, TeachesCourse_Editionid) REFERENCES Teaches (TeacherMemberid, Course_Editionid)
;

ALTER TABLE Student_Grade ADD CONSTRAINT FKStudent_Gr234275 FOREIGN KEY (Aggregated_Course_Edition_Resultsid) REFERENCES Aggregated_Course_Edition_Results (id)
;

ALTER TABLE Student_Answer ADD CONSTRAINT answers FOREIGN KEY (Exam_Attemptattempt_number, Exam_AttemptExamid2, Exam_AttemptCourse_Enrollmentid) REFERENCES Exam_Attempt (attempt_number, Examid, Course_Enrollmentid)
;

ALTER TABLE Student_Answer ADD CONSTRAINT answers FOREIGN KEY (Exam_Problempid, Exam_AttemptExamid2) REFERENCES Exam_Problem (pid, Examid)
;

ALTER TABLE Teaching_Assistant ADD CONSTRAINT assists FOREIGN KEY (TeacherMemberid) REFERENCES Teacher (Memberid)
;

ALTER TABLE Teaching_Assistant ADD CONSTRAINT assists FOREIGN KEY (StudentMemberid) REFERENCES Student (Memberid)
;

ALTER TABLE Exam_Attempt ADD CONSTRAINT attempts FOREIGN KEY (Course_Enrollmentid) REFERENCES Course_Enrollment (id)
;

ALTER TABLE Exam_Attempt ADD CONSTRAINT attempts FOREIGN KEY (Examid) REFERENCES Exam (id)
;

ALTER TABLE Curriculum ADD CONSTRAINT "contains" FOREIGN KEY (Academic_programcode) REFERENCES Academic_program (code)
;

ALTER TABLE Curriculum ADD CONSTRAINT "contains" FOREIGN KEY (Coursecode) REFERENCES Course (code)
;

ALTER TABLE Exam_Problem ADD CONSTRAINT "contains" FOREIGN KEY (Examid) REFERENCES Exam (id)
;

ALTER TABLE Student_Grade ADD CONSTRAINT "graded in" FOREIGN KEY (Course_Enrollmentid) REFERENCES Course_Enrollment (id)
;

ALTER TABLE Course_Edition ADD CONSTRAINT "has edition" FOREIGN KEY (Coursecode) REFERENCES Course (code)
;

ALTER TABLE Semester_Enrollment ADD CONSTRAINT "has enrollment" FOREIGN KEY (Program_Enrollmentid) REFERENCES Program_Enrollment (id)
;

ALTER TABLE Exam ADD CONSTRAINT "has exam" FOREIGN KEY (Course_Editionid) REFERENCES Course_Edition (id)
;

ALTER TABLE Exercise ADD CONSTRAINT "has exercise" FOREIGN KEY (Course_Editionid) REFERENCES Course_Edition (id)
;

ALTER TABLE Lecture ADD CONSTRAINT "has lecture" FOREIGN KEY (Course_Editionid) REFERENCES Course_Edition (id)
;

ALTER TABLE Survey_Option ADD CONSTRAINT "has option" FOREIGN KEY (Surveyid) REFERENCES Survey (id)
;

ALTER TABLE Announcement_Replies ADD CONSTRAINT "has reply" FOREIGN KEY (Announcementid, AnnouncementTeachesTeacherMemberid22, AnnouncementTeachesCourse_Editionid) REFERENCES Announcement (id, TeachesTeacherMemberid2, TeachesCourse_Editionid)
;

ALTER TABLE Survey_Response ADD CONSTRAINT "has response" FOREIGN KEY (Surveyid) REFERENCES Survey (id)
;

ALTER TABLE Survey ADD CONSTRAINT "has survey" FOREIGN KEY (Course_Editionid) REFERENCES Course_Edition (id)
;

ALTER TABLE Course_Enrollment ADD CONSTRAINT includes FOREIGN KEY (Semester_Enrollmentid) REFERENCES Semester_Enrollment (id)
;

ALTER TABLE Announcement_Replies ADD CONSTRAINT "replies to" FOREIGN KEY (Announcementid2, AnnouncementTeachesTeacherMemberid23, AnnouncementTeachesCourse_Editionid2) REFERENCES Announcement (id, TeachesTeacherMemberid2, TeachesCourse_Editionid)
;

ALTER TABLE Course_Prerequisite ADD CONSTRAINT required FOREIGN KEY (Coursecode2) REFERENCES Course (code)
;

ALTER TABLE Course_Prerequisite ADD CONSTRAINT requires FOREIGN KEY (Coursecode) REFERENCES Course (code)
;

ALTER TABLE Survey_Response ADD CONSTRAINT "responded to" FOREIGN KEY (Course_Enrollmentid) REFERENCES Course_Enrollment (id)
;

ALTER TABLE Exercise_Submission ADD CONSTRAINT submits FOREIGN KEY (Course_Enrollmentid) REFERENCES Course_Enrollment (id)
;

ALTER TABLE Exercise_Submission ADD CONSTRAINT "submitted for" FOREIGN KEY (Exerciseid) REFERENCES Exercise (id)
;