DatabaseCreation: MedicalSystem DDL.txt

File MedicalSystem DDL.txt, 13.5 KB (added by 231561, 13 days ago)

this is the ddl

Line 
1MedicalSystem DDL
2
3CREATE TABLE Hospital (
4 hospital_id INT PRIMARY KEY,
5 hospital_address VARCHAR(255) NOT NULL
6);
7
8CREATE TABLE Specialization (
9 spec_id INT PRIMARY KEY,
10 spec_name VARCHAR(255) NOT NULL UNIQUE
11);
12
13CREATE TABLE Department (
14 department_id INT PRIMARY KEY,
15 department_name VARCHAR(255) NOT NULL,
16 spec_id INT,
17 CONSTRAINT fk_department_specialization
18 FOREIGN KEY (spec_id) REFERENCES Specialization(spec_id)
19 ON UPDATE CASCADE
20 ON DELETE SET NULL
21);
22
23CREATE TABLE Doctor (
24 doctor_id INT PRIMARY KEY,
25 spec_id INT,
26 first_name VARCHAR(255) NOT NULL,
27 last_name VARCHAR(255) NOT NULL,
28 phone VARCHAR(20) UNIQUE,
29 email VARCHAR(100) UNIQUE,
30 CONSTRAINT chk_doctor_phone
31 CHECK (
32 phone IS NULL OR
33 (LENGTH(phone) BETWEEN 7 AND 20 AND phone NOT LIKE '%[A-Za-z]%')
34 ),
35 CONSTRAINT chk_doctor_email
36 CHECK (email IS NULL OR email LIKE '%_@_%._%'),
37 CONSTRAINT fk_doctor_specialization
38 FOREIGN KEY (spec_id) REFERENCES Specialization(spec_id)
39 ON UPDATE CASCADE
40 ON DELETE SET NULL
41);
42
43CREATE TABLE Doctor_department (
44 doctor_dept_id INT PRIMARY KEY,
45 doctor_id INT NOT NULL,
46 department_id INT NOT NULL,
47 employment_type VARCHAR(30),
48 date_from DATE NOT NULL,
49 date_to DATE,
50 CONSTRAINT chk_doctor_department_employment_type
51 CHECK (employment_type IS NULL OR employment_type IN ('FULL_TIME', 'PART_TIME', 'CONSULTANT', 'RESIDENT')),
52 CONSTRAINT chk_doctor_department_dates
53 CHECK (date_to IS NULL OR date_to >= date_from),
54 CONSTRAINT fk_doctor_department_doctor
55 FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
56 ON UPDATE CASCADE
57 ON DELETE RESTRICT,
58 CONSTRAINT fk_doctor_department_department
59 FOREIGN KEY (department_id) REFERENCES Department(department_id)
60 ON UPDATE CASCADE
61 ON DELETE RESTRICT
62);
63
64CREATE TABLE Patient (
65 patient_id INT PRIMARY KEY,
66 first_name VARCHAR(255) NOT NULL,
67 last_name VARCHAR(255) NOT NULL,
68 date_of_birth DATE NOT NULL,
69 gender VARCHAR(20),
70 phone VARCHAR(20) UNIQUE,
71 email VARCHAR(100) UNIQUE,
72 address VARCHAR(255),
73 CONSTRAINT chk_patient_gender
74 CHECK (gender IS NULL OR gender IN ('MALE', 'FEMALE', 'OTHER')),
75 CONSTRAINT chk_patient_dob
76 CHECK (date_of_birth <= CURRENT_DATE),
77 CONSTRAINT chk_patient_phone
78 CHECK (
79 phone IS NULL OR
80 (LENGTH(phone) BETWEEN 7 AND 20 AND phone NOT LIKE '%[A-Za-z]%')
81 ),
82 CONSTRAINT chk_patient_email
83 CHECK (email IS NULL OR email LIKE '%_@_%._%')
84);
85
86CREATE TABLE Referral (
87 referral_id INT PRIMARY KEY,
88 patient_id INT NOT NULL,
89 department_id INT NOT NULL,
90 referred_doctor_id INT,
91 referring_doctor_id INT NOT NULL,
92 CONSTRAINT fk_referral_patient
93 FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
94 ON UPDATE CASCADE
95 ON DELETE RESTRICT,
96 CONSTRAINT fk_referral_department
97 FOREIGN KEY (department_id) REFERENCES Department(department_id)
98 ON UPDATE CASCADE
99 ON DELETE RESTRICT,
100 CONSTRAINT fk_referral_referred_doctor
101 FOREIGN KEY (referred_doctor_id) REFERENCES Doctor(doctor_id)
102 ON UPDATE CASCADE
103 ON DELETE SET NULL,
104 CONSTRAINT fk_referral_referring_doctor
105 FOREIGN KEY (referring_doctor_id) REFERENCES Doctor(doctor_id)
106 ON UPDATE CASCADE
107 ON DELETE RESTRICT,
108 CONSTRAINT chk_referral_doctors_different
109 CHECK (referred_doctor_id IS NULL OR referred_doctor_id <> referring_doctor_id)
110);
111
112CREATE TABLE Appointment (
113 appointment_id INT PRIMARY KEY,
114 referral_id INT,
115 doctor_id INT,
116 patient_id INT NOT NULL,
117 parent_appointment_id INT,
118 prescription_value BOOLEAN NOT NULL DEFAULT FALSE,
119 appointment_date DATE NOT NULL,
120 status VARCHAR(30) NOT NULL DEFAULT 'SCHEDULED',
121 appointment_type VARCHAR(30),
122 priority_level VARCHAR(30),
123 CONSTRAINT chk_appointment_status
124 CHECK (status IN ('SCHEDULED', 'COMPLETED', 'CANCELLED', 'NO_SHOW', 'IN_PROGRESS')),
125 CONSTRAINT chk_appointment_type
126 CHECK (appointment_type IS NULL OR appointment_type IN ('REGULAR', 'FOLLOW_UP', 'EMERGENCY', 'CONTROL')),
127 CONSTRAINT chk_appointment_priority
128 CHECK (priority_level IS NULL OR priority_level IN ('LOW', 'MEDIUM', 'HIGH', 'URGENT')),
129 CONSTRAINT fk_appointment_referral
130 FOREIGN KEY (referral_id) REFERENCES Referral(referral_id)
131 ON UPDATE CASCADE
132 ON DELETE SET NULL,
133 CONSTRAINT fk_appointment_doctor
134 FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
135 ON UPDATE CASCADE
136 ON DELETE SET NULL,
137 CONSTRAINT fk_appointment_patient
138 FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
139 ON UPDATE CASCADE
140 ON DELETE RESTRICT,
141 CONSTRAINT fk_appointment_parent
142 FOREIGN KEY (parent_appointment_id) REFERENCES Appointment(appointment_id)
143 ON UPDATE CASCADE
144 ON DELETE SET NULL
145);
146
147CREATE TABLE Medical_examination (
148 exam_id INT PRIMARY KEY,
149 exam_date DATE NOT NULL,
150 notes TEXT,
151 doctor_id INT NOT NULL,
152 appointment_id INT NOT NULL UNIQUE,
153 CONSTRAINT chk_medical_examination_date
154 CHECK (exam_date <= CURRENT_DATE),
155 CONSTRAINT fk_medical_examination_doctor
156 FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
157 ON UPDATE CASCADE
158 ON DELETE RESTRICT,
159 CONSTRAINT fk_medical_examination_appointment
160 FOREIGN KEY (appointment_id) REFERENCES Appointment(appointment_id)
161 ON UPDATE CASCADE
162 ON DELETE RESTRICT
163);
164
165CREATE TABLE Laboratory_test (
166 lab_id INT PRIMARY KEY,
167 exam_id INT NOT NULL,
168 patient_id INT NOT NULL,
169 doctor_id INT,
170 result VARCHAR(255),
171 test_name VARCHAR(100) NOT NULL,
172 status VARCHAR(30) NOT NULL DEFAULT 'PENDING',
173 CONSTRAINT chk_laboratory_test_status
174 CHECK (status IN ('PENDING', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED')),
175 CONSTRAINT fk_laboratory_test_exam
176 FOREIGN KEY (exam_id) REFERENCES Medical_examination(exam_id)
177 ON UPDATE CASCADE
178 ON DELETE RESTRICT,
179 CONSTRAINT fk_laboratory_test_patient
180 FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
181 ON UPDATE CASCADE
182 ON DELETE RESTRICT,
183 CONSTRAINT fk_laboratory_test_doctor
184 FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
185 ON UPDATE CASCADE
186 ON DELETE SET NULL
187);
188
189CREATE TABLE ICD (
190 icd_id INT PRIMARY KEY,
191 code VARCHAR(20) NOT NULL UNIQUE,
192 description VARCHAR(255) NOT NULL
193);
194
195CREATE TABLE Patient_diagnosis (
196 patient_diagnosis_id INT PRIMARY KEY,
197 patient_id INT NOT NULL,
198 doctor_id INT NOT NULL,
199 exam_id INT NOT NULL,
200 icd_id INT NOT NULL,
201 diagnosis_name VARCHAR(255),
202 is_primary BOOLEAN NOT NULL DEFAULT FALSE,
203 date_from DATE NOT NULL,
204 date_to DATE,
205 CONSTRAINT chk_patient_diagnosis_dates
206 CHECK (date_to IS NULL OR date_to >= date_from),
207 CONSTRAINT fk_patient_diagnosis_patient
208 FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
209 ON UPDATE CASCADE
210 ON DELETE RESTRICT,
211 CONSTRAINT fk_patient_diagnosis_doctor
212 FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
213 ON UPDATE CASCADE
214 ON DELETE RESTRICT,
215 CONSTRAINT fk_patient_diagnosis_exam
216 FOREIGN KEY (exam_id) REFERENCES Medical_examination(exam_id)
217 ON UPDATE CASCADE
218 ON DELETE RESTRICT,
219 CONSTRAINT fk_patient_diagnosis_icd
220 FOREIGN KEY (icd_id) REFERENCES ICD(icd_id)
221 ON UPDATE CASCADE
222 ON DELETE RESTRICT
223);
224
225CREATE TABLE Pharmacy (
226 pharmacy_id INT PRIMARY KEY,
227 address VARCHAR(255) NOT NULL,
228 name VARCHAR(255) NOT NULL,
229 phone VARCHAR(20) UNIQUE,
230 license_num INT NOT NULL UNIQUE,
231 CONSTRAINT chk_pharmacy_phone
232 CHECK (
233 phone IS NULL OR
234 (LENGTH(phone) BETWEEN 7 AND 20 AND phone NOT LIKE '%[A-Za-z]%')
235 )
236);
237
238CREATE TABLE Drug_producers (
239 drug_prod_id INT PRIMARY KEY,
240 address VARCHAR(255),
241 producer_name VARCHAR(255) NOT NULL,
242 country_origin VARCHAR(100),
243 website VARCHAR(100),
244 phone VARCHAR(20),
245 CONSTRAINT chk_drug_producers_phone
246 CHECK (
247 phone IS NULL OR
248 (LENGTH(phone) BETWEEN 7 AND 20 AND phone NOT LIKE '%[A-Za-z]%')
249 )
250);
251
252CREATE TABLE ATC_code (
253 atc_id INT PRIMARY KEY,
254 atc_code VARCHAR(50) NOT NULL UNIQUE,
255 description VARCHAR(255) NOT NULL
256);
257
258CREATE TABLE Drug (
259 product_id INT PRIMARY KEY,
260 drug_prod_id INT NOT NULL,
261 atc_id INT NOT NULL,
262 manufacturer VARCHAR(100),
263 batch_number VARCHAR(50) NOT NULL,
264 expiry_date DATE NOT NULL,
265 unit_price DECIMAL(10,2) NOT NULL,
266 storage_condition VARCHAR(100),
267 CONSTRAINT chk_drug_expiry_date
268 CHECK (expiry_date > CURRENT_DATE),
269 CONSTRAINT chk_drug_unit_price
270 CHECK (unit_price >= 0),
271 CONSTRAINT fk_drug_producer
272 FOREIGN KEY (drug_prod_id) REFERENCES Drug_producers(drug_prod_id)
273 ON UPDATE CASCADE
274 ON DELETE RESTRICT,
275 CONSTRAINT fk_drug_atc
276 FOREIGN KEY (atc_id) REFERENCES ATC_code(atc_id)
277 ON UPDATE CASCADE
278 ON DELETE RESTRICT
279);
280
281CREATE TABLE Inventory (
282 inventory_id INT PRIMARY KEY,
283 product_id INT NOT NULL,
284 pharmacy_id INT NOT NULL,
285 location VARCHAR(100),
286 quantity INT NOT NULL DEFAULT 0,
287 CONSTRAINT chk_inventory_quantity
288 CHECK (quantity >= 0),
289 CONSTRAINT fk_inventory_product
290 FOREIGN KEY (product_id) REFERENCES Drug(product_id)
291 ON UPDATE CASCADE
292 ON DELETE RESTRICT,
293 CONSTRAINT fk_inventory_pharmacy
294 FOREIGN KEY (pharmacy_id) REFERENCES Pharmacy(pharmacy_id)
295 ON UPDATE CASCADE
296 ON DELETE RESTRICT
297);
298
299CREATE TABLE Inventory_price (
300 inventory_price_id INT PRIMARY KEY,
301 purchasing_price DECIMAL(10,2) NOT NULL,
302 referent_price DECIMAL(10,2),
303 sale_price DECIMAL(10,2) NOT NULL,
304 date DATE NOT NULL,
305 inventory_id INT NOT NULL,
306 CONSTRAINT chk_inventory_price_values
307 CHECK (
308 purchasing_price >= 0 AND
309 (referent_price IS NULL OR referent_price >= 0) AND
310 sale_price >= 0
311 ),
312 CONSTRAINT fk_inventory_price_inventory
313 FOREIGN KEY (inventory_id) REFERENCES Inventory(inventory_id)
314 ON UPDATE CASCADE
315 ON DELETE RESTRICT
316);
317
318CREATE TABLE Prescription (
319 presc_id INT PRIMARY KEY,
320 atc_id INT NOT NULL,
321 doctor_id INT NOT NULL,
322 patient_id INT NOT NULL,
323 presc_date DATE NOT NULL,
324 treatment_id INT,
325 duration INT NOT NULL,
326 dosage INT NOT NULL,
327 inventory_id INT NOT NULL,
328 patient_diagnosis_id INT NOT NULL,
329 CONSTRAINT chk_prescription_date
330 CHECK (presc_date <= CURRENT_DATE),
331 CONSTRAINT chk_prescription_duration
332 CHECK (duration > 0),
333 CONSTRAINT chk_prescription_dosage
334 CHECK (dosage > 0),
335 CONSTRAINT fk_prescription_atc
336 FOREIGN KEY (atc_id) REFERENCES ATC_code(atc_id)
337 ON UPDATE CASCADE
338 ON DELETE RESTRICT,
339 CONSTRAINT fk_prescription_doctor
340 FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
341 ON UPDATE CASCADE
342 ON DELETE RESTRICT,
343 CONSTRAINT fk_prescription_patient
344 FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
345 ON UPDATE CASCADE
346 ON DELETE RESTRICT,
347 CONSTRAINT fk_prescription_inventory
348 FOREIGN KEY (inventory_id) REFERENCES Inventory(inventory_id)
349 ON UPDATE CASCADE
350 ON DELETE RESTRICT,
351 CONSTRAINT fk_prescription_patient_diagnosis
352 FOREIGN KEY (patient_diagnosis_id) REFERENCES Patient_diagnosis(patient_diagnosis_id)
353 ON UPDATE CASCADE
354 ON DELETE RESTRICT
355);
356
357CREATE TABLE Pharmacy_sale (
358 sale_id INT PRIMARY KEY,
359 patient_id INT NOT NULL,
360 pharmacy_id INT NOT NULL,
361 presc_id INT NOT NULL,
362 sale_date DATE NOT NULL,
363 total_amount DECIMAL(10,2) NOT NULL,
364 CONSTRAINT chk_pharmacy_sale_date
365 CHECK (sale_date <= CURRENT_DATE),
366 CONSTRAINT chk_pharmacy_sale_total
367 CHECK (total_amount >= 0),
368 CONSTRAINT fk_pharmacy_sale_patient
369 FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
370 ON UPDATE CASCADE
371 ON DELETE RESTRICT,
372 CONSTRAINT fk_pharmacy_sale_pharmacy
373 FOREIGN KEY (pharmacy_id) REFERENCES Pharmacy(pharmacy_id)
374 ON UPDATE CASCADE
375 ON DELETE RESTRICT,
376 CONSTRAINT fk_pharmacy_sale_prescription
377 FOREIGN KEY (presc_id) REFERENCES Prescription(presc_id)
378 ON UPDATE CASCADE
379 ON DELETE RESTRICT
380);
381
382CREATE TABLE Sale_item (
383 sale_item_id INT PRIMARY KEY,
384 sale_id INT NOT NULL,
385 presc_id INT NOT NULL,
386 inventory_id INT NOT NULL,
387 date DATE NOT NULL,
388 CONSTRAINT chk_sale_item_date
389 CHECK (date <= CURRENT_DATE),
390 CONSTRAINT fk_sale_item_sale
391 FOREIGN KEY (sale_id) REFERENCES Pharmacy_sale(sale_id)
392 ON UPDATE CASCADE
393 ON DELETE RESTRICT,
394 CONSTRAINT fk_sale_item_prescription
395 FOREIGN KEY (presc_id) REFERENCES Prescription(presc_id)
396 ON UPDATE CASCADE
397 ON DELETE RESTRICT,
398 CONSTRAINT fk_sale_item_inventory
399 FOREIGN KEY (inventory_id) REFERENCES Inventory(inventory_id)
400 ON UPDATE CASCADE
401 ON DELETE RESTRICT
402);