ЕР Дијаграм

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

Ентитети

ADMIN

• user_id INTEGER PRIMARY KEY DEFAULT 1,
• CONSTRAINT fk_admin_user

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

ALERGEN

• alergen_id SERIAL PRIMARY KEY,
• alergen_name VARCHAR(255) NOT NULL UNIQUE,
• alergen_description VARCHAR(255)

API_USER

• user_id SERIAL PRIMARY KEY,
• user_first_name VARCHAR(255) NOT NULL,
• user_last_name VARCHAR(255) NOT NULL,
• user_email VARCHAR(255) NOT NULL UNIQUE,
• user_phone_no VARCHAR(20) UNIQUE,
• user_password_hash TEXT NOT NULL

CATEGORY

• cat_id SERIAL PRIMARY KEY,
• cat_name VARCHAR(255) NOT NULL UNIQUE,
• cat_description VARCHAR(255)

COMPANY

• company_id SERIAL PRIMARY KEY,
• company_name VARCHAR(255) NOT NULL UNIQUE,
• company_address VARCHAR(255) NOT NULL

COMPANY_ORDER

• comp_order_id SERIAL PRIMARY KEY,
• company_id INTEGER NOT NULL,
• delivery_id INTEGER UNIQUE,
• 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 PRIMARY KEY,
• company_id INTEGER NOT NULL,
• rest_id INTEGER NOT NULL,
• contract_status_id INTEGER NOT NULL,
• contract_start_date DATE NOT NULL,
• contract_end_date DATE NOT NULL,
• 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 PRIMARY KEY,
• contract_status_name VARCHAR(255) NOT NULL UNIQUE

CUSTOMER

• user_id INTEGER 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 PRIMARY KEY,
• comp_order_id INTEGER NOT NULL,
• company_employee_user_id INTEGER NOT NULL DEFAULT 1,
• order_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
• o_status_id INTEGER NOT NULL,
• order_total NUMERIC(10,2) NOT NULL,
• 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 PRIMARY KEY,
• delivery_date DATE NOT NULL DEFAULT CURRENT_DATE,
• delivery_notes VARCHAR(255),
• d_status_id INTEGER NOT NULL,
• driver_user_id INTEGER NOT NULL 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 PRIMARY KEY,
• delivery_id INTEGER NOT NULL UNIQUE,
• del_review_courier_rating INTEGER NOT NULL,
• del_review_speed_rating INTEGER NOT NULL,
• 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 PRIMARY KEY,
• d_status_name VARCHAR(255) NOT NULL UNIQUE

DRINK

• drink_id SERIAL PRIMARY KEY,
• rest_id INTEGER NOT NULL,
• drink_name VARCHAR(255) NOT NULL,
• drink_milliliters INTEGER NOT NULL,
• drink_price NUMERIC(10,2) NOT NULL,
• 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 INTEGER 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 PRIMARY KEY,
• user_id INTEGER NOT NULL UNIQUE,
• emp_loyalty_curr_points INTEGER NOT NULL DEFAULT 0,
• emp_loyalty_joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
• emp_loyalty_status_id INTEGER NOT NULL,
• tier_id INTEGER NOT NULL,
• 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 PRIMARY KEY,
• emp_loyalty_status_name VARCHAR(255) NOT NULL UNIQUE

INGREDIENT

• ingr_id SERIAL PRIMARY KEY,
• ingr_name VARCHAR(255) NOT NULL UNIQUE

LOYALTY_TIER

• tier_id SERIAL PRIMARY KEY,
• tier_name VARCHAR(255) NOT NULL UNIQUE,
• tier_minimum_points INTEGER NOT NULL,
• tier_maximum_points INTEGER NOT NULL,
• tier_discount_percentage NUMERIC(5,2) NOT NULL,
• tier_free_delivery_eligibility BOOLEAN NOT NULL DEFAULT FALSE,
• tier_priority_support BOOLEAN NOT NULL DEFAULT FALSE,
• tier_created_at DATE NOT NULL DEFAULT CURRENT_DATE,
• tier_update_at TIMESTAMP NOT NULL 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 PRIMARY KEY,
• comp_order_id INTEGER,
• contract_id INTEGER NOT NULL,
• lunch_start TIMESTAMP NOT NULL,
• lunch_end TIMESTAMP NOT NULL,
• lunch_preorder_offset INTEGER NOT NULL DEFAULT 0,
• lunch_weekday VARCHAR(15) NOT NULL,
• 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 PRIMARY KEY,
• cat_id INTEGER NOT NULL,
• rest_id INTEGER NOT NULL,
• meal_name VARCHAR(255) NOT NULL,
• meal_description VARCHAR(255),
• meal_price NUMERIC(10,2) NOT NULL,
• meal_weight INTEGER NOT NULL,
• 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 PRIMARY KEY,
• order_id INTEGER NOT NULL UNIQUE,
• order_review_food_rating INTEGER NOT NULL,
• order_review_res_rating INTEGER NOT NULL,
• 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 PRIMARY KEY,
• o_status_name VARCHAR(255) NOT NULL UNIQUE

RESTAURANT

• rest_id SERIAL PRIMARY KEY,
• rest_name VARCHAR(255) NOT NULL,
• rest_email VARCHAR(255) NOT NULL UNIQUE,
• rest_location VARCHAR(255) NOT NULL,
• rest_phone VARCHAR(20) NOT NULL UNIQUE,
• rest_website VARCHAR(255) UNIQUE

REVIEW

• review_id SERIAL PRIMARY KEY,
• review_comment VARCHAR(255),
• review_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
• review_rating INTEGER NOT NULL,
• CONSTRAINT chk_review_rating CHECK (review_rating BETWEEN 1 AND 5)