DatabaseCreation: ddl_najdi_mentor.sql

File ddl_najdi_mentor.sql, 14.5 KB (added by 231067, 6 days ago)
Line 
1
2CREATE TABLE "User" (
3 ID BIGSERIAL NOT NULL,
4 Username text,
5 Password text,
6 Email text,
7 Name text,
8 Surname text,
9 Biography text,
10 FacultyID BIGSERIAL NOT NULL,
11 PRIMARY KEY (ID)
12);
13
14CREATE TABLE Faculty (
15 ID BIGSERIAL NOT NULL,
16 Name text,
17 Description text,
18 DateFounded timestamp,
19 Address text,
20 Contact text,
21 Dean text,
22 UniversityID BIGSERIAL NOT NULL,
23 PRIMARY KEY (ID)
24);
25
26CREATE TABLE University (
27 ID BIGSERIAL NOT NULL,
28 Name text,
29 Description text,
30 DateFounded timestamp,
31 Address text,
32 Contact text,
33 Rector text,
34 PRIMARY KEY (ID)
35);
36
37CREATE TABLE Permission (
38 ID BIGSERIAL NOT NULL,
39 PermissionName text,
40 PRIMARY KEY (ID)
41);
42
43CREATE TABLE Role (
44 ID BIGSERIAL NOT NULL,
45 RoleName text,
46 PRIMARY KEY (ID)
47);
48
49CREATE TABLE User_Role (
50 ID BIGSERIAL NOT NULL,
51 UserID BIGSERIAL NOT NULL,
52 RoleID BIGSERIAL NOT NULL,
53 PRIMARY KEY (ID, UserID, RoleID)
54);
55
56CREATE TABLE Role_Permission (
57 ID BIGSERIAL NOT NULL,
58 RoleID BIGSERIAL NOT NULL,
59 PermissionID BIGSERIAL NOT NULL,
60 PRIMARY KEY (ID, RoleID, PermissionID)
61);
62
63CREATE TABLE StudyProgram (
64 ID BIGSERIAL NOT NULL,
65 Name text,
66 Description text,
67 FacultyID BIGSERIAL NOT NULL,
68 PRIMARY KEY (ID)
69);
70
71CREATE TABLE Subject (
72 ID BIGSERIAL NOT NULL,
73 Name text,
74 Description text,
75 PRIMARY KEY (ID)
76);
77
78CREATE TABLE Subject_StudyProgram (
79 ID BIGSERIAL NOT NULL,
80 SubjectID BIGSERIAL NOT NULL,
81 StudyProgramID BIGSERIAL NOT NULL,
82 PRIMARY KEY (ID)
83);
84
85CREATE TABLE User_Subject (
86 ID BIGSERIAL NOT NULL,
87 SubjectID BIGSERIAL NOT NULL,
88 UserID BIGSERIAL NOT NULL,
89 Semester int4,
90 PRIMARY KEY (ID)
91);
92
93CREATE TABLE Interest (
94 ID BIGSERIAL NOT NULL,
95 Name text,
96 Description text,
97 PRIMARY KEY (ID)
98);
99
100CREATE TABLE User_Interest (
101 ID BIGSERIAL NOT NULL,
102 UserID BIGSERIAL NOT NULL,
103 InterestID BIGSERIAL NOT NULL,
104 PRIMARY KEY (ID)
105);
106
107CREATE TABLE TopicSuggestion (
108 ID BIGSERIAL NOT NULL,
109 Name text,
110 Description text,
111 "Date" timestamp,
112 isAvailable bool,
113 StudentID BIGSERIAL NOT NULL,
114 MentorID BIGSERIAL NOT NULL,
115 SubjectID BIGSERIAL NOT NULL,
116 PRIMARY KEY (ID)
117);
118
119CREATE TABLE UserAttribute (
120 ID BIGSERIAL NOT NULL,
121 Name text,
122 PRIMARY KEY (ID)
123);
124
125CREATE TABLE UserAttributeValue (
126 ID BIGSERIAL NOT NULL,
127 Value text,
128 UserID BIGSERIAL NOT NULL,
129 UserAttributeID BIGSERIAL NOT NULL,
130 PRIMARY KEY (ID)
131);
132
133CREATE TABLE Mentorship (
134 ID BIGSERIAL NOT NULL,
135 StudentID BIGSERIAL NOT NULL,
136 MentorID BIGSERIAL NOT NULL,
137 TopicSuggestionID BIGSERIAL NOT NULL,
138 MentorshipTypeID BIGSERIAL NOT NULL,
139 PRIMARY KEY (ID)
140);
141
142CREATE TABLE MentorshipAttribute (
143 ID BIGSERIAL NOT NULL,
144 Name text,
145 PRIMARY KEY (ID)
146);
147
148CREATE TABLE MentorshipType (
149 ID BIGSERIAL NOT NULL,
150 Type text,
151 MentorshipAttributeID BIGSERIAL NOT NULL,
152 PRIMARY KEY (ID)
153);
154
155CREATE TABLE MentorshipAttributeValue (
156 ID BIGSERIAL NOT NULL,
157 Value text,
158 MentorshipID BIGSERIAL NOT NULL,
159 MentorshipAttributeID BIGSERIAL NOT NULL,
160 PRIMARY KEY (ID)
161);
162
163CREATE TABLE Task (
164 ID BIGSERIAL NOT NULL,
165 EndDate timestamp,
166 StartDate timestamp,
167 Status int4,
168 Description text,
169 MentorshipID BIGSERIAL NOT NULL,
170 PRIMARY KEY (ID)
171);
172
173CREATE TABLE Chat (
174 ID BIGSERIAL NOT NULL,
175 Title text,
176 Topic text,
177 Status int4,
178 "Date" timestamp,
179 StudentID BIGSERIAL NOT NULL,
180 MentorID BIGSERIAL NOT NULL,
181 PRIMARY KEY (ID)
182);
183
184CREATE TABLE Message (
185 ID BIGSERIAL NOT NULL,
186 Content text,
187 isRead bool,
188 Timestamp timestamp,
189 ChatID BIGSERIAL NOT NULL,
190 UserID BIGSERIAL NOT NULL,
191 PRIMARY KEY (ID)
192);
193
194CREATE TABLE CommentOpinion (
195 ID BIGSERIAL NOT NULL,
196 Comment text,
197 MentorRating int4,
198 Timestamp timestamp,
199 Status int4,
200 StudentID BIGSERIAL NOT NULL,
201 MentorID BIGSERIAL NOT NULL,
202 PRIMARY KEY (ID)
203);
204
205CREATE TABLE Notification (
206 ID BIGSERIAL NOT NULL,
207 Content text,
208 Timestamp timestamp,
209 isRead bool,
210 UserToNotifyID BIGSERIAL NOT NULL,
211 CommentOpinionID BIGSERIAL NOT NULL,
212 MessageID BIGSERIAL NOT NULL,
213 PRIMARY KEY (ID)
214);
215
216CREATE TABLE Notification_Type (
217 ID BIGSERIAL NOT NULL,
218 Type text,
219 NotificationID BIGSERIAL NOT NULL,
220 PRIMARY KEY (ID)
221);
222
223
224
225ALTER TABLE "User" ADD CONSTRAINT "student enrolled in" FOREIGN KEY (FacultyID) REFERENCES Faculty (ID);
226ALTER TABLE Faculty ADD CONSTRAINT FKFaculty543242 FOREIGN KEY (UniversityID) REFERENCES University (ID);
227ALTER TABLE User_Role ADD CONSTRAINT FKUser_Role973318 FOREIGN KEY (UserID) REFERENCES "User" (ID);
228ALTER TABLE User_Role ADD CONSTRAINT FKUser_Role494723 FOREIGN KEY (RoleID) REFERENCES Role (ID);
229ALTER TABLE Role_Permission ADD CONSTRAINT FKRole_Permi373264 FOREIGN KEY (RoleID) REFERENCES Role (ID);
230ALTER TABLE Role_Permission ADD CONSTRAINT FKRole_Permi82941 FOREIGN KEY (PermissionID) REFERENCES Permission (ID);
231ALTER TABLE StudyProgram ADD CONSTRAINT FKStudyProgr253332 FOREIGN KEY (FacultyID) REFERENCES Faculty (ID);
232ALTER TABLE Subject_StudyProgram ADD CONSTRAINT FKSubject_St661562 FOREIGN KEY (SubjectID) REFERENCES Subject (ID);
233ALTER TABLE Subject_StudyProgram ADD CONSTRAINT FKSubject_St673947 FOREIGN KEY (StudyProgramID) REFERENCES StudyProgram (ID);
234ALTER TABLE User_Subject ADD CONSTRAINT FKUser_Subje900582 FOREIGN KEY (SubjectID) REFERENCES Subject (ID);
235ALTER TABLE User_Subject ADD CONSTRAINT FKUser_Subje776066 FOREIGN KEY (UserID) REFERENCES "User" (ID);
236ALTER TABLE User_Interest ADD CONSTRAINT FKUser_Inter956062 FOREIGN KEY (UserID) REFERENCES "User" (ID);
237ALTER TABLE User_Interest ADD CONSTRAINT FKUser_Inter806540 FOREIGN KEY (InterestID) REFERENCES Interest (ID);
238ALTER TABLE TopicSuggestion ADD CONSTRAINT "role=student" FOREIGN KEY (StudentID) REFERENCES "User" (ID);
239ALTER TABLE TopicSuggestion ADD CONSTRAINT "role=mentor" FOREIGN KEY (MentorID) REFERENCES "User" (ID);
240ALTER TABLE TopicSuggestion ADD CONSTRAINT FKTopicSugge693337 FOREIGN KEY (SubjectID) REFERENCES Subject (ID);
241ALTER TABLE UserAttributeValue ADD CONSTRAINT FKUserAttrib58999 FOREIGN KEY (UserID) REFERENCES "User" (ID);
242ALTER TABLE UserAttributeValue ADD CONSTRAINT FKUserAttrib518781 FOREIGN KEY (UserAttributeID) REFERENCES UserAttribute (ID);
243ALTER TABLE Mentorship ADD CONSTRAINT "available=false if accepted" FOREIGN KEY (TopicSuggestionID) REFERENCES TopicSuggestion (ID);
244ALTER TABLE Mentorship ADD CONSTRAINT FKMentorship580471 FOREIGN KEY (MentorshipTypeID) REFERENCES MentorshipType (ID);
245ALTER TABLE MentorshipType ADD CONSTRAINT FKMentorship152845 FOREIGN KEY (MentorshipAttributeID) REFERENCES MentorshipAttribute (ID);
246ALTER TABLE MentorshipAttributeValue ADD CONSTRAINT FKMentorship527684 FOREIGN KEY (MentorshipID) REFERENCES Mentorship (ID);
247ALTER TABLE MentorshipAttributeValue ADD CONSTRAINT FKMentorship933147 FOREIGN KEY (MentorshipAttributeID) REFERENCES MentorshipAttribute (ID);
248ALTER TABLE Task ADD CONSTRAINT FKTask579434 FOREIGN KEY (MentorshipID) REFERENCES Mentorship (ID);
249ALTER TABLE Chat ADD CONSTRAINT "student sends contact" FOREIGN KEY (StudentID) REFERENCES "User" (ID);
250ALTER TABLE Chat ADD CONSTRAINT "mentor receives contact" FOREIGN KEY (MentorID) REFERENCES "User" (ID);
251ALTER TABLE Message ADD CONSTRAINT FKMessage915620 FOREIGN KEY (ChatID) REFERENCES Chat (ID);
252ALTER TABLE Message ADD CONSTRAINT FKMessage63769 FOREIGN KEY (UserID) REFERENCES "User" (ID);
253ALTER TABLE CommentOpinion ADD CONSTRAINT "student writes" FOREIGN KEY (StudentID) REFERENCES "User" (ID);
254ALTER TABLE CommentOpinion ADD CONSTRAINT "mentor approves" FOREIGN KEY (MentorID) REFERENCES "User" (ID);
255ALTER TABLE Notification ADD CONSTRAINT "user receives notification" FOREIGN KEY (UserToNotifyID) REFERENCES "User" (ID);
256ALTER TABLE Notification ADD CONSTRAINT FKNotificati961095 FOREIGN KEY (CommentOpinionID) REFERENCES CommentOpinion (ID);
257ALTER TABLE Notification ADD CONSTRAINT FKNotificati879184 FOREIGN KEY (MessageID) REFERENCES Message (ID);
258ALTER TABLE Notification_Type ADD CONSTRAINT FKNotificati268813 FOREIGN KEY (NotificationID) REFERENCES Notification (ID);