DatabaseCreation: DDL_OSDM.sql

-- Generated by Oracle SQL Developer Data Modeler 24.3.1.351.0831
--   at:        2026-07-01 16:46:21 CEST
--   site:      Oracle Database 21c
--   type:      Oracle Database 21c



-- predefined type, no DDL - MDSYS.SDO_GEOMETRY

-- predefined type, no DDL - XMLTYPE

CREATE TABLE Academic_Program 
    ( 
     code       VARCHAR2 (10)  NOT NULL , 
     name       CLOB  NOT NULL , 
     created_at TIMESTAMP WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP  NOT NULL , 
     updated_at TIMESTAMP WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP  NOT NULL 
    ) 
;

COMMENT ON COLUMN Academic_Program.code IS 'Used VARCHAR(10) instead of TEXT due to the following reason:
-Oracle SQL Developer Data Modeler does not allow a TEXT field to be a primary key.' 
;

ALTER TABLE Academic_Program 
    ADD CONSTRAINT Academic_Program_PK PRIMARY KEY ( code ) ;

--  ERROR: Table name length exceeds maximum allowed length(30) 
CREATE TABLE Aggregated_Course_Edition_Results 
    ( 
     exported_at                          TIMESTAMP  NOT NULL , 
     data_jsonb                           JSON  NOT NULL , 
--  ERROR: Column name length exceeds maximum allowed length(30) 
     Aggregated_Course_Edition_Results_ID NUMBER  NOT NULL , 
     Teaches_Teaches_ID                   NUMBER  NOT NULL 
    ) 
;

--  ERROR: PK name length exceeds maximum allowed length(30) 
ALTER TABLE Aggregated_Course_Edition_Results 
    ADD CONSTRAINT Aggregated_Course_Edition_Results_PK PRIMARY KEY ( Aggregated_Course_Edition_Results_ID ) ;

CREATE TABLE Announcement 
    ( 
     id                     INTEGER  NOT NULL , 
     message                CLOB  NOT NULL , 
     sent_at                TIMESTAMP WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP  NOT NULL , 
     parent_announcement_id INTEGER DEFAULT NULL , 
     Teaches_Teaches_ID     NUMBER  NOT NULL 
    ) 
;

ALTER TABLE Announcement 
    ADD CONSTRAINT Announcement_PK PRIMARY KEY ( id ) ;

CREATE TABLE Course 
    ( 
     code       VARCHAR2 (10 BYTE)  NOT NULL , 
     name       CLOB  NOT NULL , 
     credits    INTEGER  NOT NULL , 
     created_at TIMESTAMP WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP  NOT NULL 
    ) 
;

ALTER TABLE Course 
    ADD 
    CHECK (credits > 0) 
;

COMMENT ON COLUMN Course.code IS 'Used VARCHAR(10) instead of TEXT due to the following reason:
-Oracle SQL Developer Data Modeler does not allow a TEXT field to be a primary key.' 
;

ALTER TABLE Course 
    ADD CONSTRAINT Course_PK PRIMARY KEY ( code ) ;

CREATE TABLE Course_Edition 
    ( 
     Course_code   VARCHAR2 (10 BYTE)  NOT NULL , 
     academic_year INTEGER  NOT NULL , 
     semester      INTEGER  NOT NULL 
    ) 
;

ALTER TABLE Course_Edition 
    ADD 
    CHECK (academic_year >= 2000) 
;

ALTER TABLE Course_Edition 
    ADD 
    CHECK (semester BETWEEN 1 AND 2) 
;

ALTER TABLE Course_Edition 
    ADD CONSTRAINT Course_Edition_PK PRIMARY KEY ( academic_year, Course_code, semester ) ;

CREATE TABLE Course_Enrollment 
    ( 
     Semester_Enrollment_id       INTEGER  NOT NULL , 
     Course_Edition_academic_year INTEGER  NOT NULL , 
     Course_Edition_course_code   VARCHAR2 (10 BYTE)  NOT NULL , 
     Course_Edition_semester      INTEGER  NOT NULL , 
     Course_Enrollment_ID         NUMBER  NOT NULL 
    ) 
;

ALTER TABLE Course_Enrollment 
    ADD CONSTRAINT Course_Enrollment_PK PRIMARY KEY ( Course_Enrollment_ID ) ;

CREATE TABLE COURSE_EQUIVALENCE 
    ( 
     Course_code  VARCHAR2 (10 BYTE)  NOT NULL , 
     Course_code1 VARCHAR2 (10 BYTE)  NOT NULL 
    ) 
;

