Логички и физички дизајн - Креирање база податоци (со SQL DDL)

Фаза 2 - DDL, податоци, погледи

DDL скрипта за креирање на табелите

Финалната DDL скрипта за креирање на шемата:

• schema_final.sql

schema_dll_final.sql​

Број на податоци по табела

Табела	Број на записи
API_USER	10,001,000
CUSTOMER	10,000,000
CUSTOMER_LOYALTY	10,000,000
CUSTOMER_ORDER	10,000,000
REVIEW	1,000,000
ORDER_MEAL	1,000,000
ORDER_DRINK	1,000,000
API_ADMIN	100
DRIVER	900
COMPANY	500
COMPANY_ORDER	1,000,000
RESTAURANT	до 50
CATEGORY	реални MealDB категории
INGREDIENT	реален MealDB/CocktailDB сет на состојки
ALERGEN	14
MEAL	200
DRINK	100
MEAL_INGREDIENT	зависно од увезените meal-ingredient врски
ALERGEN_INGREDIENT	зависно од совпаѓањето според имињата на состојките
CONTRACT	200
DELIVERY	1,000,000
LUNCH_TIME	1,000
INVOICE	6,000
ORDER_REVIEW	700,000
DELIVERY_REVIEW	300,000
CONTRACT_STATUS	5
CUSTOMER_LOYALTY_STATUS	3
DELIVERY_STATUS	8
ORDER_STATUS	6

Извор на податоците

• *RESTAURANT* – до 50 ресторани од реалниот dataset
• *CATEGORY* – реални категории од MealDB
• *INGREDIENT* – реален сет на состојки од MealDB/CocktailDB
• *MEAL_INGREDIENT* – врски добиени од увезените податоци за оброците и состојките
• *ALERGEN_INGREDIENT* – врски добиени преку совпаѓање на имињата на состојките
• Останатите податоци се генерирани генерички со реалистична распределба според очекуваното користење на апликацијата.

Скрипти за полнење на табелите

• data_final.sql

Простор за поставување на data_final.sql

2В: Погледи (Views)

Потребно е да се направат три погледи (views) по член, така што тие подоцна реално ќе бидат користени од апликацијата.

Views

View 1

_Мени со јадења_

[source,sql]

---

CREATE OR REPLACE VIEW kbnteam.v_menu_meal AS WITH meal_ingredients AS (

SELECT

x.meal_id, string_agg(x.ingr_name, ', ' ORDER BY x.ingr_name) AS ingredients

FROM (

SELECT DISTINCT

mi.meal_id, i.ingr_name

FROM kbnteam.meal_ingredient mi JOIN kbnteam.ingredient i ON i.ingr_id = mi.ingr_id

) x GROUP BY x.meal_id

), meal_allergens AS (

SELECT

x.meal_id, string_agg(x.alergen_name, ', ' ORDER BY x.alergen_name) AS allergens

FROM (

SELECT DISTINCT

mi.meal_id, a.alergen_name

FROM kbnteam.meal_ingredient mi JOIN kbnteam.alergen_ingredient ai ON ai.ingr_id = mi.ingr_id JOIN kbnteam.alergen a ON a.alergen_id = ai.alergen_id

) x GROUP BY x.meal_id

) SELECT

m.meal_id, m.meal_name, m.meal_description, m.meal_price, m.meal_weight, c.cat_id, c.cat_name, r.rest_id, r.rest_name, COALESCE(mi.ingredients, ) AS ingredients, COALESCE(ma.allergens, ) AS allergens

FROM kbnteam.meal m JOIN kbnteam.category c ON c.cat_id = m.cat_id JOIN kbnteam.restaurant r ON r.rest_id = m.rest_id LEFT JOIN meal_ingredients mi ON mi.meal_id = m.meal_id LEFT JOIN meal_allergens ma ON ma.meal_id = m.meal_id;

---

View 2

_Целосен преглед на нарачките_

[source,sql]

---

