Логички и физички дизајн - Креирање база податоци (со 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 скрипта за креирање на табелите

Скрипта за креирање на шема

ddl.sql​