= Логички и физички дизајн - Креирање база податоци (со SQL DDL)
== Ознаки
* Со * се означени надворешни клучеви
* Со !** се означени втори надворешни клучеви
* Со !*** се означени трети надворешни клучеви
* Примарните клучеви се подвлечени и болдирани

== Табели
=== Ентитети

ADMIN(admin_id, admin_name, admin_email, admin_password)

API_USER(api_user_id, username, password, role)

COMPANY(company_id, company_name, company_email, company_phone, company_address)

CONTRACT_STATUS(contract_status_id, status_name)

ORDER_STATUS(order_status_id, status_name)

DELIVERY_STATUS(delivery_status_id, status_name)

LOYALTY_TIER(loyalty_tier_id, tier_name, min_points, discount_percent)

CUSTOMER_LOYALTY_STATUS(customer_loyalty_status_id, status_name)

INGREDIENT(ingredient_id, ingredient_name)

ALERGEN(alergen_id, alergen_name)

CATEGORY(category_id, category_name)

LUNCH_TIME(lunch_time_id, lunch_name, start_time, end_time)

REVIEW(review_id, customer_id*, review_text, rating)
* референцира до CUSTOMER (customer_id)

RESTAURANT(rest_id, rest_name, rest_location, rest_website, rest_phone, rest_email, company_id*)
* референцира до COMPANY (company_id)

CUSTOMER(customer_id, first_name, last_name, email, phone, address, password)

DRIVER(driver_id, first_name, last_name, phone, vehicle_info, company_id*)
* референцира до COMPANY (company_id)

CONTRACT(contract_id, company_id*, contract_status_id**, contract_start_date, contract_end_date, contract_value)
* референцира до COMPANY (company_id)
** референцира до CONTRACT_STATUS (contract_status_id)

CUSTOMER_LOYALTY(customer_loyalty_id, points, customer_id*, loyalty_tier_id**, customer_loyalty_status_id***)
* референцира до CUSTOMER (customer_id)
** референцира до LOYALTY_TIER (loyalty_tier_id)
*** референцира до CUSTOMER_LOYALTY_STATUS (customer_loyalty_status_id)

MEAL(meal_id, meal_name, meal_price, meal_description, rest_id*, category_id**, lunch_time_id***)
* референцира до RESTAURANT (rest_id)
** референцира до CATEGORY (category_id)
*** референцира до LUNCH_TIME (lunch_time_id)

DRINK(drink_id, drink_name, drink_price, rest_id*)
* референцира до RESTAURANT (rest_id)

CUSTOMER_ORDER(order_id, order_date, total_amount, customer_id*, rest_id**, order_status_id***)
* референцира до CUSTOMER (customer_id)
** референцира до RESTAURANT (rest_id)
*** референцира до ORDER_STATUS (order_status_id)

DELIVERY(delivery_id, delivered_at, order_id*, driver_id**, delivery_status_id***)
* референцира до CUSTOMER_ORDER (order_id)
** референцира до DRIVER (driver_id)
*** референцира до DELIVERY_STATUS (delivery_status_id)

ORDER_REVIEW(order_review_id, order_id*, review_id**)
* референцира до CUSTOMER_ORDER (order_id)
** референцира до REVIEW (review_id)

DELIVERY_REVIEW(delivery_review_id, delivery_id*, review_id**)
* референцира до DELIVERY (delivery_id)
** референцира до REVIEW (review_id)

INVOICE(invoice_id, issued_at, amount, order_id*)
* референцира до CUSTOMER_ORDER (order_id)

COMPANY_ORDER(company_order_id, company_id*, order_id**)
* референцира до COMPANY (company_id)
** референцира до CUSTOMER_ORDER (order_id)

Релациски табели

MEAL_INGREDIENT(meal_id*, ingredient_id**)
* референцира до MEAL (meal_id)
** референцира до INGREDIENT (ingredient_id)

ALERGEN_INGREDIENT(alergen_id*, ingredient_id**)
* референцира до ALERGEN (alergen_id)
** референцира до INGREDIENT (ingredient_id)

ORDER_MEAL(order_id*, meal_id**, quantity)
* референцира до CUSTOMER_ORDER (order_id)
** референцира до MEAL (meal_id)

ORDER_DRINK(order_id*, drink_id**, quantity)
* референцира до CUSTOMER_ORDER (order_id)
** референцира до DRINK (drink_id)

== DDL скрипта за креирање на табелите
=== Скрипта за креирање на шема
[attachment:ddl.sql]