CREATE OR REPLACE VIEW kbnteam.v_orders_full AS WITH order_meals AS (

SELECT

x.order_id, string_agg(x.meal_name, ', ' ORDER BY x.meal_name) AS meals

FROM (

SELECT DISTINCT

om.order_id, m.meal_name

FROM kbnteam.order_meal om JOIN kbnteam.meal m ON m.meal_id = om.meal_id

) x GROUP BY x.order_id

), order_drinks AS (

SELECT

x.order_id, string_agg(x.drink_name, ', ' ORDER BY x.drink_name) AS drinks

FROM (

SELECT DISTINCT

od.order_id, d.drink_name

FROM kbnteam.order_drink od JOIN kbnteam.drink d ON d.drink_id = od.drink_id

) x GROUP BY x.order_id

) SELECT

o.order_id, o.order_datetime, o.order_total, os.o_status_name, cu.user_id AS customer_user_id, cu.company_id AS customer_company_id, au.user_first_name AS customer_first_name, au.user_last_name AS customer_last_name, au.user_email AS customer_email, co.comp_order_id, co.company_id AS order_company_id, cmp.company_name, d.delivery_id, d.delivery_date, ds.d_status_name, drv.user_id AS driver_user_id, du.user_first_name AS driver_first_name, du.user_last_name AS driver_last_name, du.user_phone_no AS driver_phone, COALESCE(om.meals, ) AS meals, COALESCE(od.drinks, ) AS drinks

FROM kbnteam.customer_order o JOIN kbnteam.order_status os ON os.o_status_id = o.o_status_id JOIN kbnteam.customer cu ON cu.user_id = o.customer_user_id JOIN kbnteam.api_user au ON au.user_id = cu.user_id JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id JOIN kbnteam.company cmp ON cmp.company_id = co.company_id LEFT JOIN kbnteam.delivery d ON d.delivery_id = co.delivery_id LEFT JOIN kbnteam.delivery_status ds ON ds.d_status_id = d.d_status_id LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id LEFT JOIN order_meals om ON om.order_id = o.order_id LEFT JOIN order_drinks od ON od.order_id = o.order_id;

---

View 3

_Преглед на рецензиите_

[source,sql]

---

CREATE OR REPLACE VIEW kbnteam.v_reviews_full AS SELECT

r.review_id, 'order'::text AS review_type, r.review_created_at, r.review_rating, r.review_comment, orv.order_id, NULL::integer AS delivery_id, co.company_id, cmp.company_name, au.user_id AS customer_user_id, au.user_first_name AS customer_first_name, au.user_last_name AS customer_last_name, NULL::integer AS driver_user_id, NULL::varchar(255) AS driver_first_name, NULL::varchar(255) AS driver_last_name, orv.order_review_food_rating, orv.order_review_res_rating, NULL::integer AS del_review_courier_rating, NULL::integer AS del_review_speed_rating

FROM kbnteam.review r JOIN kbnteam.order_review orv ON orv.review_id = r.review_id JOIN kbnteam.customer_order o ON o.order_id = orv.order_id JOIN kbnteam.customer cu ON cu.user_id = o.customer_user_id JOIN kbnteam.api_user au ON au.user_id = cu.user_id JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id JOIN kbnteam.company cmp ON cmp.company_id = co.company_id

UNION ALL

SELECT

r.review_id, 'delivery'::text AS review_type, r.review_created_at, r.review_rating, r.review_comment, NULL::integer AS order_id, drvrev.delivery_id, co.company_id, cmp.company_name, NULL::integer AS customer_user_id, NULL::varchar(255) AS customer_first_name, NULL::varchar(255) AS customer_last_name, du.user_id AS driver_user_id, du.user_first_name AS driver_first_name, du.user_last_name AS driver_last_name, NULL::integer AS order_review_food_rating, NULL::integer AS order_review_res_rating, drvrev.del_review_courier_rating, drvrev.del_review_speed_rating

FROM kbnteam.review r JOIN kbnteam.delivery_review drvrev ON drvrev.review_id = r.review_id JOIN kbnteam.delivery d ON d.delivery_id = drvrev.delivery_id LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id LEFT JOIN kbnteam.company_order co ON co.delivery_id = d.delivery_id LEFT JOIN kbnteam.company cmp ON cmp.company_id = co.company_id;

---

View 4

_Мени со пијалоци_

[source,sql]

---

