RelationalDesign: schema8.0_dll.sql

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