ALTER TABLE COURSE_EQUIVALENCE 
    ADD CONSTRAINT COURSE_EQUIVALENCE_PK PRIMARY KEY ( Course_code, Course_code1 ) ;

CREATE TABLE COURSE_PREREQUISITE 
    ( 
     Course_code       VARCHAR2 (10 BYTE)  NOT NULL , 
     Course_code1      VARCHAR2 (10 BYTE)  NOT NULL , 
     course_code2      VARCHAR2 (10 BYTE)  NOT NULL , 
     prerequisite_code VARCHAR2 (10 BYTE)  NOT NULL 
    ) 
;

ALTER TABLE COURSE_PREREQUISITE 
    ADD 
    CHECK (course_code <> prerequisite_code) 
;

ALTER TABLE COURSE_PREREQUISITE 
    ADD 
    CHECK (course_code <> prerequisite_code) 
;

ALTER TABLE COURSE_PREREQUISITE 
    ADD CONSTRAINT COURSE_PREREQUISITE_PK PRIMARY KEY ( Course_code, Course_code1 ) ;

CREATE TABLE Curriculum 
    ( 
     Academic_Program_code VARCHAR2 (10)  NOT NULL , 
     Course_code           VARCHAR2 (10 BYTE)  NOT NULL , 
     semester              INTEGER  NOT NULL , 
     mandatory             NUMBER DEFAULT FALSE  NOT NULL 
    ) 
;

ALTER TABLE Curriculum 
    ADD 
    CHECK (semester BETWEEN 1 AND 8) 
;

ALTER TABLE Curriculum 
    ADD CONSTRAINT Curriculum_PK PRIMARY KEY ( Academic_Program_code, Course_code ) ;

CREATE TABLE Exam 
    ( 
     id                           INTEGER  NOT NULL , 
     Course_Edition_academic_year INTEGER  NOT NULL , 
     Course_Edition_Course_code   VARCHAR2 (10 BYTE)  NOT NULL , 
     Course_Edition_semester      INTEGER  NOT NULL , 
     scheduled_at                 TIMESTAMP WITH LOCAL TIME ZONE , 
     duration_minutes             INTEGER 
    ) 
;

ALTER TABLE Exam 
    ADD 
    CHECK (duration_minutes > 0) 
;

ALTER TABLE Exam 
    ADD CONSTRAINT Exam_PK PRIMARY KEY ( id ) ;

CREATE TABLE Exam_Attempt 
    ( 
     id                                     INTEGER  NOT NULL , 
     Exam_id                                INTEGER  NOT NULL , 
     attempt_number                         INTEGER  NOT NULL , 
     total_points                           FLOAT , 
     submitted_at                           TIMESTAMP WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP  NOT NULL , 
--  ERROR: Column name length exceeds maximum allowed length(30) 
     Course_Enrollment_Course_Enrollment_ID NUMBER  NOT NULL 
    ) 
;

ALTER TABLE Exam_Attempt 
    ADD 
    CHECK (attempt_number > 0) 
;

ALTER TABLE Exam_Attempt 
    ADD 
    CHECK (total_points >= 0) 
;

ALTER TABLE Exam_Attempt 
    ADD CONSTRAINT Exam_Attempt_PK PRIMARY KEY ( id ) ;

CREATE TABLE Exam_Problem 
    ( 
     pid         INTEGER  NOT NULL , 
     Exam_id     INTEGER  NOT NULL , 
     description CLOB  NOT NULL , 
     expected    CLOB  NOT NULL , 
     points      FLOAT  NOT NULL 
    ) 
;

ALTER TABLE Exam_Problem 
    ADD 
    CHECK (points > 0) 
;

ALTER TABLE Exam_Problem 
    ADD CONSTRAINT Exam_Problem_PK PRIMARY KEY ( pid, Exam_id ) ;

CREATE TABLE Exam_Results 
    ( 
     exported_at        TIMESTAMP  NOT NULL , 
     data_jsonb         JSON  NOT NULL , 
     Exam_id            INTEGER  NOT NULL , 
     Teaches_Teaches_ID NUMBER  NOT NULL 
    ) 
;

ALTER TABLE Exam_Results 
    ADD CONSTRAINT Exam_Results_PK PRIMARY KEY ( Exam_id ) ;

CREATE TABLE Exercise 
    ( 
     id                           INTEGER  NOT NULL , 
     Course_Edition_academic_year INTEGER  NOT NULL , 
     Course_Edition_Course_code   VARCHAR2 (10 BYTE)  NOT NULL , 
     Course_Edition_semester      INTEGER  NOT NULL , 
     title                        CLOB  NOT NULL , 
     task                         CLOB  NOT NULL , 
     expected_result              CLOB  NOT NULL , 
     deadline                     TIMESTAMP WITH LOCAL TIME ZONE , 
     max_grade                    SMALLINT DEFAULT 10  NOT NULL , 
     created_at                   TIMESTAMP WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP  NOT NULL 
    ) 
