F2: paws-care-ddl.sql

File paws-care-ddl.sql, 12.0 KB (added by 231140, 13 days ago)
Line 
1-- ==========================================
2-- 1. БРИШЕЊЕ НА ТАБЕЛИ (Редослед за чиста база)
3-- ==========================================
4DROP TABLE IF EXISTS Review CASCADE;
5DROP TABLE IF EXISTS Attends CASCADE;
6DROP TABLE IF EXISTS Pet_Food CASCADE;
7DROP TABLE IF EXISTS Medication CASCADE;
8DROP TABLE IF EXISTS Product CASCADE;
9DROP TABLE IF EXISTS Vaccination_Vaccine CASCADE;
10DROP TABLE IF EXISTS Vaccination CASCADE;
11DROP TABLE IF EXISTS Performed_Treatment CASCADE;
12DROP TABLE IF EXISTS Medical_Record CASCADE;
13DROP TABLE IF EXISTS Appointment CASCADE;
14DROP TABLE IF EXISTS Equipment CASCADE;
15DROP TABLE IF EXISTS Technician_Lab_Skills CASCADE;
16DROP TABLE IF EXISTS Veterinarian CASCADE;
17DROP TABLE IF EXISTS Veterinary_Technician CASCADE;
18DROP TABLE IF EXISTS Inventory_Manager CASCADE;
19DROP TABLE IF EXISTS Medical_Professional CASCADE;
20DROP TABLE IF EXISTS Receptionist_Languages CASCADE;
21DROP TABLE IF EXISTS Receptionist CASCADE;
22DROP TABLE IF EXISTS Employee CASCADE;
23DROP TABLE IF EXISTS Owner_Pet_Pair CASCADE;
24DROP TABLE IF EXISTS Owner CASCADE;
25DROP TABLE IF EXISTS Pet CASCADE;
26DROP TABLE IF EXISTS Exam_Room CASCADE;
27DROP TABLE IF EXISTS Treatment CASCADE;
28DROP TABLE IF EXISTS Vaccine CASCADE;
29DROP TABLE IF EXISTS Person CASCADE;
30
31-- ==========================================
32-- 2. КРЕИРАЊЕ НА ТАБЕЛИ (DDL)
33-- ==========================================
34
35CREATE TABLE Person(
36 person_id SERIAL PRIMARY KEY,
37 name VARCHAR(50) NOT NULL DEFAULT 'John',
38 surname VARCHAR(50) NOT NULL DEFAULT 'Doe',
39 phone_number VARCHAR(20) NOT NULL UNIQUE
40 CHECK (phone_number ~ '^\+?[0-9]+$'),
41 email VARCHAR(50) UNIQUE
42 CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
43 address VARCHAR(50)
44);
45
46
47CREATE TABLE Owner(
48 owner_id INT4 PRIMARY KEY REFERENCES
49 Person(person_id),
50 total_spent NUMERIC(10,2),
51 balance NUMERIC(10,2),
52 membership_type VARCHAR(10)
53 CHECK(membership_type IN ('VIP', 'Standard', 'Premium'))
54);
55
56CREATE TABLE Employee (
57 employee_id INT4 PRIMARY KEY REFERENCES
58 Person(person_id),
59 date_of_birth DATE NOT NULL,
60 hire_date DATE,
61
62 CONSTRAINT check_hire_after_birth
63 CHECK (hire_date > date_of_birth),
64 CONSTRAINT check_legal_age
65 CHECK (date_of_birth <= CURRENT_DATE - INTERVAL '18 years')
66);
67
68CREATE TABLE Vaccine (
69 vaccine_id INT4 PRIMARY KEY,
70 trade_name VARCHAR(100) NOT NULL,
71 target_species VARCHAR(30) NOT NULL,
72 disease_target VARCHAR(50) NOT NULL,
73 manufacturer VARCHAR(100) NOT NULL,
74 dosage_ml numeric(4, 2) DEFAULT 1.0,
75
76 CONSTRAINT check_positive_dosage CHECK (dosage_ml > 0),
77 CONSTRAINT unique_vaccine_info UNIQUE (trade_name, manufacturer)
78);
79
80CREATE TABLE Treatment (
81 treatment_id INT4 PRIMARY KEY,
82 name VARCHAR(50) NOT NULL,
83 price NUMERIC(10, 2) NOT NULL,
84
85 CONSTRAINT check_treatment_price CHECK (price >= 0),
86 CONSTRAINT unique_treatment_name UNIQUE (name)
87);
88
89CREATE TABLE Exam_Room (
90 exam_room_id INT4 PRIMARY KEY,
91 room_type VARCHAR(50) NOT NULL,
92 is_available BOOL NOT NULL DEFAULT true,
93 last_sanitized TIMESTAMP,
94
95 CONSTRAINT check_room_type
96 CHECK (room_type IN ('General', 'Surgery', 'Consultation', 'X-Ray', 'Isolation')),
97 CONSTRAINT check_sanitization_date
98 CHECK (last_sanitized <= CURRENT_TIMESTAMP)
99);
100
101CREATE TABLE Pet(
102 pet_id INT4 PRIMARY KEY,
103 name VARCHAR(50),
104 type_of_animal VARCHAR(30),
105 breed VARCHAR(50),
106 date_of_birth DATE,
107 sex CHAR(1),
108
109 CONSTRAINT check_pet_sex CHECK (sex IN ('M', 'F')),
110 CONSTRAINT CHK_pet_dob CHECK (date_of_birth <= CURRENT_DATE)
111);
112
113CREATE TABLE Owner_Pet_Pair(
114 pet_id INT4 REFERENCES Pet(pet_id)
115 ON DELETE CASCADE ON UPDATE CASCADE,
116 owner_id INT4 REFERENCES Owner(owner_id)
117 ON DELETE CASCADE ON UPDATE CASCADE,
118 is_primary_owner BOOLEAN DEFAULT FALSE,
119 PRIMARY KEY (pet_id, owner_id)
120);
121
122CREATE TABLE Receptionist(
123 receptionist_id INT4 PRIMARY KEY,
124 desk_number varchar(10),
125 FOREIGN KEY (receptionist_id)
126 REFERENCES Employee(employee_id)
127 ON DELETE CASCADE
128 ON UPDATE CASCADE,
129
130 CONSTRAINT check_desk_not_empty CHECK (length(trim(desk_number)) > 0)
131);
132
133CREATE TABLE Receptionist_Languages (
134 language_name VARCHAR(50) NOT NULL,
135 proficiency_level varchar(20) DEFAULT 'Fluent',
136 receptionist_id INT4 NOT NULL REFERENCES Receptionist(receptionist_id)
137 ON DELETE CASCADE ON UPDATE CASCADE ,
138
139 PRIMARY KEY (receptionist_id, language_name),
140
141 CONSTRAINT CHK_proficiency
142 CHECK (proficiency_level IN ('Basic', 'Intermediate', 'Fluent', 'Native'))
143);
144
145CREATE TABLE Medical_Professional (
146 medical_prof_id INT4 PRIMARY KEY
147 REFERENCES Employee(employee_id)
148 ON DELETE CASCADE
149 ON UPDATE CASCADE,
150 medical_degree VARCHAR(100) NOT NULL,
151 shift_type VARCHAR(20),
152 department VARCHAR(50) NOT NULL,
153 is_surgical_authorized BOOL NOT NULL,
154
155 CONSTRAINT check_shift_type
156 CHECK (shift_type IN ('Morning', 'Afternoon', 'Night', 'On-Call', 'Full-Day')),
157 CONSTRAINT check_department
158 CHECK (department IN ('Surgery', 'General', 'Diagnostics', 'Emergency', 'Pharmacy')),
159 CONSTRAINT check_degree_format
160 CHECK (length(medical_degree) >= 2)
161);
162
163CREATE TABLE Inventory_Manager(
164 inventory_manager_id INT4 PRIMARY KEY
165 REFERENCES Employee(employee_id)
166 ON DELETE CASCADE
167 ON UPDATE CASCADE,
168 access_level VARCHAR(20) NOT NULL,
169
170 CONSTRAINT check_access_level
171 CHECK (access_level IN ('Viewer', 'Editor', 'Admin', 'SuperUser'))
172);
173
174CREATE TABLE Veterinary_Technician(
175 veterinary_technician_id INT4 PRIMARY KEY
176 REFERENCES Medical_Professional(medical_prof_id)
177 ON DELETE CASCADE
178 ON UPDATE CASCADE,
179 certification_level varchar(50) NOT NULL,
180
181 CONSTRAINT CHK_certification_level
182 CHECK (certification_level IN ('Licensed (LVT)', 'Registered (RVT)', 'Certified (CVT)', 'Assistant', 'Intern'))
183);
184
185CREATE TABLE Technician_Lab_Skills (
186 vet_tech_id INT4 NOT NULL
187 REFERENCES Veterinary_Technician(veterinary_technician_id)
188 ON DELETE CASCADE ON UPDATE CASCADE,
189 skill_name VARCHAR(100) NOT NULL,
190 PRIMARY KEY (vet_tech_id, skill_name)
191);
192
193CREATE TABLE Veterinarian(
194 veterinarian_id INT4 PRIMARY KEY
195 REFERENCES Medical_Professional(medical_prof_id)
196 ON DELETE CASCADE
197 ON UPDATE CASCADE,
198 specialty VARCHAR(50) NOT NULL,
199 license_number VARCHAR(50) NOT NULL UNIQUE
200);
201
202CREATE TABLE Appointment(
203 appointment_id INT4 PRIMARY KEY,
204 appointment_time TIMESTAMP NOT NULL,
205 status VARCHAR(20) DEFAULT 'Scheduled'
206 CHECK (status IN ('Pending', 'Scheduled', 'Completed', 'Cancelled', 'No-show')),
207 reason VARCHAR(200),
208 pet_id INT4 DEFAULT 1 NOT NULL REFERENCES Pet(pet_id)
209 ON DELETE SET DEFAULT ON UPDATE CASCADE,
210 exam_room_id INT4 REFERENCES Exam_Room(exam_room_id)
211 ON DELETE SET NULL ON UPDATE CASCADE,
212 receptionist_id INT4 REFERENCES Receptionist(receptionist_id)
213 ON DELETE SET NULL ON UPDATE CASCADE,
214
215 CONSTRAINT check_future_date
216 CHECK (appointment_time >= CURRENT_TIMESTAMP - INTERVAL '2 hours')
217);
218
219CREATE TABLE Vaccination (
220 vaccination_id INT4 PRIMARY KEY REFERENCES
221 Performed_Treatment(performed_treatment_id),
222 date DATE NOT NULL DEFAULT CURRENT_DATE,
223 next_due_date DATE,
224
225 CONSTRAINT check_vaccine_dates CHECK (next_due_date > date)
226);
227
228CREATE TABLE Vaccination_Vaccine(
229 dosage_given numeric(4,2),
230 vaccine_id INT4 NOT NULL REFERENCES Vaccine(vaccine_id)
231 ON DELETE RESTRICT ON UPDATE CASCADE,
232 vaccination_id INT4 NOT NULL REFERENCES Vaccination(vaccination_id)
233 ON DELETE RESTRICT ON UPDATE CASCADE,
234 PRIMARY KEY (vaccine_id, vaccination_id)
235);
236
237CREATE TABLE Equipment (
238 equipment_id INT4 PRIMARY KEY,
239 name VARCHAR(100) NOT NULL,
240 model VARCHAR(50),
241 status VARCHAR(20) NOT NULL DEFAULT 'Available'
242 CHECK (status IN ('Available', 'In Use', 'Broken', 'Maintenance')),
243 exam_room_id INT4 REFERENCES Exam_Room(exam_room_id)
244 ON DELETE SET NULL ON UPDATE CASCADE
245);
246
247CREATE TABLE Medical_Record (
248 medical_record_id INT4 PRIMARY KEY,
249 date DATE NOT NULL,
250 diagnosis TEXT NOT NULL,
251 therapy TEXT NOT NULL,
252 note TEXT,
253 app_id INT4 REFERENCES Appointment(appointment_id)
254 ON DELETE RESTRICT ON UPDATE CASCADE,
255 pet_id INT4 NOT NULL REFERENCES Pet(pet_id)
256 ON DELETE RESTRICT ON UPDATE CASCADE,
257 medical_professional_id INT4 NOT NULL REFERENCES Medical_Professional(medical_prof_id)
258 ON DELETE RESTRICT ON UPDATE CASCADE,
259
260 CONSTRAINT CHK_medical_record_date
261 CHECK (date <= CURRENT_DATE)
262);
263
264CREATE TABLE Performed_Treatment (
265 performed_treatment_id INT4 PRIMARY KEY,
266 start_time TIMESTAMP NOT NULL,
267 end_time TIMESTAMP,
268 medical_record_id INT4 NOT NULL REFERENCES Medical_Record(medical_record_id)
269 ON DELETE CASCADE ON UPDATE CASCADE,
270 treatment_id INT4 NOT NULL REFERENCES Treatment(treatment_id)
271 ON DELETE RESTRICT ON UPDATE CASCADE,
272 medical_professional_id INT4 NOT NULL REFERENCES Medical_Professional(medical_prof_id)
273 ON DELETE RESTRICT ON UPDATE CASCADE,
274 exam_room_id INT4 NOT NULL REFERENCES Exam_Room(exam_room_id)
275 ON DELETE RESTRICT ON UPDATE CASCADE,
276
277 CONSTRAINT check_treatment_duration
278 CHECK (end_time IS NULL OR end_time > start_time),
279 CONSTRAINT check_start_time_limit
280 CHECK (start_time <= CURRENT_TIMESTAMP + INTERVAL '7 days')
281);
282
283CREATE TABLE Product (
284 product_id INT4 PRIMARY KEY,
285 name VARCHAR(100) NOT NULL,
286 manufacturer VARCHAR(100),
287 stock_quantity INT4 DEFAULT 0,
288 unit_price NUMERIC(10, 2) NOT NULL,
289 description TEXT,
290 is_prescription_required BOOL DEFAULT false,
291 reorder_level INT NOT NULL,
292 inventory_manager_id INT4 NOT NULL REFERENCES Inventory_Manager(inventory_manager_id)
293 ON DELETE RESTRICT ON UPDATE CASCADE,
294
295 CONSTRAINT check_stock_quantity CHECK (stock_quantity >= 0),
296
297 CONSTRAINT check_unit_price CHECK (unit_price > 0),
298
299 CONSTRAINT check_reorder_level CHECK (reorder_level >= 0),
300
301 CONSTRAINT unique_product_brand UNIQUE (name, manufacturer)
302);
303
304CREATE TABLE Medication (
305 medication_id INT4 PRIMARY KEY REFERENCES Product(product_id)
306 ON DELETE CASCADE
307 ON UPDATE CASCADE,
308 dosage_form VARCHAR(50) NOT NULL,
309 side_effects TEXT NOT NULL,
310
311 CONSTRAINT check_dosage_form
312 CHECK (dosage_form IN ('Tablet', 'Capsule', 'Injection', 'Syrup', 'Ointment', 'Cream', 'Drops', 'Powder'))
313);
314
315CREATE TABLE Pet_Food (
316 product_id INT4 PRIMARY KEY REFERENCES Product(product_id)
317 ON DELETE CASCADE
318 ON UPDATE CASCADE,
319 calories INT4,
320 is_medical_diet BOOL NOT NULL DEFAULT false,
321 flavor VARCHAR(50),
322 animal_species VARCHAR(30) NOT NULL,
323 life_stage VARCHAR(20) NOT NULL,
324
325 CONSTRAINT check_food_calories
326 CHECK (calories > 0),
327
328 CONSTRAINT check_food_life_stage
329 CHECK (life_stage IN ('Puppy/Kitten', 'Junior', 'Adult', 'Senior', 'All Stages'))
330);
331
332CREATE TABLE Attends (
333 medical_prof_id INT4 REFERENCES Medical_Professional(medical_prof_id)
334 ON DELETE CASCADE ON UPDATE CASCADE,
335 app_id INT4 REFERENCES Appointment(appointment_id)
336 ON DELETE CASCADE ON UPDATE CASCADE,
337 PRIMARY KEY (medical_prof_id, app_id)
338);
339
340CREATE TABLE Review (
341 review_id INT4 PRIMARY KEY,
342 rating INT4 NOT NULL CHECK (rating BETWEEN 1 AND 5),
343 comment TEXT,
344 owner_id INT4 REFERENCES Owner(owner_id)
345 ON DELETE SET NULL ON UPDATE CASCADE
346);