AdvancedTopic: DDL_optimized.sql

File DDL_optimized.sql, 22.5 KB (added by 231082, 3 days ago)

The DDL from the Advanced Topic.

Line 
1CREATE TABLE t_Member (
2 created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
3 updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
4
5 id INTEGER GENERATED ALWAYS AS IDENTITY,
6
7 date_birth DATE,
8 is_active BOOLEAN DEFAULT TRUE,
9
10 name TEXT,
11 surname TEXT,
12 username TEXT,
13 password_hash TEXT,
14
15
16 CONSTRAINT pk_member PRIMARY KEY(id),
17
18 CONSTRAINT nn_member_name CHECK(name IS NOT NULL),
19 CONSTRAINT nn_member_surname CHECK(surname IS NOT NULL),
20 CONSTRAINT uq_member_username UNIQUE(username),
21 CONSTRAINT nn_member_username CHECK(username IS NOT NULL),
22 CONSTRAINT nn_member_password_hash CHECK(password_hash IS NOT NULL),
23 CONSTRAINT nn_member_date_birth CHECK(date_birth IS NOT NULL),
24 CONSTRAINT nn_member_created_at CHECK(created_at IS NOT NULL),
25
26 CONSTRAINT ck_member_created_before_updated CHECK(created_at <= updated_at)
27);
28
29
30CREATE TABLE t_Member_Message (
31 sent_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
32
33 id INTEGER GENERATED ALWAYS AS IDENTITY,
34 member_id1 INTEGER,
35 member_id2 INTEGER,
36
37 message TEXT,
38
39
40 CONSTRAINT pk_member_message PRIMARY KEY(id, member_id1, member_id2),
41
42 CONSTRAINT fk_member_message_member_id1 FOREIGN KEY(member_id1)
43 REFERENCES t_Member (id)
44 ON DELETE SET NULL ON UPDATE CASCADE,
45 CONSTRAINT fk_member_message_member_id2 FOREIGN KEY(member_id2)
46 REFERENCES t_Member (id)
47 ON DELETE SET NULL ON UPDATE CASCADE,
48
49 CONSTRAINT nn_member_message_message CHECK(message IS NOT NULL),
50 CONSTRAINT nn_member_message_sent_at CHECK(sent_at IS NOT NULL)
51);
52
53CREATE TABLE t_Student (
54 id INTEGER,
55
56 index TEXT,
57
58
59 CONSTRAINT pk_student PRIMARY KEY(id),
60
61 CONSTRAINT fk_student_id FOREIGN KEY(id)
62 REFERENCES t_Member (id)
63 ON DELETE SET NULL ON UPDATE CASCADE,
64
65 CONSTRAINT uq_student_index UNIQUE(index),
66 CONSTRAINT nn_student_index CHECK(index IS NOT NULL),
67 CONSTRAINT ck_student_index CHECK (index ~ '^\d{10}$')
68);
69
70
71CREATE TABLE t_Teacher (
72 id INTEGER,
73
74 date_registration DATE,
75
76
77 CONSTRAINT pk_teacher PRIMARY KEY(id),
78
79 CONSTRAINT fk_teacher FOREIGN KEY(id)
80 REFERENCES t_Member (id)
81 ON DELETE SET NULL ON UPDATE CASCADE,
82
83 CONSTRAINT nn_teacher_date_registration CHECK(date_registration IS NOT NULL)
84);
85
86CREATE TABLE t_Teaching_Assistant (
87 id INTEGER GENERATED ALWAYS AS IDENTITY,
88 student_id INTEGER,
89 teacher_id INTEGER,
90
91
92 CONSTRAINT pk_teaching_assistant PRIMARY KEY(id, student_id, teacher_id),
93
94 CONSTRAINT fk_teaching_assistant_student_id FOREIGN KEY(student_id)
95 REFERENCES t_Student(id)
96 ON DELETE SET NULL ON UPDATE CASCADE,
97 CONSTRAINT fk_teaching_assistant_teacher_id FOREIGN KEY(teacher_id)
98 REFERENCES t_Teacher(id)
99 ON DELETE SET NULL ON UPDATE CASCADE
100);
101
102CREATE TABLE __t_Academic_Program (
103 created_at DATE,
104 updated_at DATE,
105 numeric_id SMALLINT,
106 duration_years UDV_ACADEMIC_PROGRAM_DURATION_YEARS,
107
108 code TEXT,
109
110 name TEXT,
111
112
113 CONSTRAINT pk_academic_program PRIMARY KEY(code),
114
115 CONSTRAINT nn_academic_program_name CHECK(name IS NOT NULL),
116 CONSTRAINT nn_academic_program_created_at CHECK(created_at IS NOT NULL),
117 CONSTRAINT nn_academic_program_updated_at CHECK(updated_at IS NOT NULL),
118 CONSTRAINT ck_academic_program_updated_after_created CHECK(updated_at >= created_at)
119);
120
121
122CREATE TABLE t_Program_Enrollment (
123 id INTEGER GENERATED ALWAYS AS IDENTITY,
124
125 student_id INTEGER,
126
127 date_enrollment DATE,
128 date_disenrollment DATE,
129 finished BOOLEAN,
130
131 academic_program_code TEXT,
132
133
134 CONSTRAINT pk_program_enrollment_id PRIMARY KEY(id),
135
136 CONSTRAINT fk_program_enrollment_student_id FOREIGN KEY(student_id)
137 REFERENCES t_Student(id)
138 ON DELETE SET NULL ON UPDATE CASCADE,
139 CONSTRAINT fk_program_enrollment_academic_program_code FOREIGN KEY(academic_program_code)
140 REFERENCES __t_Academic_Program(code)
141 ON DELETE SET NULL ON UPDATE CASCADE,
142
143 CONSTRAINT uq_program_enrollment_1 UNIQUE(student_id, academic_program_code, date_enrollment),
144 CONSTRAINT nn_program_enrollment_student_id CHECK(student_id IS NOT NULL),
145 CONSTRAINT nn_program_enrollment_academic_program_code CHECK(academic_program_code IS NOT NULL),
146 CONSTRAINT nn_program_enrollment_date_enrollment CHECK(date_enrollment IS NOT NULL)
147);
148
149
150CREATE TABLE t_Course (
151 credits INT,
152 created_at DATE,
153
154 code TEXT,
155
156 name TEXT,
157
158
159 CONSTRAINT pk_course PRIMARY KEY(code),
160
161 CONSTRAINT nn_course_name CHECK(name IS NOT NULL),
162 CONSTRAINT nn_course_credits CHECK(credits IS NOT NULL),
163 CONSTRAINT nn_course_created_at CHECK(created_at IS NOT NULL),
164 CONSTRAINT ck_course_credits CHECK(credits >= 0)
165);
166
167CREATE TABLE __t_Curriculum (
168 academic_program_code TEXT,
169 course_code TEXT,
170
171 semester UDV_CURRICULUM_SEMESTER,
172 mandatory BOOLEAN,
173
174
175 CONSTRAINT pk_curriculum PRIMARY KEY(academic_program_code, course_code),
176
177 CONSTRAINT fk_curriculum_academic_program_code FOREIGN KEY(academic_program_code)
178 REFERENCES __t_Academic_Program(code)
179 ON DELETE SET NULL ON UPDATE CASCADE,
180 CONSTRAINT fk_curriculum_course_code FOREIGN KEY(course_code)
181 REFERENCES t_Course(code)
182 ON DELETE SET NULL ON UPDATE CASCADE,
183
184 CONSTRAINT nn_curriculum_semester CHECK(semester IS NOT NULL),
185 CONSTRAINT nn_curriculum_mandatory CHECK(mandatory IS NOT NULL)
186);
187
188CREATE TABLE t_Course_Prerequisite (
189 predecessor_course_code TEXT,
190 successor_course_code TEXT,
191
192
193 CONSTRAINT pk_course_prerequisite PRIMARY KEY(predecessor_course_code, successor_course_code),
194
195 CONSTRAINT fk_course_prerequisite_predecessor_course_code FOREIGN KEY(predecessor_course_code)
196 REFERENCES t_Course(code)
197 ON DELETE SET NULL ON UPDATE CASCADE,
198 CONSTRAINT fk_course_prerequisite_successor_course_code FOREIGN KEY(successor_course_code)
199 REFERENCES t_Course(code)
200 ON DELETE SET NULL ON UPDATE CASCADE,
201
202 CONSTRAINT ck_course_prerequisite_different_courses CHECK(predecessor_course_code <> successor_course_code)
203);
204
205CREATE TABLE t_Course_Equivalence (
206 course1_code TEXT,
207 course2_code TEXT,
208
209
210 CONSTRAINT pk_course_equivalence PRIMARY KEY(course1_code, course2_code),
211
212 CONSTRAINT fk_course_equivalence_course1_code FOREIGN KEY(course1_code)
213 REFERENCES t_Course(code)
214 ON DELETE SET NULL ON UPDATE CASCADE,
215 CONSTRAINT fk_course_equivalence_course2_code FOREIGN KEY(course2_code)
216 REFERENCES t_Course(code)
217 ON DELETE SET NULL ON UPDATE CASCADE,
218 CONSTRAINT ck_course_equivalence_different_courses CHECK(course1_code <> course2_code)
219);
220
221CREATE TABLE __t_Course_Edition (
222 id INTEGER GENERATED ALWAYS AS IDENTITY,
223
224 academic_year SMALLINT,
225 semester UDV_COURSE_EDITION_SEMESTER,
226
227 course_code TEXT,
228
229
230 CONSTRAINT pk_course_edition PRIMARY KEY(id),
231
232 CONSTRAINT fk_course_edition FOREIGN KEY(course_code)
233 REFERENCES t_Course(code)
234 ON DELETE SET NULL ON UPDATE CASCADE,
235
236 CONSTRAINT uq_course_edition_ccays UNIQUE(course_code, academic_year, semester),
237 CONSTRAINT nn_course_edition_academic_year CHECK(academic_year IS NOT NULL),
238 CONSTRAINT nn_course_edition_semester CHECK(semester IS NOT NULL)
239);
240
241CREATE TABLE t_Teaches (
242 teacher_id INTEGER,
243 course_edition_id INTEGER,
244
245
246 CONSTRAINT pk_teaches PRIMARY KEY(teacher_id, course_edition_id),
247
248 CONSTRAINT fk_teaches_teacher_id FOREIGN KEY(teacher_id)
249 REFERENCES t_Teacher(id)
250 ON DELETE SET NULL ON UPDATE CASCADE,
251 CONSTRAINT fk_teaches_course_edition_id FOREIGN KEY(course_edition_id)
252 REFERENCES __t_Course_Edition(id)
253 ON DELETE SET NULL ON UPDATE CASCADE
254);
255
256CREATE TABLE t_Announcement (
257 sent_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
258
259 id INTEGER GENERATED ALWAYS AS IDENTITY,
260 teaches_teacher_id INTEGER,
261 teaches_course_edition_id INTEGER,
262
263 message TEXT,
264
265
266 CONSTRAINT pk_announcement PRIMARY KEY(id, teaches_teacher_id, teaches_course_edition_id),
267
268 CONSTRAINT fk_announcement_teaches FOREIGN KEY(teaches_teacher_id, teaches_course_edition_id)
269 REFERENCES t_Teaches(teacher_id, course_edition_id)
270 ON DELETE SET NULL ON UPDATE CASCADE,
271
272 CONSTRAINT ck_announcement_message CHECK(message IS NOT NULL),
273 CONSTRAINT ck_announcement_sent_at CHECK(sent_at IS NOT NULL)
274);
275
276
277CREATE TABLE t_Announcement_Replies (
278 ann1_id INTEGER,
279 ann1_ttid INTEGER,
280 ann1_ceid INTEGER,
281 ann2_id INTEGER,
282 ann2_ttid INTEGER,
283 ann2_ceid INTEGER,
284
285
286 CONSTRAINT pk_announcement_replies
287 PRIMARY KEY(
288 ann1_id,
289 ann1_ttid,
290 ann1_ceid,
291 ann2_id,
292 ann2_ttid,
293 ann2_ceid
294 ),
295
296 CONSTRAINT fk_announcement_replies_ann1 FOREIGN KEY(ann1_id, ann1_ttid, ann1_ceid)
297 REFERENCES t_Announcement(id, teaches_teacher_id, teaches_course_edition_id)
298 ON DELETE SET NULL ON UPDATE CASCADE,
299 CONSTRAINT fk_announcement_replies_ann2 FOREIGN KEY(ann2_id, ann2_ttid, ann2_ceid)
300 REFERENCES t_Announcement(id, teaches_teacher_id, teaches_course_edition_id)
301 ON DELETE SET NULL ON UPDATE CASCADE,
302);
303
304
305CREATE TABLE t_Lecture (
306 created_at TIMESTAMP WITHOUT TIME ZONE,
307
308 id INTEGER GENERATED ALWAYS AS IDENTITY,
309
310 course_edition_id INTEGER,
311
312 title TEXT,
313 content_url TEXT,
314
315
316 CONSTRAINT pk_lecture PRIMARY KEY(id),
317
318 CONSTRAINT fk_lecture FOREIGN KEY(course_edition_id)
319 REFERENCES __t_Course_Edition(id)
320 ON DELETE SET NULL ON UPDATE CASCADE,
321
322 CONSTRAINT nn_lecture_title CHECK (title IS NOT NULL),
323 CONSTRAINT nn_lecture_created_at CHECK (created_at IS NOT NULL)
324);
325
326CREATE TABLE __t_Semester_Enrollment (
327 id INTEGER GENERATED ALWAYS AS IDENTITY,
328
329 program_enrollment_id INTEGER,
330
331 academic_year SMALLINT,
332 semester UDV_SEMESTER_ENROLLMENT_SEMESTER,
333
334 CONSTRAINT pk_semester_enrollment PRIMARY KEY(id),
335
336 CONSTRAINT fk_semester_enrollment FOREIGN KEY(program_enrollment_id)
337 REFERENCES t_Program_Enrollment(id)
338 ON DELETE SET NULL ON UPDATE CASCADE,
339
340 CONSTRAINT uq_semester_enrollment_peidays UNIQUE(program_enrollment_id, academic_year, semester),
341 CONSTRAINT nn_semester_enrollment_academic_year CHECK(academic_year IS NOT NULL),
342 CONSTRAINT nn_semester_enrollment_semester CHECK(semester IS NOT NULL),
343 CONSTRAINT ck_semester_enrollment_academic_year CHECK(academic_year >= 2000)
344);
345
346CREATE TABLE t_Course_Enrollment (
347 id INTEGER GENERATED ALWAYS AS IDENTITY,
348
349 semester_enrollment_id INTEGER,
350 course_edition_id INTEGER,
351
352
353 CONSTRAINT pk_course_enrollment PRIMARY KEY(id),
354
355 CONSTRAINT fk_course_enrollment_seid FOREIGN KEY(semester_enrollment_id)
356 REFERENCES __t_Semester_Enrollment(id)
357 ON DELETE SET NULL ON UPDATE CASCADE,
358 CONSTRAINT fk_course_enrollment_ceid FOREIGN KEY(course_edition_id)
359 REFERENCES __t_Course_Edition(id)
360 ON DELETE SET NULL ON UPDATE CASCADE,
361
362 CONSTRAINT uq_course_enrollment_course_once_per_semenroll UNIQUE(semester_enrollment_id, course_edition_id)
363);
364
365CREATE TABLE t_Exercise (
366 id INTEGER GENERATED ALWAYS AS IDENTITY,
367
368 course_edition_id INTEGER,
369
370 deadline DATE,
371 max_grade INTEGER,
372 created_at DATE,
373
374 title TEXT,
375 task TEXT,
376 expected_result TEXT,
377
378
379 CONSTRAINT pk_exercise PRIMARY KEY(id),
380
381 CONSTRAINT fk_exercise FOREIGN KEY(course_edition_id)
382 REFERENCES __t_Course_Edition(id)
383 ON DELETE SET NULL ON UPDATE CASCADE,
384
385 CONSTRAINT nn_exercise_title CHECK(title IS NOT NULL),
386 CONSTRAINT nn_exercise_task CHECK(task IS NOT NULL),
387 CONSTRAINT nn_exercise_expected_result CHECK(expected_result IS NOT NULL),
388 CONSTRAINT nn_exercise_max_grade CHECK(max_grade IS NOT NULL),
389 CONSTRAINT nn_exercise_created_at CHECK(created_at IS NOT NULL),
390 CONSTRAINT ck_exercise_max_grade CHECK(max_grade > 0),
391 CONSTRAINT ck_exercise_deadline_after_created CHECK (deadline IS NULL OR deadline > created_at)
392);
393
394
395
396CREATE TABLE t_Exercise_Submission (
397 submitted_at TIMESTAMP WITHOUT TIME ZONE,
398 graded_at TIMESTAMP WITHOUT TIME ZONE,
399
400 grade INTEGER,
401
402 id INTEGER GENERATED ALWAYS AS IDENTITY,
403 exercise_id INTEGER,
404 course_enrollment_id INTEGER,
405
406 content TEXT,
407
408
409
410 CONSTRAINT pk_exercise_submission PRIMARY KEY(id, exercise_id, course_enrollment_id),
411
412 CONSTRAINT fk_exercise_submission_exercise_id FOREIGN KEY(exercise_id)
413 REFERENCES t_Exercise(id)
414 ON DELETE SET NULL ON UPDATE CASCADE,
415 CONSTRAINT fk_exercise_submission_ceid FOREIGN KEY(course_enrollment_id)
416 REFERENCES t_Course_Enrollment(id)
417 ON DELETE SET NULL ON UPDATE CASCADE,
418
419 CONSTRAINT nn_exercise_submission_content CHECK(content IS NOT NULL),
420 CONSTRAINT nn_exercise_submission_submission_submitted_at CHECK(submitted_at IS NOT NULL),
421 CONSTRAINT ck_exercise_submission_submission_graded_after_submitted CHECK (graded_at >= submitted_at),
422 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))
423);
424
425CREATE TABLE t_Survey (
426 open_until TIMESTAMP WITHOUT TIME ZONE,
427 id INTEGER GENERATED ALWAYS AS IDENTITY,
428
429 course_edition_id INTEGER,
430
431 title TEXT,
432
433
434 CONSTRAINT pk_survey PRIMARY KEY(id),
435
436 CONSTRAINT fk_survey FOREIGN KEY(course_edition_id)
437 REFERENCES __t_Course_Edition(id)
438 ON DELETE SET NULL ON UPDATE CASCADE,
439
440 CONSTRAINT nn_survey_title CHECK(title IS NOT NULL)
441);
442
443CREATE TABLE t_Survey_Option (
444 survey_id INTEGER,
445
446 capacity INTEGER,
447
448 option TEXT,
449
450
451 CONSTRAINT pk_survey_option PRIMARY KEY(option, survey_id),
452
453 CONSTRAINT fk_survey_option FOREIGN KEY(survey_id)
454 REFERENCES t_Survey(id)
455 ON DELETE SET NULL ON UPDATE CASCADE,
456
457 CONSTRAINT ck_survey_option_capacity CHECK(capacity > 0)
458);
459
460CREATE TABLE t_Survey_Response (
461 responded_at TIMESTAMP WITHOUT TIME ZONE,
462
463 survey_id INTEGER,
464 course_enrollment_id INTEGER,
465
466 option_text TEXT,
467
468
469 CONSTRAINT pk_survey_response PRIMARY KEY(option_text, survey_id, course_enrollment_id),
470
471 CONSTRAINT fk_survey_response_survey_id FOREIGN KEY(survey_id)
472 REFERENCES t_Survey(id)
473 ON DELETE SET NULL ON UPDATE CASCADE,
474
475 CONSTRAINT fk_survey_response_ceid FOREIGN KEY(course_enrollment_id)
476 REFERENCES t_Course_Enrollment(id)
477 ON DELETE SET NULL ON UPDATE CASCADE,
478
479 CONSTRAINT nn_survey_response_responded_at CHECK(responded_at IS NOT NULL)
480);
481CREATE TABLE __t_Student_Grade (
482 course_enrollment_id INTEGER,
483
484 graded_at DATE,
485
486 aggr_course_edition_res_id INTEGER,
487
488 grade UDV_STUDENT_GRADE_GRADE,
489
490
491 CONSTRAINT pk_student_grade PRIMARY KEY(course_enrollment_id),
492
493 CONSTRAINT fk_student_grade FOREIGN KEY(course_enrollment_id)
494 REFERENCES t_Course_Enrollment(id)
495 ON DELETE SET NULL ON UPDATE CASCADE,
496
497 CONSTRAINT fk_student_aceri FOREIGN KEY(aggr_course_edition_res_id)
498 REFERENCES t_Aggregated_Course_Edition_Results(id)
499 ON DELETE SET NULL ON UPDATE CASCADE,
500
501 CONSTRAINT nn_student_grade_grade CHECK(grade IS NOT NULL),
502 CONSTRAINT nn_student_grade_graded_at CHECK(graded_at IS NOT NULL)
503);
504
505
506CREATE TABLE t_Exam (
507 scheduled_at TIMESTAMP WITHOUT TIME ZONE,
508
509 id INTEGER GENERATED ALWAYS AS IDENTITY,
510
511 course_edition_id INTEGER,
512
513
514 duration_minutes SMALLINT,
515
516 type EXAM_TYPE,
517
518
519 CONSTRAINT pk_exam PRIMARY KEY(id),
520
521 CONSTRAINT fk_exam FOREIGN KEY(course_edition_id)
522 REFERENCES __t_Course_Edition(id)
523 ON DELETE SET NULL ON UPDATE CASCADE,
524
525 CONSTRAINT nn_exam_type CHECK(type IS NOT NULL),
526 CONSTRAINT ck_exam_duration_minutes CHECK(duration_minutes > 0)
527);
528
529
530CREATE TABLE t_Exam_Attempt (
531 submitted_at TIMESTAMP WITHOUT TIME ZONE,
532
533 exam_id INTEGER,
534 course_enrollment_id INTEGER,
535
536 total_points FLOAT,
537
538 attempt_number SMALLINT,
539
540
541 CONSTRAINT pk_exam_attempt PRIMARY KEY(attempt_number, exam_id, course_enrollment_id),
542
543 CONSTRAINT fk_exam_attempt_exam_id FOREIGN KEY(exam_id)
544 REFERENCES t_Exam(id)
545 ON DELETE SET NULL ON UPDATE CASCADE,
546 CONSTRAINT fk_exam_attempt_ceid FOREIGN KEY(course_enrollment_id)
547 REFERENCES t_Course_Enrollment(id)
548 ON DELETE SET NULL ON UPDATE CASCADE,
549
550 CONSTRAINT nn_exam_attempt_submitted_at CHECK(submitted_at IS NOT NULL),
551 CONSTRAINT nn_exam_attempt_total_points CHECK(total_points IS NOT NULL),
552 CONSTRAINT ck_exam_attempt_total_points CHECK(total_points >= 0)
553);
554
555CREATE TABLE t_Exam_Problem (
556 id INTEGER GENERATED ALWAYS AS IDENTITY,
557 exam_id INTEGER,
558
559 points FLOAT,
560
561 description TEXT,
562 expected TEXT,
563
564
565 CONSTRAINT pk_exam_problem PRIMARY KEY(id, exam_id),
566
567 CONSTRAINT fk_exam_problem FOREIGN KEY(exam_id)
568 REFERENCES t_Exam(id)
569 ON DELETE SET NULL ON UPDATE CASCADE,
570
571 CONSTRAINT nn_exam_problem_description CHECK(description IS NOT NULL),
572 CONSTRAINT nn_exam_problem_expected CHECK(expected IS NOT NULL),
573 CONSTRAINT nn_exam_problem_points CHECK(points IS NOT NULL),
574 CONSTRAINT ck_exam_problem_points CHECK(points > 0.0)
575);
576
577CREATE TABLE t_Student_Answer (
578 exam_id INTEGER,
579 exam_problem_id INTEGER,
580 exam_attempt_ceid INTEGER,
581
582 points_acquired FLOAT,
583
584 exam_attempt_attempt_number SMALLINT,
585
586 answer TEXT,
587
588
589 CONSTRAINT pk_student_answer
590 PRIMARY KEY(
591 exam_id,
592 exam_problem_id,
593 exam_attempt_ceid,
594 exam_attempt_attempt_number
595 ),
596
597 CONSTRAINT fk_student_answer_exam FOREIGN KEY(exam_id)
598 REFERENCES t_Exam(id)
599 ON DELETE SET NULL ON UPDATE CASCADE,
600 CONSTRAINT fk_student_answer_exam_problem FOREIGN KEY(exam_id, exam_problem_id)
601 REFERENCES t_Exam_Problem(exam_id, id)
602 ON DELETE SET NULL ON UPDATE CASCADE,
603 CONSTRAINT fk_student_answer_exam_attempt FOREIGN KEY(exam_id, exam_attempt_ceid, exam_attempt_attempt_number)
604 REFERENCES t_Exam_Attempt(exam_id, course_enrollment_id, attempt_number)
605 ON DELETE SET NULL ON UPDATE CASCADE,
606
607 CONSTRAINT nn_student_answer_points_acquired CHECK(points_acquired IS NOT NULL),
608 CONSTRAINT ck_student_answer_points_acquired CHECK(points_acquired >= 0)
609);
610
611CREATE TABLE t_Exam_Results (
612 exported_at TIMESTAMP WITHOUT TIME ZONE,
613 teaches_teacher_id INTEGER,
614 teaches_course_edition_id INTEGER,
615 exam_id INTEGER,
616 id INTEGER GENERATED ALWAYS AS IDENTITY,
617 data jsonb,
618
619 CONSTRAINT pk_exam_results PRIMARY KEY (id),
620
621 CONSTRAINT fk_exam_results_teaches FOREIGN KEY (teaches_teacher_id, teaches_course_edition_id)
622 REFERENCES t_Teaches (teacher_id, course_edition_id)
623 ON DELETE SET NULL ON UPDATE CASCADE,
624 CONSTRAINT fk_exam_results_exam FOREIGN KEY (exam_id)
625 REFERENCES t_Exam (id)
626 ON DELETE SET NULL ON UPDATE CASCADE,
627
628 CONSTRAINT uq_exam_results_ette UNIQUE (exported_at, teaches_teacher_id, teaches_course_edition_id, exam_id),
629 CONSTRAINT nn_exam_results_exported_at CHECK (exported_at IS NOT NULL),
630 CONSTRAINT nn_exam_results_data CHECK (data IS NOT NULL),
631 CONSTRAINT ck_exam_results_exported_at CHECK (exported_at <= CURRENT_TIMESTAMP),
632 CONSTRAINT ck_exam_results_data CHECK (fn_validate_exam_data(data))
633);
634
635CREATE TABLE t_Aggregated_Course_Edition_Results (
636 exported_at TIMESTAMP WITHOUT TIME ZONE,
637 teaches_teacher_id INTEGER,
638 teaches_course_edition_id INTEGER,
639 id INTEGER GENERATED ALWAYS AS IDENTITY,
640 data jsonb,
641
642 CONSTRAINT pk_aggregated_course_edition_results PRIMARY KEY (id),
643
644 CONSTRAINT fk_aggregated_results_teaches FOREIGN KEY (teaches_teacher_id, teaches_course_edition_id)
645 REFERENCES t_Teaches (teacher_id, course_edition_id)
646 ON DELETE SET NULL ON UPDATE CASCADE,
647
648 CONSTRAINT uq_aggregated_results_ette UNIQUE (exported_at, teaches_teacher_id, teaches_course_edition_id),
649 CONSTRAINT nn_aggregated_results_exported_at CHECK (exported_at IS NOT NULL),
650 CONSTRAINT nn_aggregated_results_data CHECK (data IS NOT NULL),
651 CONSTRAINT ck_aggregated_results_data CHECK (fn_validate_aggregated_data(data))
652);