CREATE OR REPLACE VIEW kbnteam.v_menu_drink AS SELECT

d.drink_id, d.drink_name, d.drink_milliliters, d.drink_price, r.rest_id, r.rest_name

FROM kbnteam.drink d JOIN kbnteam.restaurant r ON r.rest_id = d.rest_id;

---

View 5

_Преглед на фактурирањето на компаниите_

[source,sql]

---

CREATE OR REPLACE VIEW kbnteam.v_company_billing_overview AS WITH company_contracts AS (

SELECT

ct.company_id, COUNT(*) AS contract_count

FROM kbnteam.contract ct GROUP BY ct.company_id

), invoice_totals AS (

SELECT

i.invoice_id, i.comp_order_id, co.company_id, co.delivery_id, COUNT(o.order_id) AS customer_order_count, COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS invoice_amount

FROM kbnteam.invoice i JOIN kbnteam.company_order co ON co.comp_order_id = i.comp_order_id LEFT JOIN kbnteam.customer_order o ON o.comp_order_id = co.comp_order_id GROUP BY i.invoice_id, i.comp_order_id, co.company_id, co.delivery_id

) SELECT

it.invoice_id, it.comp_order_id, cmp.company_id, cmp.company_name, cmp.company_address, it.delivery_id, it.customer_order_count, it.invoice_amount, COALESCE(cc.contract_count, 0) AS contract_count

FROM invoice_totals it JOIN kbnteam.company cmp ON cmp.company_id = it.company_id LEFT JOIN company_contracts cc ON cc.company_id = cmp.company_id;

---

View 6

_Приходи според договорите_

[source,sql]

---

CREATE OR REPLACE VIEW kbnteam.v_contracts_revenue AS WITH order_restaurants AS (

SELECT DISTINCT

om.order_id, m.rest_id

FROM kbnteam.order_meal om JOIN kbnteam.meal m ON m.meal_id = om.meal_id

UNION

SELECT DISTINCT

od.order_id, d.rest_id

FROM kbnteam.order_drink od JOIN kbnteam.drink d ON d.drink_id = od.drink_id

), contract_metrics AS (

SELECT

co.company_id, orr.rest_id, COUNT(DISTINCT co.comp_order_id) AS company_order_count, COUNT(DISTINCT o.order_id) AS customer_order_count, COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS total_revenue

FROM kbnteam.customer_order o JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id JOIN order_restaurants orr ON orr.order_id = o.order_id GROUP BY co.company_id, orr.rest_id

) SELECT

ct.contract_id, cmp.company_id, cmp.company_name, r.rest_id, r.rest_name, cs.contract_status_name, ct.contract_start_date, ct.contract_end_date, COALESCE(cm.company_order_count, 0) AS company_order_count, COALESCE(cm.customer_order_count, 0) AS customer_order_count, COALESCE(cm.total_revenue, 0)::numeric(14,2) AS total_revenue

FROM kbnteam.contract ct JOIN kbnteam.company cmp ON cmp.company_id = ct.company_id JOIN kbnteam.restaurant r ON r.rest_id = ct.rest_id JOIN kbnteam.contract_status cs ON cs.contract_status_id = ct.contract_status_id LEFT JOIN contract_metrics cm

ON cm.company_id = ct.company_id

AND cm.rest_id = ct.rest_id;

---

View 7

_Преглед на лојалноста на клиентите_

[source,sql]

---

CREATE OR REPLACE VIEW kbnteam.v_customer_loyalty_full_v2 AS WITH order_stats AS (

SELECT

o.customer_user_id, COUNT(*) AS order_count, COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS total_spent, MAX(o.order_datetime) AS last_order_at

FROM kbnteam.customer_order o GROUP BY o.customer_user_id

) SELECT

cl.cus_loyalty_id, cl.user_id AS customer_user_id, cu.company_id, cmp.company_name, au.user_first_name, au.user_last_name, au.user_email, au.user_phone_no, cl.cus_loyalty_curr_points, cl.cus_loyalty_joined_at, cls.cus_loyalty_status_id, cls.cus_loyalty_status_name, lt.tier_id, lt.tier_name, lt.tier_discount_percentage, lt.tier_free_delivery_eligibility, lt.tier_priority_support, COALESCE(os.order_count, 0) AS order_count, COALESCE(os.total_spent, 0)::numeric(14,2) AS total_spent, os.last_order_at

