RelationalDesign: schema_dll_final.sql

File schema_dll_final.sql, 14.3 KB (added by 223162, 13 days ago)

DDL

Line 
1BEGIN;
2
3DROP SCHEMA IF EXISTS kbnteam CASCADE;
4
5CREATE SCHEMA kbnteam;
6
7SET search_path TO kbnteam;
8
9
10CREATE TABLE api_user (
11 user_id SERIAL PRIMARY KEY,
12 user_first_name VARCHAR(255) NOT NULL,
13 user_last_name VARCHAR(255) NOT NULL,
14 user_email VARCHAR(255) NOT NULL UNIQUE,
15 user_phone_no VARCHAR(20) UNIQUE,
16 user_role VARCHAR(20) NOT NULL,
17 CONSTRAINT uq_api_user_id_role
18 UNIQUE (user_id, user_role),
19 CONSTRAINT chk_api_user_email
20 CHECK (user_email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$'),
21 CONSTRAINT chk_api_user_phone
22 CHECK (user_phone_no IS NULL OR user_phone_no ~ '^\+?[0-9]{7,15}$'),
23 CONSTRAINT chk_api_user_role
24 CHECK (user_role IN ('api_admin', 'customer', 'driver'))
25);
26
27CREATE TABLE company (
28 company_id SERIAL PRIMARY KEY,
29 company_name VARCHAR(255) NOT NULL UNIQUE,
30 company_address VARCHAR(255) NOT NULL
31);
32
33CREATE TABLE restaurant (
34 rest_id SERIAL PRIMARY KEY,
35 rest_name VARCHAR(255) NOT NULL,
36 rest_email VARCHAR(255) NOT NULL UNIQUE,
37 rest_location VARCHAR(255) NOT NULL,
38 rest_phone VARCHAR(20) NOT NULL UNIQUE,
39 rest_website VARCHAR(255) UNIQUE,
40 CONSTRAINT chk_restaurant_email
41 CHECK (rest_email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$'),
42 CONSTRAINT chk_restaurant_phone
43 CHECK (rest_phone ~ '^\+?[0-9]{7,15}$')
44);
45
46CREATE TABLE category (
47 cat_id SERIAL PRIMARY KEY,
48 cat_name VARCHAR(255) NOT NULL UNIQUE,
49 cat_description VARCHAR(255)
50);
51
52CREATE TABLE contract_status (
53 contract_status_id SERIAL PRIMARY KEY,
54 contract_status_name VARCHAR(255) NOT NULL UNIQUE
55);
56
57CREATE TABLE customer_loyalty_status (
58 cus_loyalty_status_id SERIAL PRIMARY KEY,
59 cus_loyalty_status_name VARCHAR(255) NOT NULL UNIQUE
60);
61
62CREATE TABLE delivery_status (
63 d_status_id SERIAL PRIMARY KEY,
64 d_status_name VARCHAR(255) NOT NULL UNIQUE
65);
66
67CREATE TABLE order_status (
68 o_status_id SERIAL PRIMARY KEY,
69 o_status_name VARCHAR(255) NOT NULL UNIQUE
70);
71
72CREATE TABLE ingredient (
73 ingr_id SERIAL PRIMARY KEY,
74 ingr_name VARCHAR(255) NOT NULL UNIQUE
75);
76
77CREATE TABLE alergen (
78 alergen_id SERIAL PRIMARY KEY,
79 alergen_name VARCHAR(255) NOT NULL UNIQUE,
80 alergen_description VARCHAR(255)
81);
82
83CREATE TABLE loyalty_tier (
84 tier_id SERIAL PRIMARY KEY,
85 tier_name VARCHAR(255) NOT NULL UNIQUE,
86 tier_minimum_points INTEGER NOT NULL,
87 tier_maximum_points INTEGER NOT NULL,
88 tier_discount_percentage NUMERIC(5,2) NOT NULL,
89 tier_free_delivery_eligibility BOOLEAN NOT NULL DEFAULT FALSE,
90 tier_priority_support BOOLEAN NOT NULL DEFAULT FALSE,
91 tier_created_at DATE NOT NULL DEFAULT CURRENT_DATE,
92 tier_update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
93 CONSTRAINT chk_tier_points_nonnegative
94 CHECK (tier_minimum_points >= 0 AND tier_maximum_points >= 0),
95 CONSTRAINT chk_tier_points_range
96 CHECK (tier_maximum_points >= tier_minimum_points),
97 CONSTRAINT chk_tier_discount
98 CHECK (tier_discount_percentage >= 0 AND tier_discount_percentage <= 100)
99);
100
101CREATE TABLE review (
102 review_id SERIAL PRIMARY KEY,
103 review_comment VARCHAR(255),
104 review_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
105 review_rating INTEGER NOT NULL,
106 CONSTRAINT chk_review_rating
107 CHECK (review_rating BETWEEN 1 AND 5)
108);
109
110CREATE TABLE api_admin (
111 user_id INTEGER PRIMARY KEY,
112 user_role VARCHAR(20) NOT NULL DEFAULT 'api_admin',
113 rest_id INTEGER NOT NULL,
114 CONSTRAINT chk_api_admin_role
115 CHECK (user_role = 'api_admin'),
116 CONSTRAINT fk_admin_user
117 FOREIGN KEY (user_id, user_role)
118 REFERENCES api_user(user_id, user_role)
119 ON UPDATE CASCADE
120 ON DELETE CASCADE,
121 CONSTRAINT fk_admin_restaurant
122 FOREIGN KEY (rest_id)
123 REFERENCES restaurant(rest_id)
124 ON UPDATE CASCADE
125 ON DELETE RESTRICT
126);
127
128CREATE TABLE customer (
129 user_id INTEGER PRIMARY KEY,
130 user_role VARCHAR(20) NOT NULL DEFAULT 'customer',
131 company_id INTEGER NOT NULL,
132 CONSTRAINT chk_customer_role
133 CHECK (user_role = 'customer'),
134 CONSTRAINT fk_customer_user
135 FOREIGN KEY (user_id, user_role)
136 REFERENCES api_user(user_id, user_role)
137 ON UPDATE CASCADE
138 ON DELETE CASCADE,
139 CONSTRAINT fk_customer_company
140 FOREIGN KEY (company_id)
141 REFERENCES company(company_id)
142 ON UPDATE CASCADE
143 ON DELETE RESTRICT
144);
145
146CREATE TABLE driver (
147 user_id INTEGER PRIMARY KEY,
148 user_role VARCHAR(20) NOT NULL DEFAULT 'driver',
149 rest_id INTEGER NOT NULL,
150 CONSTRAINT chk_driver_role
151 CHECK (user_role = 'driver'),
152 CONSTRAINT fk_driver_user
153 FOREIGN KEY (user_id, user_role)
154 REFERENCES api_user(user_id, user_role)
155 ON UPDATE CASCADE
156 ON DELETE CASCADE,
157 CONSTRAINT fk_driver_restaurant
158 FOREIGN KEY (rest_id)
159 REFERENCES restaurant(rest_id)
160 ON UPDATE CASCADE
161 ON DELETE RESTRICT
162);
163
164CREATE TABLE contract (
165 contract_id SERIAL PRIMARY KEY,
166 company_id INTEGER NOT NULL,
167 contract_end_date DATE NOT NULL,
168 contract_start_date DATE NOT NULL,
169 contract_status_id INTEGER NOT NULL,
170 rest_id INTEGER NOT NULL,
171 CONSTRAINT fk_contract_company
172 FOREIGN KEY (company_id)
173 REFERENCES company(company_id)
174 ON UPDATE CASCADE
175 ON DELETE RESTRICT,
176 CONSTRAINT fk_contract_status
177 FOREIGN KEY (contract_status_id)
178 REFERENCES contract_status(contract_status_id)
179 ON UPDATE CASCADE
180 ON DELETE RESTRICT,
181 CONSTRAINT fk_contract_restaurant
182 FOREIGN KEY (rest_id)
183 REFERENCES restaurant(rest_id)
184 ON UPDATE CASCADE
185 ON DELETE RESTRICT,
186 CONSTRAINT chk_contract_dates
187 CHECK (contract_end_date >= contract_start_date)
188);
189
190CREATE TABLE delivery (
191 delivery_id SERIAL PRIMARY KEY,
192 delivery_date DATE NOT NULL DEFAULT CURRENT_DATE,
193 delivery_notes VARCHAR(255),
194 d_status_id INTEGER NOT NULL,
195 driver_user_id INTEGER,
196 CONSTRAINT fk_delivery_status
197 FOREIGN KEY (d_status_id)
198 REFERENCES delivery_status(d_status_id)
199 ON UPDATE CASCADE
200 ON DELETE RESTRICT,
201 CONSTRAINT fk_delivery_driver
202 FOREIGN KEY (driver_user_id)
203 REFERENCES driver(user_id)
204 ON UPDATE CASCADE
205 ON DELETE SET NULL
206);
207
208CREATE TABLE company_order (
209 comp_order_id SERIAL PRIMARY KEY,
210 company_id INTEGER NOT NULL,
211 delivery_id INTEGER UNIQUE,
212 CONSTRAINT fk_company_order_company
213 FOREIGN KEY (company_id)
214 REFERENCES company(company_id)
215 ON UPDATE CASCADE
216 ON DELETE RESTRICT,
217 CONSTRAINT fk_company_order_delivery
218 FOREIGN KEY (delivery_id)
219 REFERENCES delivery(delivery_id)
220 ON UPDATE CASCADE
221 ON DELETE SET NULL
222);
223
224CREATE TABLE meal (
225 meal_id SERIAL PRIMARY KEY,
226 cat_id INTEGER NOT NULL,
227 meal_description VARCHAR(255),
228 meal_name VARCHAR(255) NOT NULL,
229 meal_price NUMERIC(10,2) NOT NULL,
230 meal_weight INTEGER NOT NULL,
231 rest_id INTEGER NOT NULL,
232 CONSTRAINT fk_meal_category
233 FOREIGN KEY (cat_id)
234 REFERENCES category(cat_id)
235 ON UPDATE CASCADE
236 ON DELETE RESTRICT,
237 CONSTRAINT fk_meal_restaurant
238 FOREIGN KEY (rest_id)
239 REFERENCES restaurant(rest_id)
240 ON UPDATE CASCADE
241 ON DELETE RESTRICT,
242 CONSTRAINT chk_meal_price
243 CHECK (meal_price >= 0),
244 CONSTRAINT chk_meal_weight
245 CHECK (meal_weight > 0),
246 CONSTRAINT uq_meal_restaurant_name
247 UNIQUE (rest_id, meal_name)
248);
249
250CREATE TABLE drink (
251 drink_id SERIAL PRIMARY KEY,
252 drink_milliliters INTEGER NOT NULL,
253 drink_name VARCHAR(255) NOT NULL,
254 drink_price NUMERIC(10,2) NOT NULL,
255 rest_id INTEGER NOT NULL,
256 CONSTRAINT fk_drink_restaurant
257 FOREIGN KEY (rest_id)
258 REFERENCES restaurant(rest_id)
259 ON UPDATE CASCADE
260 ON DELETE RESTRICT,
261 CONSTRAINT chk_drink_milliliters
262 CHECK (drink_milliliters > 0),
263 CONSTRAINT chk_drink_price
264 CHECK (drink_price >= 0),
265 CONSTRAINT uq_drink_restaurant_name
266 UNIQUE (rest_id, drink_name)
267);
268
269CREATE TABLE customer_loyalty (
270 cus_loyalty_id SERIAL PRIMARY KEY,
271 cus_loyalty_curr_points INTEGER NOT NULL DEFAULT 0,
272 cus_loyalty_joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
273 cus_loyalty_status_id INTEGER NOT NULL,
274 user_id INTEGER NOT NULL UNIQUE,
275 tier_id INTEGER NOT NULL,
276 CONSTRAINT fk_customer_loyalty_status
277 FOREIGN KEY (cus_loyalty_status_id)
278 REFERENCES customer_loyalty_status(cus_loyalty_status_id)
279 ON UPDATE CASCADE
280 ON DELETE RESTRICT,
281 CONSTRAINT fk_customer_loyalty_user
282 FOREIGN KEY (user_id)
283 REFERENCES customer(user_id)
284 ON UPDATE CASCADE
285 ON DELETE CASCADE,
286 CONSTRAINT fk_customer_loyalty_tier
287 FOREIGN KEY (tier_id)
288 REFERENCES loyalty_tier(tier_id)
289 ON UPDATE CASCADE
290 ON DELETE RESTRICT,
291 CONSTRAINT chk_customer_loyalty_points
292 CHECK (cus_loyalty_curr_points >= 0)
293);
294
295CREATE TABLE customer_order (
296 order_id SERIAL PRIMARY KEY,
297 comp_order_id INTEGER NOT NULL,
298 customer_user_id INTEGER NOT NULL,
299 order_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
300 o_status_id INTEGER NOT NULL,
301 order_total NUMERIC(10,2) NOT NULL DEFAULT 0,
302 CONSTRAINT fk_customer_order_company_order
303 FOREIGN KEY (comp_order_id)
304 REFERENCES company_order(comp_order_id)
305 ON UPDATE CASCADE
306 ON DELETE RESTRICT,
307 CONSTRAINT fk_customer_order_customer
308 FOREIGN KEY (customer_user_id)
309 REFERENCES customer(user_id)
310 ON UPDATE CASCADE
311 ON DELETE RESTRICT,
312 CONSTRAINT fk_customer_order_status
313 FOREIGN KEY (o_status_id)
314 REFERENCES order_status(o_status_id)
315 ON UPDATE CASCADE
316 ON DELETE RESTRICT,
317 CONSTRAINT chk_order_total
318 CHECK (order_total >= 0)
319);
320
321CREATE TABLE invoice (
322 invoice_id SERIAL PRIMARY KEY,
323 comp_order_id INTEGER NOT NULL UNIQUE,
324 CONSTRAINT fk_invoice_company_order
325 FOREIGN KEY (comp_order_id)
326 REFERENCES company_order(comp_order_id)
327 ON UPDATE CASCADE
328 ON DELETE CASCADE
329);
330
331CREATE TABLE lunch_time (
332 lunch_time_id SERIAL PRIMARY KEY,
333 comp_order_id INTEGER,
334 contract_id INTEGER NOT NULL,
335 lunch_end TIMESTAMP NOT NULL,
336 lunch_preorder_offset INTEGER NOT NULL DEFAULT 0,
337 lunch_start TIMESTAMP NOT NULL,
338 lunch_weekday VARCHAR(15) NOT NULL,
339 CONSTRAINT fk_lunch_time_company_order
340 FOREIGN KEY (comp_order_id)
341 REFERENCES company_order(comp_order_id)
342 ON UPDATE CASCADE
343 ON DELETE SET NULL,
344 CONSTRAINT fk_lunch_time_contract
345 FOREIGN KEY (contract_id)
346 REFERENCES contract(contract_id)
347 ON UPDATE CASCADE
348 ON DELETE CASCADE,
349 CONSTRAINT chk_lunch_time_offset
350 CHECK (lunch_preorder_offset >= 0),
351 CONSTRAINT chk_lunch_interval
352 CHECK (lunch_end > lunch_start),
353 CONSTRAINT chk_lunch_weekday
354 CHECK (lunch_weekday IN ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'))
355);
356
357CREATE TABLE meal_ingredient (
358 ingr_id INTEGER NOT NULL,
359 meal_id INTEGER NOT NULL,
360 PRIMARY KEY (ingr_id, meal_id),
361 CONSTRAINT fk_meal_ingredient_ingredient
362 FOREIGN KEY (ingr_id)
363 REFERENCES ingredient(ingr_id)
364 ON UPDATE CASCADE
365 ON DELETE CASCADE,
366 CONSTRAINT fk_meal_ingredient_meal
367 FOREIGN KEY (meal_id)
368 REFERENCES meal(meal_id)
369 ON UPDATE CASCADE
370 ON DELETE CASCADE
371);
372
373CREATE TABLE alergen_ingredient (
374 alergen_id INTEGER NOT NULL,
375 ingr_id INTEGER NOT NULL,
376 PRIMARY KEY (alergen_id, ingr_id),
377 CONSTRAINT fk_alergen_ingredient_alergen
378 FOREIGN KEY (alergen_id)
379 REFERENCES alergen(alergen_id)
380 ON UPDATE CASCADE
381 ON DELETE CASCADE,
382 CONSTRAINT fk_alergen_ingredient_ingredient
383 FOREIGN KEY (ingr_id)
384 REFERENCES ingredient(ingr_id)
385 ON UPDATE CASCADE
386 ON DELETE CASCADE
387);
388
389CREATE TABLE order_meal (
390 meal_id INTEGER NOT NULL,
391 order_id INTEGER NOT NULL,
392 PRIMARY KEY (meal_id, order_id),
393 CONSTRAINT fk_order_meal_meal
394 FOREIGN KEY (meal_id)
395 REFERENCES meal(meal_id)
396 ON UPDATE CASCADE
397 ON DELETE RESTRICT,
398 CONSTRAINT fk_order_meal_order
399 FOREIGN KEY (order_id)
400 REFERENCES customer_order(order_id)
401 ON UPDATE CASCADE
402 ON DELETE CASCADE
403);
404
405CREATE TABLE order_drink (
406 drink_id INTEGER NOT NULL,
407 order_id INTEGER NOT NULL,
408 PRIMARY KEY (drink_id, order_id),
409 CONSTRAINT fk_order_drink_drink
410 FOREIGN KEY (drink_id)
411 REFERENCES drink(drink_id)
412 ON UPDATE CASCADE
413 ON DELETE RESTRICT,
414 CONSTRAINT fk_order_drink_order
415 FOREIGN KEY (order_id)
416 REFERENCES customer_order(order_id)
417 ON UPDATE CASCADE
418 ON DELETE CASCADE
419);
420
421CREATE TABLE delivery_review (
422 delivery_review_id SERIAL PRIMARY KEY,
423 del_review_courier_rating INTEGER NOT NULL,
424 del_review_speed_rating INTEGER NOT NULL,
425 delivery_id INTEGER NOT NULL UNIQUE,
426 review_id INTEGER NOT NULL UNIQUE,
427 CONSTRAINT fk_delivery_review_delivery
428 FOREIGN KEY (delivery_id)
429 REFERENCES delivery(delivery_id)
430 ON UPDATE CASCADE
431 ON DELETE CASCADE,
432 CONSTRAINT fk_delivery_review_review
433 FOREIGN KEY (review_id)
434 REFERENCES review(review_id)
435 ON UPDATE CASCADE
436 ON DELETE CASCADE,
437 CONSTRAINT chk_delivery_review_courier_rating
438 CHECK (del_review_courier_rating BETWEEN 1 AND 5),
439 CONSTRAINT chk_delivery_review_speed_rating
440 CHECK (del_review_speed_rating BETWEEN 1 AND 5)
441);
442
443CREATE TABLE order_review (
444 order_review_id SERIAL PRIMARY KEY,
445 order_id INTEGER NOT NULL UNIQUE,
446 order_review_food_rating INTEGER NOT NULL,
447 order_review_res_rating INTEGER NOT NULL,
448 review_id INTEGER NOT NULL UNIQUE,
449 CONSTRAINT fk_order_review_order
450 FOREIGN KEY (order_id)
451 REFERENCES customer_order(order_id)
452 ON UPDATE CASCADE
453 ON DELETE CASCADE,
454 CONSTRAINT fk_order_review_review
455 FOREIGN KEY (review_id)
456 REFERENCES review(review_id)
457 ON UPDATE CASCADE
458 ON DELETE CASCADE,
459 CONSTRAINT chk_order_review_food_rating
460 CHECK (order_review_food_rating BETWEEN 1 AND 5),
461 CONSTRAINT chk_order_review_res_rating
462 CHECK (order_review_res_rating BETWEEN 1 AND 5)
463);
464
465COMMIT;