source: sql/ddl.sql

Last change on this file was c613cbf, checked in by MBK <marija.karapandzova@…>, 4 months ago

Add exceptions
  • Property mode set to 100644
File size: 37.4 KB
RevLine 
[c613cbf]1
2BEGIN;
3
4DROP TABLE IF EXISTS medical_record_allergies CASCADE;
5DROP TABLE IF EXISTS medical_record_symptoms CASCADE;
6DROP TABLE IF EXISTS medical_record_procedures CASCADE;
7DROP TABLE IF EXISTS medical_record_lab_results CASCADE;
8DROP TABLE IF EXISTS performed_lab_tests CASCADE;
9DROP TABLE IF EXISTS performed_procedures CASCADE;
10DROP TABLE IF EXISTS medical_report_lab_results CASCADE;
11DROP TABLE IF EXISTS prescription_medical_records CASCADE;
12DROP TABLE IF EXISTS diagnosis_medical_records CASCADE;
13DROP TABLE IF EXISTS doctor_medical_records CASCADE;
14DROP TABLE IF EXISTS billing_lab_tests CASCADE;
15DROP TABLE IF EXISTS billing_procedures CASCADE;
16DROP TABLE IF EXISTS department_procedures CASCADE;
17DROP TABLE IF EXISTS diagnosis_procedures CASCADE;
18DROP TABLE IF EXISTS specialization_procedures CASCADE;
19DROP TABLE IF EXISTS diagnosis_symptoms CASCADE;
20DROP TABLE IF EXISTS patient_symptoms CASCADE;
21DROP TABLE IF EXISTS allergy_prescription_restrictions CASCADE;
22DROP TABLE IF EXISTS patient_allergies CASCADE;
23DROP TABLE IF EXISTS symptoms CASCADE;
24DROP TABLE IF EXISTS allergies CASCADE;
25
26DROP TABLE IF EXISTS billing CASCADE;
27DROP TABLE IF EXISTS medical_report CASCADE;
28DROP TABLE IF EXISTS referrals CASCADE;
29DROP TABLE IF EXISTS medical_records CASCADE;
30DROP TABLE IF EXISTS lab_results CASCADE;
31DROP TABLE IF EXISTS lab_tests CASCADE;
32DROP TABLE IF EXISTS prescription_restriction CASCADE;
33DROP TABLE IF EXISTS prescriptions CASCADE;
34DROP TABLE IF EXISTS procedure_results CASCADE;
35DROP TABLE IF EXISTS procedures CASCADE;
36DROP TABLE IF EXISTS diagnosis CASCADE;
37DROP TABLE IF EXISTS appointments CASCADE;
38DROP TABLE IF EXISTS lab_technician CASCADE;
39DROP TABLE IF EXISTS admin CASCADE;
40DROP TABLE IF EXISTS patients CASCADE;
41DROP TABLE IF EXISTS doctors CASCADE;
42DROP TABLE IF EXISTS departments CASCADE;
43DROP TABLE IF EXISTS doctor_specialization CASCADE;
44DROP TABLE IF EXISTS doctor_level CASCADE;
45
46
47
48
49CREATE TABLE doctor_level (
50 level_id BIGINT,
51 level TEXT NOT NULL,
52
53 CONSTRAINT doctor_level_PK
54 PRIMARY KEY (level_id),
55
56 CONSTRAINT doctor_level_level_UQ
57 UNIQUE (level)
58);
59
60CREATE TABLE doctor_specialization (
61 specialization_id BIGINT,
62 specialization_name TEXT NOT NULL,
63
64 CONSTRAINT doctor_specialization_PK
65 PRIMARY KEY (specialization_id),
66
67 CONSTRAINT doctor_specialization_name_UQ
68 UNIQUE (specialization_name)
69);
70
71
72CREATE TABLE departments (
73 department_id BIGINT,
74 department_name TEXT NOT NULL,
75
76 CONSTRAINT departments_PK
77 PRIMARY KEY (department_id),
78
79 CONSTRAINT departments_name_UQ
80 UNIQUE (department_name)
81);
82
83CREATE TABLE doctors (
84 doctor_id BIGINT,
85
86 first_name TEXT NOT NULL,
87 last_name TEXT NOT NULL,
88
89 email_address TEXT NOT NULL,
90
91 level_id BIGINT NOT NULL,
92 specialization_id BIGINT NOT NULL,
93 department_id BIGINT NOT NULL,
94
95 CONSTRAINT doctors_PK
96 PRIMARY KEY (doctor_id),
97
98 CONSTRAINT doctors_email_UQ
99 UNIQUE (email_address),
100
101 CONSTRAINT doctors_email_chk
102 CHECK (email_address LIKE '%@%'),
103
104 CONSTRAINT doctors_level_FK
105 FOREIGN KEY (level_id)
106 REFERENCES doctor_level(level_id)
107 ON DELETE RESTRICT,
108
109 CONSTRAINT doctors_specialization_FK
110 FOREIGN KEY (specialization_id)
111 REFERENCES doctor_specialization(specialization_id)
112 ON DELETE RESTRICT,
113
114 CONSTRAINT doctors_department_FK
115 FOREIGN KEY (department_id)
116 REFERENCES departments(department_id)
117 ON DELETE RESTRICT
118);
119
120
121
122CREATE TABLE patients (
123 patient_id BIGINT,
124
125 first_name TEXT NOT NULL,
126 last_name TEXT NOT NULL,
127
128 email_address TEXT,
129 date_of_birth DATE NOT NULL,
130
131 blood_type TEXT,
132 gender TEXT,
133
134 phone_number TEXT,
135 embg TEXT NOT NULL,
136
137 CONSTRAINT patients_PK
138 PRIMARY KEY (patient_id),
139
140 CONSTRAINT patients_email_UQ
141 UNIQUE (email_address),
142
143 CONSTRAINT patients_embg_UQ
144 UNIQUE (embg),
145
146 CONSTRAINT patients_gender_chk
147 CHECK (gender IN ('MALE', 'FEMALE')),
148
149 CONSTRAINT patients_blood_type_chk
150 CHECK (
151 blood_type IN (
152 'A+', 'A-',
153 'B+', 'B-',
154 'AB+', 'AB-',
155 'O+', 'O-'
156 )
157 )
158);
159
160CREATE TABLE admin (
161 admin_id BIGINT,
162
163 username TEXT NOT NULL,
164 name TEXT NOT NULL,
165 lastname TEXT NOT NULL,
166
167 email TEXT NOT NULL,
168
169 CONSTRAINT admin_PK
170 PRIMARY KEY (admin_id),
171
172 CONSTRAINT admin_username_UQ
173 UNIQUE (username),
174
175 CONSTRAINT admin_email_UQ
176 UNIQUE (email),
177
178 CONSTRAINT admin_email_chk
179 CHECK (email LIKE '%@adminmedora%')
180);
181
182CREATE TABLE lab_technician (
183 technician_id BIGINT,
184
185 username TEXT NOT NULL,
186 name TEXT NOT NULL,
187 lastname TEXT NOT NULL,
188
189 email TEXT NOT NULL,
190
191 CONSTRAINT lab_technician_PK
192 PRIMARY KEY (technician_id),
193
194 CONSTRAINT lab_technician_username_UQ
195 UNIQUE (username),
196
197 CONSTRAINT lab_technician_email_UQ
198 UNIQUE (email),
199
200 CONSTRAINT lab_technician_email_chk
201 CHECK (email LIKE '%@labmedora%')
202);
203
204
205
206CREATE TABLE appointments (
207 appointment_id BIGINT,
208
209 appointment_date DATE NOT NULL,
210 appointment_time TIME NOT NULL,
211
212 status TEXT NOT NULL,
213
214 patient_id BIGINT NOT NULL,
215 doctor_id BIGINT NOT NULL,
216
217 CONSTRAINT appointments_PK
218 PRIMARY KEY (appointment_id),
219
220 CONSTRAINT appointments_status_chk
221 CHECK (
222 status IN (
223 'SCHEDULED',
224 'COMPLETED',
225 'CANCELLED',
226 'IN_PROGRESS'
227 )
228 ),
229
230 CONSTRAINT appointments_patient_FK
231 FOREIGN KEY (patient_id)
232 REFERENCES patients(patient_id)
233 ON DELETE RESTRICT,
234
235 CONSTRAINT appointments_doctor_FK
236 FOREIGN KEY (doctor_id)
237 REFERENCES doctors(doctor_id)
238 ON DELETE RESTRICT
239);
240
241CREATE TABLE diagnosis (
242 diagnosis_id BIGINT,
243
244 name TEXT NOT NULL,
245 description TEXT,
246
247 patient_id BIGINT NOT NULL,
248 doctor_id BIGINT NOT NULL,
249
250 CONSTRAINT diagnosis_PK
251 PRIMARY KEY (diagnosis_id),
252
253 CONSTRAINT diagnosis_patient_FK
254 FOREIGN KEY (patient_id)
255 REFERENCES patients(patient_id)
256 ON DELETE RESTRICT,
257
258 CONSTRAINT diagnosis_doctor_FK
259 FOREIGN KEY (doctor_id)
260 REFERENCES doctors(doctor_id)
261 ON DELETE RESTRICT
262);
263
264
265
266
267CREATE TABLE procedures (
268 procedure_id BIGINT,
269
270 procedure_type TEXT NOT NULL,
271 procedure_date DATE NOT NULL,
272
273 description TEXT,
274 cost DECIMAL NOT NULL,
275
276 doctor_id BIGINT NOT NULL,
277 diagnosis_id BIGINT NOT NULL,
278
279 CONSTRAINT procedures_PK
280 PRIMARY KEY (procedure_id),
281
282 CONSTRAINT procedures_cost_chk
283 CHECK (cost >= 0),
284
285 CONSTRAINT procedures_type_chk
286 CHECK (length(trim(procedure_type)) > 0),
287
288 CONSTRAINT procedures_doctor_FK
289 FOREIGN KEY (doctor_id)
290 REFERENCES doctors(doctor_id)
291 ON DELETE RESTRICT,
292
293 CONSTRAINT procedures_diagnosis_FK
294 FOREIGN KEY (diagnosis_id)
295 REFERENCES diagnosis(diagnosis_id)
296 ON DELETE RESTRICT
297);
298
299
300
301
302CREATE TABLE procedure_results (
303 result_id BIGINT,
304
305 result_description TEXT,
306 result_date DATE NOT NULL,
307
308 procedure_id BIGINT NOT NULL,
309
310 CONSTRAINT procedure_results_PK
311 PRIMARY KEY (result_id),
312
313 CONSTRAINT procedure_results_description_chk
314 CHECK (
315 result_description IS NULL
316 OR length(trim(result_description)) > 0
317 ),
318
319 CONSTRAINT procedure_results_procedure_FK
320 FOREIGN KEY (procedure_id)
321 REFERENCES procedures(procedure_id)
322 ON DELETE RESTRICT
323);
324
325CREATE TABLE prescriptions (
326 prescription_id BIGINT,
327
328 medication_name TEXT NOT NULL,
329
330 CONSTRAINT prescriptions_PK
331 PRIMARY KEY (prescription_id)
332);
333
334CREATE TABLE prescription_restriction (
335 restriction_id BIGINT,
336
337 description TEXT NOT NULL,
338
339 prescription_id BIGINT NOT NULL,
340
341 CONSTRAINT prescription_restriction_PK
342 PRIMARY KEY (restriction_id),
343
344 CONSTRAINT prescription_restriction_desc_chk
345 CHECK (length(trim(description)) > 0),
346
347 CONSTRAINT prescription_restriction_prescription_FK
348 FOREIGN KEY (prescription_id)
349 REFERENCES prescriptions(prescription_id)
350 ON DELETE RESTRICT
351);
352
353
354CREATE TABLE lab_tests (
355 test_id BIGINT,
356
357 test_name TEXT NOT NULL,
358 description TEXT,
359 cost DECIMAL NOT NULL,
360
361 CONSTRAINT lab_tests_PK
362 PRIMARY KEY (test_id),
363
364 CONSTRAINT lab_tests_cost_chk
365 CHECK (cost >= 0),
366
367 CONSTRAINT lab_tests_name_chk
368 CHECK (length(trim(test_name)) > 0)
369);
370
371
372CREATE TABLE lab_results (
373 result_id BIGINT,
374
375 results TEXT NOT NULL,
376 result_date DATE NOT NULL,
377
378 test_id BIGINT NOT NULL,
379
380 CONSTRAINT lab_results_PK
381 PRIMARY KEY (result_id),
382
383 CONSTRAINT lab_results_results_chk
384 CHECK (length(trim(results)) > 0),
385
386 CONSTRAINT lab_results_date_chk
387 CHECK (result_date <= CURRENT_DATE),
388
389 CONSTRAINT lab_results_test_FK
390 FOREIGN KEY (test_id)
391 REFERENCES lab_tests(test_id)
392 ON DELETE RESTRICT
393);
394
395CREATE TABLE medical_records (
396 record_id BIGINT PRIMARY KEY,
397
398 patient_id BIGINT NOT NULL,
399
400
401 FOREIGN KEY (patient_id)
402 REFERENCES patients(patient_id)
403 ON DELETE RESTRICT
404);
405
406
407
408
409CREATE TABLE allergies (
410 allergy_id BIGINT,
411
412 name TEXT NOT NULL,
413 allergy_severity TEXT NOT NULL,
414
415 CONSTRAINT allergies_PK
416 PRIMARY KEY (allergy_id),
417
418 CONSTRAINT allergies_name_UQ
419 UNIQUE (name),
420
421 CONSTRAINT allergies_name_chk
422 CHECK (length(trim(name)) > 0),
423
424 CONSTRAINT allergies_severity_chk
425 CHECK (allergy_severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL'))
426);
427
428
429CREATE TABLE symptoms (
430 symptom_id BIGINT,
431
432 name TEXT NOT NULL,
433 description TEXT,
434
435 CONSTRAINT symptoms_PK
436 PRIMARY KEY (symptom_id),
437
438 CONSTRAINT symptoms_name_UQ
439 UNIQUE (name),
440
441 CONSTRAINT symptoms_name_chk
442 CHECK (length(trim(name)) > 0),
443
444 CONSTRAINT symptoms_description_chk
445 CHECK (
446 description IS NULL
447 OR length(trim(description)) > 0
448 )
449);
450
451
452CREATE TABLE referrals (
453
454 referral_id BIGINT PRIMARY KEY,
455
456 reason TEXT NOT NULL,
457 referral_date DATE NOT NULL,
458
459 record_id BIGINT NOT NULL,
460 from_doctor_id BIGINT NOT NULL,
461 to_doctor_id BIGINT not null,
462
463 CONSTRAINT referrals_reason_chk
464 CHECK (length(trim(reason)) > 0),
465
466 FOREIGN KEY (record_id)
467 REFERENCES medical_records(record_id)
468 ON DELETE RESTRICT,
469
470 FOREIGN KEY (from_doctor_id)
471 REFERENCES doctors(doctor_id)
472 ON DELETE RESTRICT,
473
474 FOREIGN KEY (to_doctor_id)
475 REFERENCES doctors(doctor_id)
476 ON DELETE RESTRICT
477);
478
479CREATE TABLE medical_report (
480 report_id BIGINT PRIMARY KEY,
481
482 description TEXT NOT NULL,
483 report_date DATE NOT NULL,
484
485 record_id BIGINT NOT NULL,
486 doctor_id BIGINT NOT NULL,
487
488 FOREIGN KEY (record_id)
489 REFERENCES medical_records(record_id)
490 ON DELETE RESTRICT,
491
492 FOREIGN KEY (doctor_id)
493 REFERENCES doctors(doctor_id)
494 ON DELETE RESTRICT
495);
496
497
498CREATE TABLE billing (
499 bill_id BIGINT PRIMARY KEY,
500
501 total_cost DECIMAL NOT NULL,
502
503 payment_status TEXT NOT NULL,
504 payment_date DATE,
505
506 record_id BIGINT NOT NULL,
507 admin_id BIGINT NOT NULL,
508
509 CONSTRAINT billing_cost_chk CHECK (total_cost >= 0),
510 CONSTRAINT billing_status_chk
511 CHECK (payment_status IN ('PENDING', 'PAID', 'CANCELLED')),
512
513 FOREIGN KEY (record_id)
514 REFERENCES medical_records(record_id)
515 ON DELETE RESTRICT,
516
517 FOREIGN KEY (admin_id)
518 REFERENCES admin(admin_id)
519 ON DELETE RESTRICT
520);
521
522
523
524
525CREATE TABLE patient_allergies (
526 patient_id BIGINT NOT NULL,
527 allergy_id BIGINT NOT NULL,
528
529 CONSTRAINT patient_allergies_PK
530 PRIMARY KEY (patient_id, allergy_id),
531
532 CONSTRAINT patient_allergies_patient_FK
533 FOREIGN KEY (patient_id)
534 REFERENCES patients(patient_id)
535 ON DELETE RESTRICT,
536
537 CONSTRAINT patient_allergies_allergy_FK
538 FOREIGN KEY (allergy_id)
539 REFERENCES allergies(allergy_id)
540 ON DELETE RESTRICT
541);
542
543
544
545CREATE TABLE allergy_prescription_restrictions (
546 allergy_id BIGINT NOT NULL,
547 restriction_id BIGINT NOT NULL,
548
549 CONSTRAINT allergy_prescription_restrictions_PK
550 PRIMARY KEY (allergy_id, restriction_id),
551
552 CONSTRAINT allergy_prescription_restrictions_allergy_FK
553 FOREIGN KEY (allergy_id)
554 REFERENCES allergies(allergy_id)
555 ON DELETE RESTRICT,
556
557 CONSTRAINT allergy_prescription_restrictions_restriction_FK
558 FOREIGN KEY (restriction_id)
559 REFERENCES prescription_restriction(restriction_id)
560 ON DELETE RESTRICT
561);
562
563
564
565CREATE TABLE patient_symptoms (
566 patient_id BIGINT NOT NULL,
567 symptom_id BIGINT NOT NULL,
568
569 CONSTRAINT patient_symptoms_PK
570 PRIMARY KEY (patient_id, symptom_id),
571
572 CONSTRAINT patient_symptoms_patient_FK
573 FOREIGN KEY (patient_id)
574 REFERENCES patients(patient_id)
575 ON DELETE RESTRICT,
576
577 CONSTRAINT patient_symptoms_symptom_FK
578 FOREIGN KEY (symptom_id)
579 REFERENCES symptoms(symptom_id)
580 ON DELETE RESTRICT
581);
582
583
584
585
586CREATE TABLE diagnosis_symptoms (
587 diagnosis_id BIGINT NOT NULL,
588 symptom_id BIGINT NOT NULL,
589
590 CONSTRAINT diagnosis_symptoms_PK
591 PRIMARY KEY (diagnosis_id, symptom_id),
592
593 CONSTRAINT diagnosis_symptoms_diagnosis_FK
594 FOREIGN KEY (diagnosis_id)
595 REFERENCES diagnosis(diagnosis_id)
596 ON DELETE RESTRICT,
597
598 CONSTRAINT diagnosis_symptoms_symptom_FK
599 FOREIGN KEY (symptom_id)
600 REFERENCES symptoms(symptom_id)
601 ON DELETE RESTRICT
602);
603
604CREATE TABLE specialization_procedures (
605 specialization_id BIGINT NOT NULL,
606 procedure_id BIGINT NOT NULL,
607
608 CONSTRAINT specialization_procedures_PK
609 PRIMARY KEY (specialization_id, procedure_id),
610
611 CONSTRAINT specialization_procedures_specialization_FK
612 FOREIGN KEY (specialization_id)
613 REFERENCES doctor_specialization(specialization_id)
614 ON DELETE RESTRICT,
615
616 CONSTRAINT specialization_procedures_procedure_FK
617 FOREIGN KEY (procedure_id)
618 REFERENCES procedures(procedure_id)
619 ON DELETE RESTRICT
620);
621
622
623
624CREATE TABLE diagnosis_procedures (
625 diagnosis_id BIGINT NOT NULL,
626 procedure_id BIGINT NOT NULL,
627
628 CONSTRAINT diagnosis_procedures_PK
629 PRIMARY KEY (diagnosis_id, procedure_id),
630
631 CONSTRAINT diagnosis_procedures_diagnosis_FK
632 FOREIGN KEY (diagnosis_id)
633 REFERENCES diagnosis(diagnosis_id)
634 ON DELETE RESTRICT,
635
636 CONSTRAINT diagnosis_procedures_procedure_FK
637 FOREIGN KEY (procedure_id)
638 REFERENCES procedures(procedure_id)
639 ON DELETE RESTRICT
640);
641
642CREATE TABLE department_procedures (
643 department_id BIGINT NOT NULL ,
644 procedure_id BIGINT NOT NULL,
645
646 CONSTRAINT department_procedures_PK
647 PRIMARY KEY (department_id, procedure_id),
648
649 CONSTRAINT department_procedures_department_FK
650 FOREIGN KEY (department_id)
651 REFERENCES departments(department_id)
652 ON DELETE RESTRICT,
653
654 CONSTRAINT department_procedures_procedure_FK
655 FOREIGN KEY (procedure_id)
656 REFERENCES procedures(procedure_id)
657 ON DELETE RESTRICT
658);
659
660
661CREATE TABLE billing_procedures (
662 bill_id BIGINT NOT NULL,
663 procedure_id BIGINT NOT NULL,
664
665 CONSTRAINT billing_procedures_PK
666 PRIMARY KEY (bill_id, procedure_id),
667
668 CONSTRAINT billing_procedures_bill_FK
669 FOREIGN KEY (bill_id)
670 REFERENCES billing(bill_id)
671 ON DELETE RESTRICT,
672
673 CONSTRAINT billing_procedures_procedure_FK
674 FOREIGN KEY (procedure_id)
675 REFERENCES procedures(procedure_id)
676 ON DELETE RESTRICT
677);
678
679
680
681
682CREATE TABLE billing_lab_tests (
683 bill_id BIGINT NOT NULL,
684 test_id BIGINT NOT NULL,
685
686 CONSTRAINT billing_lab_tests_PK
687 PRIMARY KEY (bill_id, test_id),
688
689 CONSTRAINT billing_lab_tests_bill_FK
690 FOREIGN KEY (bill_id)
691 REFERENCES billing(bill_id)
692 ON DELETE RESTRICT,
693
694 CONSTRAINT billing_lab_tests_test_FK
695 FOREIGN KEY (test_id)
696 REFERENCES lab_tests(test_id)
697 ON DELETE RESTRICT
698);
699
700
701
702CREATE TABLE doctor_medical_records (
703 doctor_id BIGINT NOT NULL,
704 record_id BIGINT NOT NULL,
705
706 CONSTRAINT doctor_medical_records_PK
707 PRIMARY KEY (doctor_id, record_id),
708
709 CONSTRAINT doctor_medical_records_doctor_FK
710 FOREIGN KEY (doctor_id)
711 REFERENCES doctors(doctor_id)
712 ON DELETE RESTRICT,
713
714 CONSTRAINT doctor_medical_records_record_FK
715 FOREIGN KEY (record_id)
716 REFERENCES medical_records(record_id)
717 ON DELETE RESTRICT
718);
719
720
721
722CREATE TABLE diagnosis_medical_records (
723 diagnosis_id BIGINT NOT NULL,
724 record_id BIGINT NOT NULL,
725
726 CONSTRAINT diagnosis_medical_records_PK
727 PRIMARY KEY (diagnosis_id, record_id),
728
729 CONSTRAINT diagnosis_medical_records_diagnosis_FK
730 FOREIGN KEY (diagnosis_id)
731 REFERENCES diagnosis(diagnosis_id)
732 ON DELETE RESTRICT,
733
734 CONSTRAINT diagnosis_medical_records_record_FK
735 FOREIGN KEY (record_id)
736 REFERENCES medical_records(record_id)
737 ON DELETE RESTRICT
738);
739
740
741CREATE TABLE prescription_medical_records (
742 prescription_id BIGINT NOT NULL,
743 record_id BIGINT NOT NULL,
744
745 dosage TEXT NOT NULL,
746 frequency TEXT NOT NULL,
747 duration TEXT NOT NULL,
748
749 notes TEXT,
750
751 CONSTRAINT prescription_medical_records_PK
752 PRIMARY KEY (prescription_id, record_id),
753
754 CONSTRAINT prescription_medical_records_prescription_FK
755 FOREIGN KEY (prescription_id)
756 REFERENCES prescriptions(prescription_id)
757 ON DELETE RESTRICT,
758
759 CONSTRAINT prescription_medical_records_record_FK
760 FOREIGN KEY (record_id)
761 REFERENCES medical_records(record_id)
762 ON DELETE RESTRICT
763);
764
765
766
767CREATE TABLE medical_report_lab_results (
768 report_id BIGINT NOT NULL,
769 result_id BIGINT NOT NULL,
770
771 CONSTRAINT medical_report_lab_results_PK
772 PRIMARY KEY (report_id, result_id),
773
774 CONSTRAINT medical_report_lab_results_report_FK
775 FOREIGN KEY (report_id)
776 REFERENCES medical_report(report_id)
777 ON DELETE RESTRICT,
778
779 CONSTRAINT medical_report_lab_results_result_FK
780 FOREIGN KEY (result_id)
781 REFERENCES lab_results(result_id)
782 ON DELETE RESTRICT
783);
784
785
786
787CREATE TABLE performed_procedures (
788 performed_id BIGINT,
789
790 procedure_id BIGINT NOT NULL,
791 doctor_id BIGINT NOT NULL,
792 patient_id BIGINT NOT NULL,
793 diagnosis_id BIGINT,
794
795 procedure_date DATE NOT NULL,
796 notes TEXT,
797
798 CONSTRAINT performed_procedures_PK
799 PRIMARY KEY (performed_id),
800
801 CONSTRAINT performed_procedures_procedure_FK
802 FOREIGN KEY (procedure_id)
803 REFERENCES procedures(procedure_id)
804 ON DELETE RESTRICT,
805
806 CONSTRAINT performed_procedures_doctor_FK
807 FOREIGN KEY (doctor_id)
808 REFERENCES doctors(doctor_id)
809 ON DELETE RESTRICT,
810
811 CONSTRAINT performed_procedures_patient_FK
812 FOREIGN KEY (patient_id)
813 REFERENCES patients(patient_id)
814 ON DELETE RESTRICT,
815
816 CONSTRAINT performed_procedures_diagnosis_FK
817 FOREIGN KEY (diagnosis_id)
818 REFERENCES diagnosis(diagnosis_id)
819 ON DELETE RESTRICT
820);
821
822CREATE TABLE performed_lab_tests (
823 performed_test_id BIGINT,
824
825 test_id BIGINT NOT NULL,
826 patient_id BIGINT NOT NULL,
827 doctor_id BIGINT NOT NULL,
828 technician_id BIGINT NOT NULL,
829
830 test_date DATE NOT NULL,
831 notes TEXT,
832
833 CONSTRAINT performed_lab_tests_PK
834 PRIMARY KEY (performed_test_id),
835
836 CONSTRAINT performed_lab_tests_test_FK
837 FOREIGN KEY (test_id)
838 REFERENCES lab_tests(test_id)
839 ON DELETE RESTRICT,
840
841 CONSTRAINT performed_lab_tests_patient_FK
842 FOREIGN KEY (patient_id)
843 REFERENCES patients(patient_id)
844 ON DELETE RESTRICT,
845
846 CONSTRAINT performed_lab_tests_doctor_FK
847 FOREIGN KEY (doctor_id)
848 REFERENCES doctors(doctor_id)
849 ON DELETE RESTRICT,
850
851 CONSTRAINT performed_lab_tests_technician_FK
852 FOREIGN KEY (technician_id)
853 REFERENCES lab_technician(technician_id)
854 ON DELETE RESTRICT
855);
856
857CREATE TABLE medical_record_lab_results (
858 record_id BIGINT NOT NULL,
859 result_id BIGINT NOT NULL,
860
861 PRIMARY KEY (record_id, result_id),
862
863 FOREIGN KEY (record_id)
864 REFERENCES medical_records(record_id)
865 ON DELETE RESTRICT,
866
867 FOREIGN KEY (result_id)
868 REFERENCES lab_results(result_id)
869 ON DELETE RESTRICT
870);
871
872
873
874
875CREATE TABLE medical_record_procedures (
876 record_id BIGINT NOT NULL,
877 procedure_id BIGINT NOT NULL,
878
879 PRIMARY KEY (record_id, procedure_id),
880
881 FOREIGN KEY (record_id)
882 REFERENCES medical_records(record_id)
883 ON DELETE RESTRICT,
884
885 FOREIGN KEY (procedure_id)
886 REFERENCES procedures(procedure_id)
887 ON DELETE RESTRICT
888);
889
890
891
892CREATE TABLE medical_record_symptoms (
893 record_id BIGINT NOT NULL,
894 symptom_id BIGINT NOT NULL,
895 severity TEXT,
896
897 PRIMARY KEY (record_id, symptom_id),
898
899 FOREIGN KEY (record_id)
900 REFERENCES medical_records(record_id)
901 ON DELETE RESTRICT,
902
903 FOREIGN KEY (symptom_id)
904 REFERENCES symptoms(symptom_id)
905 ON DELETE RESTRICT
906);
907
908CREATE TABLE medical_record_allergies (
909 record_id BIGINT NOT NULL,
910 allergy_id BIGINT NOT NULL,
911 reaction TEXT,
912 severity TEXT,
913
914 PRIMARY KEY (record_id, allergy_id),
915
916 FOREIGN KEY (record_id)
917 REFERENCES medical_records(record_id)
918 ON DELETE RESTRICT,
919
920 FOREIGN KEY (allergy_id)
921 REFERENCES allergies(allergy_id)
922 ON DELETE RESTRICT
923);
924
925
926COMMIT;
927
Note: See TracBrowser for help on using the repository browser.