FROM kbnteam.customer_loyalty cl JOIN kbnteam.customer cu

ON cu.user_id = cl.user_id

JOIN kbnteam.company cmp

ON cmp.company_id = cu.company_id

JOIN kbnteam.api_user au

ON au.user_id = cl.user_id

JOIN kbnteam.customer_loyalty_status cls

ON cls.cus_loyalty_status_id = cl.cus_loyalty_status_id

JOIN kbnteam.loyalty_tier lt

ON lt.tier_id = cl.tier_id

LEFT JOIN order_stats os

ON os.customer_user_id = cl.user_id;

---

View 8

_Преглед на доставите на доставувачите_

[source,sql]

---

CREATE OR REPLACE VIEW kbnteam.v_driver_deliveries AS WITH order_meals AS (

SELECT

x.order_id, string_agg(x.meal_name, ', ' ORDER BY x.meal_name) AS meals

FROM (

SELECT DISTINCT

om.order_id, m.meal_name

FROM kbnteam.order_meal om JOIN kbnteam.meal m ON m.meal_id = om.meal_id

) x GROUP BY x.order_id

), order_drinks AS (

SELECT

x.order_id, string_agg(x.drink_name, ', ' ORDER BY x.drink_name) AS drinks

FROM (

SELECT DISTINCT

od.order_id, d.drink_name

FROM kbnteam.order_drink od JOIN kbnteam.drink d ON d.drink_id = od.drink_id

) x GROUP BY x.order_id

) SELECT

d.delivery_id, d.delivery_date, d.delivery_notes, ds.d_status_name, drv.user_id AS driver_user_id, du.user_first_name AS driver_first_name, du.user_last_name AS driver_last_name, du.user_phone_no AS driver_phone, r.rest_id, r.rest_name, co.comp_order_id, cmp.company_id, cmp.company_name, o.order_id, o.order_datetime, o.order_total, cu.user_id AS customer_user_id, au.user_first_name AS customer_first_name, au.user_last_name AS customer_last_name, au.user_email AS customer_email, COALESCE(om.meals, ) AS meals, COALESCE(od.drinks, ) AS drinks

FROM kbnteam.delivery d JOIN kbnteam.delivery_status ds ON ds.d_status_id = d.d_status_id LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id LEFT JOIN kbnteam.restaurant r ON r.rest_id = drv.rest_id LEFT JOIN kbnteam.company_order co ON co.delivery_id = d.delivery_id LEFT JOIN kbnteam.company cmp ON cmp.company_id = co.company_id LEFT JOIN kbnteam.customer_order o ON o.comp_order_id = co.comp_order_id LEFT JOIN kbnteam.customer cu ON cu.user_id = o.customer_user_id LEFT JOIN kbnteam.api_user au ON au.user_id = cu.user_id LEFT JOIN order_meals om ON om.order_id = o.order_id LEFT JOIN order_drinks od ON od.order_id = o.order_id;

---

View 9

_Термини за ручек на доставувачите_

[source,sql]

---

CREATE OR REPLACE VIEW kbnteam.v_driver_lunch_timers AS SELECT

lt.lunch_time_id, lt.lunch_weekday, lt.lunch_start, lt.lunch_end, lt.lunch_preorder_offset, lt.comp_order_id, ct.contract_id, cmp.company_id, cmp.company_name, r.rest_id, r.rest_name, drv.user_id AS driver_user_id, au.user_first_name AS driver_first_name, au.user_last_name AS driver_last_name, au.user_phone_no AS driver_phone

FROM kbnteam.lunch_time lt JOIN kbnteam.contract ct ON ct.contract_id = lt.contract_id JOIN kbnteam.company cmp ON cmp.company_id = ct.company_id JOIN kbnteam.restaurant r ON r.rest_id = ct.rest_id JOIN kbnteam.driver drv ON drv.rest_id = r.rest_id JOIN kbnteam.api_user au ON au.user_id = drv.user_id;

---