;

ALTER TABLE Exercise 
    ADD 
    CHECK (max_grade > 0) 
;

ALTER TABLE Exercise 
    ADD CONSTRAINT Exercise_PK PRIMARY KEY ( id ) ;

CREATE TABLE Exercise_Submission 
    ( 
     id                                     INTEGER  NOT NULL , 
     Exercise_id                            INTEGER  NOT NULL , 
     submission                             CLOB  NOT NULL , 
     grade                                  SMALLINT , 
     submitted_at                           TIMESTAMP WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP  NOT NULL , 
     graded_at                              TIMESTAMP WITH LOCAL TIME ZONE , 
--  ERROR: Column name length exceeds maximum allowed length(30) 
     Course_Enrollment_Course_Enrollment_ID NUMBER  NOT NULL 
    ) 
;

ALTER TABLE Exercise_Submission 
    ADD 
    CHECK (grade BETWEEN 1 AND 10) 
;

ALTER TABLE Exercise_Submission 
    ADD CONSTRAINT Exercise_Submission_PK PRIMARY KEY ( id ) ;

CREATE TABLE Lecture 
    ( 
     id                           INTEGER  NOT NULL , 
     Course_Edition_academic_year INTEGER  NOT NULL , 
     Course_Edition_Course_code   VARCHAR2 (10 BYTE)  NOT NULL , 
     Course_Edition_semester      INTEGER  NOT NULL , 
     title                        CLOB  NOT NULL , 
     content_url                  CLOB , 
     created_at                   TIMESTAMP WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP  NOT NULL 
    ) 
;

ALTER TABLE Lecture 
    ADD CONSTRAINT Lecture_PK PRIMARY KEY ( id ) ;

CREATE TABLE Member 
    ( 
     id            INTEGER  NOT NULL , 
     name          CLOB  NOT NULL , 
     surname       CLOB  NOT NULL , 
     username      CLOB  NOT NULL , 
     password_hash CLOB  NOT NULL , 
     date_of_birth DATE  NOT NULL , 
     created_at    TIMESTAMP WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP  NOT NULL , 
     updated_at    TIMESTAMP WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP  NOT NULL 
    ) 
;

ALTER TABLE Member 
    ADD CONSTRAINT Member_PK PRIMARY KEY ( id ) ;

CREATE TABLE MEMBER_MESSAGE 
    ( 
     Member_id  INTEGER  NOT NULL , 
     Member_id1 INTEGER  NOT NULL , 
     message    CLOB  NOT NULL , 
     sent_at    TIMESTAMP WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP 
    ) 
;

ALTER TABLE MEMBER_MESSAGE 
    ADD CONSTRAINT MEMBER_MESSAGE_PK PRIMARY KEY ( Member_id, Member_id1 ) ;

CREATE TABLE Program_Enrollment 
    ( 
     Student_id            INTEGER  NOT NULL , 
     Academic_Program_code VARCHAR2 (10)  NOT NULL , 
     date_enrollment       DATE  NOT NULL , 
     date_disenrollment    DATE 
    ) 
;

ALTER TABLE Program_Enrollment 
    ADD CONSTRAINT Program_Enrollment_PK PRIMARY KEY ( Student_id, Academic_Program_code, date_enrollment ) ;

CREATE TABLE REPLIES_TO 
    ( 
     Announcement_id  INTEGER  NOT NULL , 
     Announcement_id1 INTEGER  NOT NULL 
    ) 
;

ALTER TABLE REPLIES_TO 
    ADD CONSTRAINT REPLIES_TO_PK PRIMARY KEY ( Announcement_id, Announcement_id1 ) ;

CREATE TABLE Semester_Enrollment 
    ( 
     id                                       INTEGER  NOT NULL , 
     academic_year                            INTEGER , 
     semester_enrollment                      INTEGER , 
     prev_hash                                CLOB , 
     current_hash                             CLOB DEFAULT ''  NOT NULL , 
     Program_Enrollment_id                    INTEGER  NOT NULL , 
--  ERROR: Column name length exceeds maximum allowed length(30) 
     Program_Enrollment_Academic_Program_code VARCHAR2 (10)  NOT NULL , 
--  ERROR: Column name length exceeds maximum allowed length(30) 
     Program_Enrollment_date_enrollment       DATE  NOT NULL 
    ) 
