ЕР Дијаграм

Податочни побарувања

Ентитети

ADMIN

• user_id int, примарен клуч DEFAULT 1
• CONSTRAINT fk_admin_user

FOREIGN KEY (user_id) REFERENCES api_user(user_id) ON UPDATE CASCADE ON DELETE SET DEFAULT

ALLERGEN

• allergen_id serial, примарен клуч
• allergen_name string, задолжителен и уникатен
• allergen_description string

API_USER

• user_id serial, примарен клуч
• user_first_name string, задолжителен
• user_last_name string, задолжителен
• user_email string, задолжителен и уникатен
• user_phone_no string, уникатен
• user_password_hash TEXT, задолжителен

CATEGORY

• cat_id serial, примарен клуч
• cat_name string, задолжителен и уникатен
• cat_description string

COMPANY

• company_id serial, примарен клуч
• company_name string, задолжителен и уникатен
• company_address string, задолжителен

COMPANY_ORDER

• comp_order_id serial, примарен клуч
• company_id int, задолжителен
• delivery_id int, уникатен
• CONSTRAINT fk_company_order_company

FOREIGN KEY (company_id) REFERENCES company(company_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT fk_company_order_delivery

FOREIGN KEY (delivery_id) REFERENCES delivery(delivery_id) ON UPDATE CASCADE ON DELETE SET NULL

CONTRACT

• contract_id serial, примарен клуч
• company_id int, задолжителен
• rest_id int, задолжителен
• contract_status_id int, задолжителен
• contract_start_date date, задолжителен
• contract_end_date date, задолжителен
• CONSTRAINT fk_contract_company

FOREIGN KEY (company_id) REFERENCES company(company_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT fk_contract_restaurant

FOREIGN KEY (rest_id) REFERENCES restaurant(rest_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT fk_contract_status

FOREIGN KEY (contract_status_id) REFERENCES contract_status(contract_status_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT chk_contract_dates CHECK (contract_end_date >= contract_start_date)

CONTRACT_STATUS

• contract_status_id serial, примарен клуч
• contract_status_name string, задолжителен и уникатен

CUSTOMER

• user_id int PRIMARY KEY DEFAULT 1
• CONSTRAINT fk_customer_user

FOREIGN KEY (user_id) REFERENCES api_user(user_id) ON UPDATE CASCADE ON DELETE SET DEFAULT

CUSTOMER_ORDER

• order_id serial, примарен клуч
• comp_order_id int, задолжителен
• company_employee_user_id int, задолжителен DEFAULT 1
• order_datetime timestamp, задолжителен DEFAULT CURRENT_TIMESTAMP
• o_status_id int, задолжителен
• order_total NUMERIC(10,2), задолжителен
• CONSTRAINT fk_customer_order_company_order

FOREIGN KEY (comp_order_id) REFERENCES company_order(comp_order_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT fk_customer_order_employee

FOREIGN KEY (company_employee_user_id) REFERENCES company_employee(user_id) ON UPDATE CASCADE ON DELETE SET DEFAULT

• CONSTRAINT fk_customer_order_status

FOREIGN KEY (o_status_id) REFERENCES order_status(o_status_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT chk_order_total CHECK (order_total >= 0)

DELIVERY

• delivery_id serial, примарен клуч
• delivery_date DATE, задолжителен DEFAULT CURRENT_DATE
• delivery_notes string
• d_status_id int, задолжителен
• driver_user_id int, задолжителен DEFAULT 1
• CONSTRAINT fk_delivery_status

FOREIGN KEY (d_status_id) REFERENCES delivery_status(d_status_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT fk_delivery_driver

FOREIGN KEY (driver_user_id) REFERENCES driver(user_id) ON UPDATE CASCADE ON DELETE SET DEFAULT

DELIVERY_REVIEW

• delivery_review_id serial, примарен клуч
• delivery_id int, задолжителен и уникатен
• del_review_courier_rating int, задолжителен
• del_review_speed_rating int, задолжителен
• CONSTRAINT fk_delivery_review_delivery

FOREIGN KEY (delivery_id) REFERENCES delivery(delivery_id) ON UPDATE CASCADE ON DELETE CASCADE

• CONSTRAINT chk_delivery_review_courier_rating CHECK (del_review_courier_rating BETWEEN 1 AND 5)
• CONSTRAINT chk_delivery_review_speed_rating CHECK (del_review_speed_rating BETWEEN 1 AND 5)

DELIVERY_STATUS

• d_status_id serial, примарен клуч
• d_status_name string, задолжителен и уникатен

DRINK

• drink_id serial, примарен клуч
• rest_id int, задолжителен
• drink_name string, задолжителен
• drink_milliliters int, задолжителен
• drink_price NUMERIC(10,2), задолжителен
• CONSTRAINT fk_drink_restaurant

FOREIGN KEY (rest_id) REFERENCES restaurant(rest_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT chk_drink_milliliters CHECK (drink_milliliters > 0)
• CONSTRAINT chk_drink_price CHECK (drink_price >= 0)
• CONSTRAINT uq_drink_restaurant_name UNIQUE (rest_id, drink_name)

DRIVER

• user_id int PRIMARY KEY DEFAULT 1
• CONSTRAINT fk_driver_user

FOREIGN KEY (user_id) REFERENCES api_user(user_id) ON UPDATE CASCADE ON DELETE SET DEFAULT

EMPLOYEE_LOYALTY

• emp_loyalty_id serial, примарен клуч
• user_id int, задолжителен и уникатен
• emp_loyalty_curr_points int, задолжителен DEFAULT 0
• emp_loyalty_joined_at timestamp, задолжителен DEFAULT CURRENT_TIMESTAMP
• emp_loyalty_status_id int, задолжителен
• tier_id int, задолжителен
• CONSTRAINT fk_employee_loyalty_user

FOREIGN KEY (user_id) REFERENCES company_employee(user_id) ON UPDATE CASCADE ON DELETE CASCADE

• CONSTRAINT fk_employee_loyalty_status

FOREIGN KEY (emp_loyalty_status_id) REFERENCES employee_loyalty_status(emp_loyalty_status_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT fk_employee_loyalty_tier

FOREIGN KEY (tier_id) REFERENCES loyalty_tier(tier_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT chk_employee_loyalty_points CHECK (emp_loyalty_curr_points >= 0)

EMPLOYEE_LOYALTY_STATUS

• emp_loyalty_status_id serial, примарен клуч
• emp_loyalty_status_name string, задолжителен и уникатен

INGREDIENT

• ingr_id serial, примарен клуч
• ingr_name string, задолжителен и уникатен

LOYALTY_TIER

• tier_id serial, примарен клуч
• tier_name string, задолжителен и уникатен
• tier_minimum_points int, задолжителен
• tier_maximum_points int, задолжителен
• tier_discount_percentage NUMERIC(5,2), задолжителен
• tier_free_delivery_eligibility bool, задолжителен DEFAULT FALSE
• tier_priority_support bool, задолжителен DEFAULT FALSE
• tier_created_at DATE, задолжителен DEFAULT CURRENT_DATE
• tier_update_at timestamp, задолжителен DEFAULT CURRENT_TIMESTAMP
• CONSTRAINT chk_tier_points_nonnegative CHECK (tier_minimum_points >= 0 AND tier_maximum_points >= 0)
• CONSTRAINT chk_tier_points_range CHECK (tier_maximum_points >= tier_minimum_points)
• CONSTRAINT chk_tier_discount CHECK (tier_discount_percentage >= 0 AND tier_discount_percentage <= 100)

LUNCH_TIME

• lunch_time_id serial, примарен клуч
• comp_order_id int
• contract_id int, задолжителен
• lunch_start timestamp, задолжителен
• lunch_end timestamp, задолжителен
• lunch_preorder_offset int, задолжителен DEFAULT 0
• lunch_weekday string, задолжителен
• CONSTRAINT fk_lunch_time_company_order

FOREIGN KEY (comp_order_id) REFERENCES company_order(comp_order_id) ON UPDATE CASCADE ON DELETE SET NULL

• CONSTRAINT fk_lunch_time_contract

FOREIGN KEY (contract_id) REFERENCES contract(contract_id) ON UPDATE CASCADE ON DELETE CASCADE

• CONSTRAINT chk_lunch_time_offset CHECK (lunch_preorder_offset >= 0)
• CONSTRAINT chk_lunch_weekday CHECK (

lunch_weekday IN ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday')

)

• CONSTRAINT chk_lunch_interval CHECK (lunch_end > lunch_start)

MEAL

• meal_id serial, примарен клуч
• cat_id int, задолжителен
• rest_id int, задолжителен
• meal_name string, задолжителен
• meal_description string
• meal_price NUMERIC(10,2), задолжителен
• meal_weight int, задолжителен
• CONSTRAINT fk_meal_category

FOREIGN KEY (cat_id) REFERENCES category(cat_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT fk_meal_restaurant

FOREIGN KEY (rest_id) REFERENCES restaurant(rest_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT chk_meal_price CHECK (meal_price >= 0)
• CONSTRAINT chk_meal_weight CHECK (meal_weight > 0)
• CONSTRAINT uq_meal_restaurant_name UNIQUE (rest_id, meal_name)

ORDER_REVIEW

• order_review_id serial, примарен клуч
• order_id int, задолжителен и уникатен
• order_review_food_rating int, задолжителен
• order_review_res_rating int, задолжителен
• CONSTRAINT fk_order_review_order

FOREIGN KEY (order_id) REFERENCES customer_order(order_id) ON UPDATE CASCADE ON DELETE CASCADE

• CONSTRAINT chk_order_review_food_rating CHECK (order_review_food_rating BETWEEN 1 AND 5)
• CONSTRAINT chk_order_review_res_rating CHECK (order_review_res_rating BETWEEN 1 AND 5)

ORDER_STATUS

• o_status_id serial, примарен клуч
• o_status_name string, задолжителен и уникатен

RESTAURANT

• rest_id serial, примарен клуч
• rest_name string, задолжителен
• rest_email string, задолжителен и уникатен
• rest_location string, задолжителен
• rest_phone string, задолжителен и уникатен
• rest_website string, уникатен

REVIEW

• review_id serial, примарен клуч
• review_comment string
• review_created_at timestamp, задолжителен DEFAULT CURRENT_TIMESTAMP
• review_rating int, задолжителен
• CONSTRAINT chk_review_rating CHECK (review_rating BETWEEN 1 AND 5)

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

Order_Meal

• order_id int, задолжителен
• meal_id int, задолжителен
• quantity int, задолжителен DEFAULT 1
• PRIMARY KEY (order_id, meal_id), примарен клуч
• CONSTRAINT fk_order_meal_order

FOREIGN KEY (order_id) REFERENCES customer_order(order_id) ON UPDATE CASCADE ON DELETE CASCADE

• CONSTRAINT fk_order_meal_meal

FOREIGN KEY (meal_id) REFERENCES meal(meal_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT chk_order_meal_quantity CHECK (quantity > 0)

Order_Drink

• order_id int, задолжителен
• drink_id int, задолжителен
• quantity int, задолжителен DEFAULT 1
• PRIMARY KEY (order_id, drink_id), примарен клуч
• CONSTRAINT fk_order_drink_order

FOREIGN KEY (order_id) REFERENCES customer_order(order_id) ON UPDATE CASCADE ON DELETE CASCADE

• CONSTRAINT fk_order_drink_drink

FOREIGN KEY (drink_id) REFERENCES drink(drink_id) ON UPDATE CASCADE ON DELETE RESTRICT

• CONSTRAINT chk_order_drink_quantity CHECK (quantity > 0)

Meal_Ingredient

• meal_id int, задолжителен
• ingr_id int, задолжителен
• PRIMARY KEY (meal_id, ingr_id), примарен клуч
• CONSTRAINT fk_meal_ingredient_meal

FOREIGN KEY (meal_id) REFERENCES meal(meal_id) ON UPDATE CASCADE ON DELETE CASCADE

• CONSTRAINT fk_meal_ingredient_ingredient

FOREIGN KEY (ingr_id) REFERENCES ingredient(ingr_id) ON UPDATE CASCADE ON DELETE CASCADE

Allergen_Ingredient

• allergen_id int, задолжителен
• ingr_id int, задолжителен
• PRIMARY KEY (allergen_id, ingr_id), примарен клуч
• CONSTRAINT fk_allergen_ingredient_allergen

FOREIGN KEY (allergen_id) REFERENCES allergen(allergen_id) ON UPDATE CASCADE ON DELETE CASCADE

• CONSTRAINT fk_allergen_ingredient_ingredient

FOREIGN KEY (ingr_id) REFERENCES ingredient(ingr_id) ON UPDATE CASCADE ON DELETE CASCADE