CREATE TABLE "User" ( ID BIGSERIAL NOT NULL, Username text, Password text, Email text, Name text, Surname text, Biography text, FacultyID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE Faculty ( ID BIGSERIAL NOT NULL, Name text, Description text, DateFounded timestamp, Address text, Contact text, Dean text, UniversityID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE University ( ID BIGSERIAL NOT NULL, Name text, Description text, DateFounded timestamp, Address text, Contact text, Rector text, PRIMARY KEY (ID) ); CREATE TABLE Permission ( ID BIGSERIAL NOT NULL, PermissionName text, PRIMARY KEY (ID) ); CREATE TABLE Role ( ID BIGSERIAL NOT NULL, RoleName text, PRIMARY KEY (ID) ); CREATE TABLE User_Role ( ID BIGSERIAL NOT NULL, UserID BIGSERIAL NOT NULL, RoleID BIGSERIAL NOT NULL, PRIMARY KEY (ID, UserID, RoleID) ); CREATE TABLE Role_Permission ( ID BIGSERIAL NOT NULL, RoleID BIGSERIAL NOT NULL, PermissionID BIGSERIAL NOT NULL, PRIMARY KEY (ID, RoleID, PermissionID) ); CREATE TABLE StudyProgram ( ID BIGSERIAL NOT NULL, Name text, Description text, FacultyID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE Subject ( ID BIGSERIAL NOT NULL, Name text, Description text, PRIMARY KEY (ID) ); CREATE TABLE Subject_StudyProgram ( ID BIGSERIAL NOT NULL, SubjectID BIGSERIAL NOT NULL, StudyProgramID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE User_Subject ( ID BIGSERIAL NOT NULL, SubjectID BIGSERIAL NOT NULL, UserID BIGSERIAL NOT NULL, Semester int4, PRIMARY KEY (ID) ); CREATE TABLE Interest ( ID BIGSERIAL NOT NULL, Name text, Description text, PRIMARY KEY (ID) ); CREATE TABLE User_Interest ( ID BIGSERIAL NOT NULL, UserID BIGSERIAL NOT NULL, InterestID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE TopicSuggestion ( ID BIGSERIAL NOT NULL, Name text, Description text, "Date" timestamp, isAvailable bool, StudentID BIGSERIAL NOT NULL, MentorID BIGSERIAL NOT NULL, SubjectID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE UserAttribute ( ID BIGSERIAL NOT NULL, Name text, PRIMARY KEY (ID) ); CREATE TABLE UserAttributeValue ( ID BIGSERIAL NOT NULL, Value text, UserID BIGSERIAL NOT NULL, UserAttributeID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE Mentorship ( ID BIGSERIAL NOT NULL, StudentID BIGSERIAL NOT NULL, MentorID BIGSERIAL NOT NULL, TopicSuggestionID BIGSERIAL NOT NULL, MentorshipTypeID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE MentorshipAttribute ( ID BIGSERIAL NOT NULL, Name text, PRIMARY KEY (ID) ); CREATE TABLE MentorshipType ( ID BIGSERIAL NOT NULL, Type text, MentorshipAttributeID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE MentorshipAttributeValue ( ID BIGSERIAL NOT NULL, Value text, MentorshipID BIGSERIAL NOT NULL, MentorshipAttributeID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE Task ( ID BIGSERIAL NOT NULL, EndDate timestamp, StartDate timestamp, Status int4, Description text, MentorshipID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE Chat ( ID BIGSERIAL NOT NULL, Title text, Topic text, Status int4, "Date" timestamp, StudentID BIGSERIAL NOT NULL, MentorID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE Message ( ID BIGSERIAL NOT NULL, Content text, isRead bool, Timestamp timestamp, ChatID BIGSERIAL NOT NULL, UserID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE CommentOpinion ( ID BIGSERIAL NOT NULL, Comment text, MentorRating int4, Timestamp timestamp, Status int4, StudentID BIGSERIAL NOT NULL, MentorID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE Notification ( ID BIGSERIAL NOT NULL, Content text, Timestamp timestamp, isRead bool, UserToNotifyID BIGSERIAL NOT NULL, CommentOpinionID BIGSERIAL NOT NULL, MessageID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE Notification_Type ( ID BIGSERIAL NOT NULL, Type text, NotificationID BIGSERIAL NOT NULL, PRIMARY KEY (ID) ); ALTER TABLE "User" ADD CONSTRAINT "student enrolled in" FOREIGN KEY (FacultyID) REFERENCES Faculty (ID); ALTER TABLE Faculty ADD CONSTRAINT FKFaculty543242 FOREIGN KEY (UniversityID) REFERENCES University (ID); ALTER TABLE User_Role ADD CONSTRAINT FKUser_Role973318 FOREIGN KEY (UserID) REFERENCES "User" (ID); ALTER TABLE User_Role ADD CONSTRAINT FKUser_Role494723 FOREIGN KEY (RoleID) REFERENCES Role (ID); ALTER TABLE Role_Permission ADD CONSTRAINT FKRole_Permi373264 FOREIGN KEY (RoleID) REFERENCES Role (ID); ALTER TABLE Role_Permission ADD CONSTRAINT FKRole_Permi82941 FOREIGN KEY (PermissionID) REFERENCES Permission (ID); ALTER TABLE StudyProgram ADD CONSTRAINT FKStudyProgr253332 FOREIGN KEY (FacultyID) REFERENCES Faculty (ID); ALTER TABLE Subject_StudyProgram ADD CONSTRAINT FKSubject_St661562 FOREIGN KEY (SubjectID) REFERENCES Subject (ID); ALTER TABLE Subject_StudyProgram ADD CONSTRAINT FKSubject_St673947 FOREIGN KEY (StudyProgramID) REFERENCES StudyProgram (ID); ALTER TABLE User_Subject ADD CONSTRAINT FKUser_Subje900582 FOREIGN KEY (SubjectID) REFERENCES Subject (ID); ALTER TABLE User_Subject ADD CONSTRAINT FKUser_Subje776066 FOREIGN KEY (UserID) REFERENCES "User" (ID); ALTER TABLE User_Interest ADD CONSTRAINT FKUser_Inter956062 FOREIGN KEY (UserID) REFERENCES "User" (ID); ALTER TABLE User_Interest ADD CONSTRAINT FKUser_Inter806540 FOREIGN KEY (InterestID) REFERENCES Interest (ID); ALTER TABLE TopicSuggestion ADD CONSTRAINT "role=student" FOREIGN KEY (StudentID) REFERENCES "User" (ID); ALTER TABLE TopicSuggestion ADD CONSTRAINT "role=mentor" FOREIGN KEY (MentorID) REFERENCES "User" (ID); ALTER TABLE TopicSuggestion ADD CONSTRAINT FKTopicSugge693337 FOREIGN KEY (SubjectID) REFERENCES Subject (ID); ALTER TABLE UserAttributeValue ADD CONSTRAINT FKUserAttrib58999 FOREIGN KEY (UserID) REFERENCES "User" (ID); ALTER TABLE UserAttributeValue ADD CONSTRAINT FKUserAttrib518781 FOREIGN KEY (UserAttributeID) REFERENCES UserAttribute (ID); ALTER TABLE Mentorship ADD CONSTRAINT "available=false if accepted" FOREIGN KEY (TopicSuggestionID) REFERENCES TopicSuggestion (ID); ALTER TABLE Mentorship ADD CONSTRAINT FKMentorship580471 FOREIGN KEY (MentorshipTypeID) REFERENCES MentorshipType (ID); ALTER TABLE MentorshipType ADD CONSTRAINT FKMentorship152845 FOREIGN KEY (MentorshipAttributeID) REFERENCES MentorshipAttribute (ID); ALTER TABLE MentorshipAttributeValue ADD CONSTRAINT FKMentorship527684 FOREIGN KEY (MentorshipID) REFERENCES Mentorship (ID); ALTER TABLE MentorshipAttributeValue ADD CONSTRAINT FKMentorship933147 FOREIGN KEY (MentorshipAttributeID) REFERENCES MentorshipAttribute (ID); ALTER TABLE Task ADD CONSTRAINT FKTask579434 FOREIGN KEY (MentorshipID) REFERENCES Mentorship (ID); ALTER TABLE Chat ADD CONSTRAINT "student sends contact" FOREIGN KEY (StudentID) REFERENCES "User" (ID); ALTER TABLE Chat ADD CONSTRAINT "mentor receives contact" FOREIGN KEY (MentorID) REFERENCES "User" (ID); ALTER TABLE Message ADD CONSTRAINT FKMessage915620 FOREIGN KEY (ChatID) REFERENCES Chat (ID); ALTER TABLE Message ADD CONSTRAINT FKMessage63769 FOREIGN KEY (UserID) REFERENCES "User" (ID); ALTER TABLE CommentOpinion ADD CONSTRAINT "student writes" FOREIGN KEY (StudentID) REFERENCES "User" (ID); ALTER TABLE CommentOpinion ADD CONSTRAINT "mentor approves" FOREIGN KEY (MentorID) REFERENCES "User" (ID); ALTER TABLE Notification ADD CONSTRAINT "user receives notification" FOREIGN KEY (UserToNotifyID) REFERENCES "User" (ID); ALTER TABLE Notification ADD CONSTRAINT FKNotificati961095 FOREIGN KEY (CommentOpinionID) REFERENCES CommentOpinion (ID); ALTER TABLE Notification ADD CONSTRAINT FKNotificati879184 FOREIGN KEY (MessageID) REFERENCES Message (ID); ALTER TABLE Notification_Type ADD CONSTRAINT FKNotificati268813 FOREIGN KEY (NotificationID) REFERENCES Notification (ID);