;

ALTER TABLE Semester_Enrollment 
    ADD 
    CHECK (academic_year >= 2000) 
;

ALTER TABLE Semester_Enrollment 
    ADD 
    CHECK (semester BETWEEN 1 AND 2) 
;

COMMENT ON COLUMN Semester_Enrollment.prev_hash IS 'The SCRAM-SHA-256 hash of the previous enrollment record for the same student.' 
;

COMMENT ON COLUMN Semester_Enrollment.current_hash IS 'The SCRAM-SHA-256 hash of the concatenation of the row’s data + prev_hash.' 
;

ALTER TABLE Semester_Enrollment 
    ADD CONSTRAINT Semester_Enrollment_PK PRIMARY KEY ( id ) ;

CREATE TABLE Student 
    ( 
     id          INTEGER  NOT NULL , 
     enrolled_at TIMESTAMP WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP  NOT NULL , 
     "index"     CLOB  NOT NULL 
    ) 
;

ALTER TABLE Student 
    ADD CONSTRAINT Student_PK PRIMARY KEY ( id ) ;

CREATE TABLE Student_Answer 
    ( 
     Exam_Attempt_id      INTEGER  NOT NULL , 
     Exam_Problem_pid     INTEGER  NOT NULL , 
     Exam_Problem_exam_id INTEGER  NOT NULL , 
     answer               CLOB , 
     points_acquired      FLOAT 
    ) 
;

ALTER TABLE Student_Answer 
    ADD 
    CHECK (points_acquired >= 0) 
;

ALTER TABLE Student_Answer 
    ADD CONSTRAINT Student_Answer_PK PRIMARY KEY ( Exam_Problem_pid, Exam_Problem_exam_id, Exam_Attempt_id ) ;

CREATE TABLE Student_Grade 
    ( 
     grade                                                                  INTEGER , 
     graded_at                                                              TIMESTAMP WITH LOCAL TIME ZONE , 
--  ERROR: Column name length exceeds maximum allowed length(30) 
     Course_Enrollment_Course_Enrollment_ID                                 NUMBER  NOT NULL , 
--  ERROR: Column name length exceeds maximum allowed length(30) 
     Aggregated_Course_Edition_Results_Aggregated_Course_Edition_Results_ID NUMBER  NOT NULL 
    ) 
;

CREATE TABLE Survey 
    ( 
     id                           INTEGER  NOT NULL , 
     Course_Edition_academic_year INTEGER  NOT NULL , 
     Course_Edition_Course_code   VARCHAR2 (10 BYTE)  NOT NULL , 
     Course_Edition_semester      INTEGER  NOT NULL , 
     title                        CLOB  NOT NULL , 
     open_until                   TIMESTAMP WITH LOCAL TIME ZONE 
    ) 
;

ALTER TABLE Survey 
    ADD CONSTRAINT Survey_PK PRIMARY KEY ( id ) ;

CREATE TABLE Survey_Option 
    ( 
     Survey_id INTEGER  NOT NULL , 
     "option"  CLOB  NOT NULL , 
     capacity  INTEGER 
    ) 
;

ALTER TABLE Survey_Option 
    ADD 
    CHECK (capacity > 0) 
;

CREATE TABLE SURVEY_RESPONSE 
    ( 
--  ERROR: Column name length exceeds maximum allowed length(30) 
     Course_Enrollment_Course_Enrollment_ID NUMBER  NOT NULL , 
     Survey_id                              INTEGER  NOT NULL , 
     option_text                            CLOB  NOT NULL , 
     responded_at                           DATE DEFAULT CURRENT_TIMESTAMP  NOT NULL 
    ) 
;

ALTER TABLE SURVEY_RESPONSE 
    ADD CONSTRAINT SURVEY_RESPONSE_PK PRIMARY KEY ( Course_Enrollment_Course_Enrollment_ID, Survey_id ) ;

CREATE TABLE Teacher 
    ( 
     id                INTEGER  NOT NULL , 
     date_registration DATE DEFAULT CURRENT_DATE  NOT NULL 
    ) 
;

ALTER TABLE Teacher 
    ADD CONSTRAINT Teacher_PK PRIMARY KEY ( id ) ;

CREATE TABLE Teaches 
    ( 
     Teacher_id                   INTEGER  NOT NULL , 
     Course_Edition_academic_year INTEGER  NOT NULL , 
     Course_Edition_course_code   VARCHAR2 (10 BYTE)  NOT NULL , 
     Course_Edition_semester      INTEGER  NOT NULL , 
     Teaches_ID                   NUMBER  NOT NULL , 
     academic_year                INTEGER  NOT NULL , 
     semester                     INTEGER  NOT NULL 
    ) 
;

ALTER TABLE Teaches 
    ADD CONSTRAINT Teaches_PK PRIMARY KEY ( Teaches_ID ) ;

CREATE TABLE Teaching_Assistant 
    ( 
     Student_id INTEGER  NOT NULL , 
     Teacher_id INTEGER  NOT NULL 
    ) 
;

ALTER TABLE Teaching_Assistant 
    ADD CONSTRAINT Teaching_Assistant_PK PRIMARY KEY ( Student_id, Teacher_id ) ;

--  ERROR: FK name length exceeds maximum allowed length(30) 
ALTER TABLE Aggregated_Course_Edition_Results 
    ADD CONSTRAINT Aggregated_Course_Edition_Results_Teaches_FK FOREIGN KEY 
    ( 
     Teaches_Teaches_ID
    ) 
    REFERENCES Teaches 
    ( 
     Teaches_ID
    ) 
;

ALTER TABLE Announcement 
    ADD CONSTRAINT Announcement_Teaches_FK FOREIGN KEY 
    ( 
     Teaches_Teaches_ID
    ) 
    REFERENCES Teaches 
    ( 
     Teaches_ID
    ) 
;

ALTER TABLE Course_Edition 
    ADD CONSTRAINT Course_Edition_Course_FK FOREIGN KEY 
    ( 
     Course_code
    ) 
    REFERENCES Course 
    ( 
     code
    ) 
;

--  ERROR: FK name length exceeds maximum allowed length(30) 
ALTER TABLE Course_Enrollment 
    ADD CONSTRAINT Course_Enrollment_Course_Edition_FK FOREIGN KEY 
    ( 
     Course_Edition_academic_year,
     Course_Edition_course_code,
     Course_Edition_semester
    ) 
    REFERENCES Course_Edition 
    ( 
     academic_year,
     Course_code,
     semester
    ) 
;

--  ERROR: FK name length exceeds maximum allowed length(30) 
ALTER TABLE Course_Enrollment 
    ADD CONSTRAINT Course_Enrollment_Semester_Enrollment_FK FOREIGN KEY 
    ( 
     Semester_Enrollment_id
    ) 
    REFERENCES Semester_Enrollment 
    ( 
     id
    ) 
;

ALTER TABLE COURSE_EQUIVALENCE 
    ADD CONSTRAINT COURSE_EQUIVALENCE_Course_FK FOREIGN KEY 
    ( 
     Course_code
    ) 
    REFERENCES Course 
    ( 
     code
    ) 
;

ALTER TABLE COURSE_EQUIVALENCE 
    ADD CONSTRAINT COURSE_EQUIVALENCE_Course_FKv1 FOREIGN KEY 
    ( 
     Course_code1
    ) 
    REFERENCES Course 
    ( 
     code
    ) 
;

ALTER TABLE COURSE_PREREQUISITE 
    ADD CONSTRAINT COURSE_PREREQUISITE_Course_FK FOREIGN KEY 
    ( 
     Course_code
    ) 
    REFERENCES Course 
    ( 
     code
    ) 
;

--  ERROR: FK name length exceeds maximum allowed length(30) 
ALTER TABLE COURSE_PREREQUISITE 
    ADD CONSTRAINT COURSE_PREREQUISITE_Course_FKv1 FOREIGN KEY 
    ( 
     Course_code1
    ) 
    REFERENCES Course 
    ( 
     code
    ) 
;

ALTER TABLE Curriculum 
    ADD CONSTRAINT Curriculum_Academic_Program_FK FOREIGN KEY 
    ( 
     Academic_Program_code
    ) 
    REFERENCES Academic_Program 
    ( 
     code
    ) 
;

ALTER TABLE Curriculum 
    ADD CONSTRAINT Curriculum_Course_FK FOREIGN KEY 
    ( 
     Course_code
    ) 
    REFERENCES Course 
    ( 
     code
    ) 
;

--  ERROR: FK name length exceeds maximum allowed length(30) 
ALTER TABLE Exam_Attempt 
    ADD CONSTRAINT Exam_Attempt_Course_Enrollment_FK FOREIGN KEY 
    ( 
     Course_Enrollment_Course_Enrollment_ID
    ) 
    REFERENCES Course_Enrollment 
    ( 
     Course_Enrollment_ID
    ) 
;

ALTER TABLE Exam_Attempt 
    ADD CONSTRAINT Exam_Attempt_Exam_FK FOREIGN KEY 
    ( 
     Exam_id
    ) 
    REFERENCES Exam 
    ( 
     id
    ) 
;

ALTER TABLE Exam 
    ADD CONSTRAINT Exam_Course_Edition_FK FOREIGN KEY 
    ( 
     Course_Edition_academic_year,
     Course_Edition_Course_code,
     Course_Edition_semester
    ) 
    REFERENCES Course_Edition 
    ( 
     academic_year,
     Course_code,
     semester
    ) 
;

ALTER TABLE Exam_Problem 
    ADD CONSTRAINT Exam_Problem_Exam_FK FOREIGN KEY 
    ( 
     Exam_id
    ) 
    REFERENCES Exam 
    ( 
     id
    ) 
;

ALTER TABLE Exam_Results 
    ADD CONSTRAINT Exam_Results_Exam_FK FOREIGN KEY 
    ( 
     Exam_id
    ) 
    REFERENCES Exam 
    ( 
     id
    ) 
;

ALTER TABLE Exam_Results 
    ADD CONSTRAINT Exam_Results_Teaches_FK FOREIGN KEY 
    ( 
     Teaches_Teaches_ID
    ) 
    REFERENCES Teaches 
    ( 
     Teaches_ID
    ) 
;

ALTER TABLE Exercise 
    ADD CONSTRAINT Exercise_Course_Edition_FK FOREIGN KEY 
    ( 
     Course_Edition_academic_year,
     Course_Edition_Course_code,
     Course_Edition_semester
    ) 
    REFERENCES Course_Edition 
    ( 
     academic_year,
     Course_code,
     semester
    ) 
;

--  ERROR: FK name length exceeds maximum allowed length(30) 
ALTER TABLE Exercise_Submission 
    ADD CONSTRAINT Exercise_Submission_Course_Enrollment_FK FOREIGN KEY 
    ( 
     Course_Enrollment_Course_Enrollment_ID
    ) 
    REFERENCES Course_Enrollment 
    ( 
     Course_Enrollment_ID
    ) 
;

--  ERROR: FK name length exceeds maximum allowed length(30) 
ALTER TABLE Exercise_Submission 
    ADD CONSTRAINT Exercise_Submission_Exercise_FK FOREIGN KEY 
    ( 
     Exercise_id
    ) 
    REFERENCES Exercise 
    ( 
     id
    ) 
;

ALTER TABLE Lecture 
    ADD CONSTRAINT Lecture_Course_Edition_FK FOREIGN KEY 
    ( 
     Course_Edition_academic_year,
     Course_Edition_Course_code,
     Course_Edition_semester
    ) 
    REFERENCES Course_Edition 
    ( 
     academic_year,
     Course_code,
     semester
    ) 
;

ALTER TABLE MEMBER_MESSAGE 
    ADD CONSTRAINT MEMBER_MESSAGE_Member_FK FOREIGN KEY 
    ( 
     Member_id
    ) 
    REFERENCES Member 
    ( 
     id
    ) 
;

ALTER TABLE MEMBER_MESSAGE 
    ADD CONSTRAINT MEMBER_MESSAGE_Member_FKv1 FOREIGN KEY 
    ( 
     Member_id1
    ) 
    REFERENCES Member 
    ( 
     id
    ) 
;

--  ERROR: FK name length exceeds maximum allowed length(30) 
ALTER TABLE Program_Enrollment 
    ADD CONSTRAINT Program_Enrollment_Academic_Program_FK FOREIGN KEY 
    ( 
     Academic_Program_code
    ) 
    REFERENCES Academic_Program 
    ( 
     code
    ) 
;

ALTER TABLE Program_Enrollment 
    ADD CONSTRAINT Program_Enrollment_Student_FK FOREIGN KEY 
    ( 
     Student_id
    ) 
    REFERENCES Student 
    ( 
     id
    ) 
;

ALTER TABLE REPLIES_TO 
    ADD CONSTRAINT REPLIES_TO_Announcement_FK FOREIGN KEY 
    ( 
     Announcement_id
    ) 
    REFERENCES Announcement 
    ( 
     id
    ) 
;

ALTER TABLE REPLIES_TO 
    ADD CONSTRAINT REPLIES_TO_Announcement_FKv1 FOREIGN KEY 
    ( 
     Announcement_id1
    ) 
    REFERENCES Announcement 
    ( 
     id
    ) 
;

--  ERROR: FK name length exceeds maximum allowed length(30) 
ALTER TABLE Semester_Enrollment 
    ADD CONSTRAINT Semester_Enrollment_Program_Enrollment_FK FOREIGN KEY 
    ( 
     Program_Enrollment_id,
     Program_Enrollment_Academic_Program_code,
     Program_Enrollment_date_enrollment
    ) 
    REFERENCES Program_Enrollment 
    ( 
     Student_id,
     Academic_Program_code,
     date_enrollment
    ) 
;

ALTER TABLE Student_Answer 
    ADD CONSTRAINT Student_Answer_Exam_Attempt_FK FOREIGN KEY 
    ( 
     Exam_Attempt_id
    ) 
    REFERENCES Exam_Attempt 
    ( 
     id
    ) 
;

ALTER TABLE Student_Answer 
    ADD CONSTRAINT Student_Answer_Exam_Problem_FK FOREIGN KEY 
    ( 
     Exam_Problem_pid,
     Exam_Problem_exam_id
    ) 
    REFERENCES Exam_Problem 
    ( 
     pid,
     Exam_id
    ) 
;

--  ERROR: FK name length exceeds maximum allowed length(30) 
ALTER TABLE Student_Grade 
    ADD CONSTRAINT Student_Grade_Aggregated_Course_Edition_Results_FK FOREIGN KEY 
    ( 
     Aggregated_Course_Edition_Results_Aggregated_Course_Edition_Results_ID
    ) 
    REFERENCES Aggregated_Course_Edition_Results 
    ( 
     Aggregated_Course_Edition_Results_ID
    ) 
;

--  ERROR: FK name length exceeds maximum allowed length(30) 
ALTER TABLE Student_Grade 
    ADD CONSTRAINT Student_Grade_Course_Enrollment_FK FOREIGN KEY 
    ( 
     Course_Enrollment_Course_Enrollment_ID
    ) 
    REFERENCES Course_Enrollment 
    ( 
     Course_Enrollment_ID
    ) 
;

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

ALTER TABLE Survey 
    ADD CONSTRAINT Survey_Course_Edition_FK FOREIGN KEY 
    ( 
     Course_Edition_academic_year,
     Course_Edition_Course_code,
     Course_Edition_semester
    ) 
    REFERENCES Course_Edition 
    ( 
     academic_year,
     Course_code,
     semester
    ) 
;

ALTER TABLE Survey_Option 
    ADD CONSTRAINT Survey_Option_Survey_FK FOREIGN KEY 
    ( 
     Survey_id
    ) 
    REFERENCES Survey 
    ( 
     id
    ) 
;

--  ERROR: FK name length exceeds maximum allowed length(30) 
ALTER TABLE SURVEY_RESPONSE 
    ADD CONSTRAINT SURVEY_RESPONSE_Course_Enrollment_FK FOREIGN KEY 
    ( 
     Course_Enrollment_Course_Enrollment_ID
    ) 
    REFERENCES Course_Enrollment 
    ( 
     Course_Enrollment_ID
    ) 
;

ALTER TABLE SURVEY_RESPONSE 
    ADD CONSTRAINT SURVEY_RESPONSE_Survey_FK FOREIGN KEY 
    ( 
     Survey_id
    ) 
    REFERENCES Survey 
    ( 
     id
    ) 
;

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

ALTER TABLE Teaches 
    ADD CONSTRAINT Teaches_Course_Edition_FK FOREIGN KEY 
    ( 
     Course_Edition_academic_year,
     Course_Edition_course_code,
     Course_Edition_semester
    ) 
    REFERENCES Course_Edition 
    ( 
     academic_year,
     Course_code,
     semester
    ) 
;

ALTER TABLE Teaches 
    ADD CONSTRAINT Teaches_Teacher_FK FOREIGN KEY 
    ( 
     Teacher_id
    ) 
    REFERENCES Teacher 
    ( 
     id
    ) 
;

ALTER TABLE Teaching_Assistant 
    ADD CONSTRAINT Teaching_Assistant_Student_FK FOREIGN KEY 
    ( 
     Student_id
    ) 
    REFERENCES Student 
    ( 
     id
    ) 
;

ALTER TABLE Teaching_Assistant 
    ADD CONSTRAINT Teaching_Assistant_Teacher_FK FOREIGN KEY 
    ( 
     Teacher_id
    ) 
    REFERENCES Teacher 
    ( 
     id
    ) 
;

CREATE OR REPLACE TRIGGER FKNTM_Aggregated_Course_Editio 
BEFORE UPDATE OF Teaches_Teaches_ID 
ON Aggregated_Course_Edition_Results 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Aggregated_Course_Edition_Results is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Announcement 
BEFORE UPDATE OF Teaches_Teaches_ID 
ON Announcement 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Announcement is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Course_Edition 
BEFORE UPDATE OF Course_code 
ON Course_Edition 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Course_Edition is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Course_Enrollment 
BEFORE UPDATE OF Course_Edition_academic_year, Course_Edition_course_code, Course_Edition_semester, Semester_Enrollment_id 
ON Course_Enrollment 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Course_Enrollment is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Exam 
BEFORE UPDATE OF Course_Edition_academic_year, Course_Edition_Course_code, Course_Edition_semester 
ON Exam 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Exam is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Exam_Attempt 
BEFORE UPDATE OF Course_Enrollment_Course_Enrollment_ID, Exam_id 
ON Exam_Attempt 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Exam_Attempt is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Exam_Problem 
BEFORE UPDATE OF Exam_id 
ON Exam_Problem 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Exam_Problem is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Exam_Results 
BEFORE UPDATE OF Exam_id, Teaches_Teaches_ID 
ON Exam_Results 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Exam_Results is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Exercise_Submission 
BEFORE UPDATE OF Course_Enrollment_Course_Enrollment_ID, Exercise_id 
ON Exercise_Submission 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Exercise_Submission is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Lecture 
BEFORE UPDATE OF Course_Edition_academic_year, Course_Edition_Course_code, Course_Edition_semester 
ON Lecture 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Lecture is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Program_Enrollment 
BEFORE UPDATE OF Academic_Program_code, Student_id 
ON Program_Enrollment 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Program_Enrollment is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Semester_Enrollment 
BEFORE UPDATE OF Program_Enrollment_id, Program_Enrollment_Academic_Program_code, Program_Enrollment_date_enrollment 
ON Semester_Enrollment 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Semester_Enrollment is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Student_Grade 
BEFORE UPDATE OF Aggregated_Course_Edition_Results_Aggregated_Course_Edition_Results_ID, Course_Enrollment_Course_Enrollment_ID 
ON Student_Grade 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Student_Grade is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Survey 
BEFORE UPDATE OF Course_Edition_academic_year, Course_Edition_Course_code, Course_Edition_semester 
ON Survey 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Survey is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Survey_Option 
BEFORE UPDATE OF Survey_id 
ON Survey_Option 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Survey_Option is violated'); 
END; 
/

CREATE OR REPLACE TRIGGER FKNTM_Teaches 
BEFORE UPDATE OF Course_Edition_academic_year, Course_Edition_course_code, Course_Edition_semester, Teacher_id 
ON Teaches 
BEGIN 
  raise_application_error(-20225,'Non Transferable FK constraint  on table Teaches is violated'); 
END; 
/

--  ERROR: No Discriminator Column found in Arc FKArc_1 - constraint trigger for Arc cannot be generated 

--  ERROR: No Discriminator Column found in Arc FKArc_1 - constraint trigger for Arc cannot be generated

CREATE SEQUENCE Aggregated_Course_Edition_Resu 
START WITH 1 
    NOCACHE 
    ORDER ;

CREATE OR REPLACE TRIGGER Aggregated_Course_Edition_Resu 
BEFORE INSERT ON Aggregated_Course_Edition_Results 
FOR EACH ROW 
WHEN (NEW.Aggregated_Course_Edition_Results_ID IS NULL) 
BEGIN 
    :NEW.Aggregated_Course_Edition_Results_ID := Aggregated_Course_Edition_Resu.NEXTVAL; 
END;
/

CREATE SEQUENCE Course_Enrollment_Course_Enrol 
START WITH 1 
    NOCACHE 
    ORDER ;

CREATE OR REPLACE TRIGGER Course_Enrollment_Course_Enrol 
BEFORE INSERT ON Course_Enrollment 
FOR EACH ROW 
WHEN (NEW.Course_Enrollment_ID IS NULL) 
BEGIN 
    :NEW.Course_Enrollment_ID := Course_Enrollment_Course_Enrol.NEXTVAL; 
END;
/

CREATE SEQUENCE Teaches_Teaches_ID_SEQ 
START WITH 1 
    NOCACHE 
    ORDER ;

CREATE OR REPLACE TRIGGER Teaches_Teaches_ID_TRG 
BEFORE INSERT ON Teaches 
FOR EACH ROW 
WHEN (NEW.Teaches_ID IS NULL) 
BEGIN 
    :NEW.Teaches_ID := Teaches_Teaches_ID_SEQ.NEXTVAL; 
END;
/



-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                            30
-- CREATE INDEX                             0
-- ALTER TABLE                             86
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                          19
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          3
-- CREATE MATERIALIZED VIEW                 0
-- CREATE MATERIALIZED VIEW LOG             0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- 
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
-- 
-- ERRORS                                  24
-- WARNINGS                                 0