Relational Mapping - Relational Schema and DDL

Notation

• Entities are denoted with capital letters and their attributes are defined (a1, a2, ..., an) where n is the number of attributes the entity has.
• Primary keys are denoted with underscore and boldness.
• Foreign keys are denoted with asterisk suffixing the attribute.

Relations - Relational Schema

USER(user_id, first_name, last_name, email, password, phone)
RESTAURANT_OWNER(user_id*(USER))
DELIVERY_MAN(user_id*(USER))
CUSTOMER(user_id*(USER))
ORDER(order_id, user_id*(USER), order_date, comment, status)
ITEM(item_id, image_url, name, price, description)
ORDER_ITEMS(order_id*(ORDER), item_id(ITEM), quantity, total_price)
INGREDIENT(ingredient_id, name)
ITEM_INGREDIENT(item_id*(ITEM), ingredient_id*(INGREDIENT), quantity)
DELIVERY_FIRM(deliveryfirm_id, name)
DELIVERY_ASSIGNMENT(deliveryfirm_id*(DELIVERY_FIRM), user_id*(DELIVERY_MAN))
RESTAURANT(restaurantid, name, website_url, opening_time, closing_time, email, phone_number)
RESTAURANT_OWNERS(user_id*(RESTAURANT_OWNER), restaurant_id*(RESTAURANT))
COSTS(cost_id, restaurant_id*(RESTAURANT), date, amount, type)
EARNINGS(earning_id, restaurant_id*(RESTAURANT), date, amount, source)
MENU(menu_id, restaurant_id*(RESTAURANT), created_at, updated_at, name)
CATEGORY(category_id, name, description)
RESTAURANT_CATEGORY(category_id*(CATEGORY), restaurant_id*(RESTAURANT))
ITEM_CATEGORY(category_id*(CATEGORY), item_id*(ITEM))
ADDRESS(address_id, city, street, postal_code)
USER_ADDRESSES(address_id*(ADDRESS), user_id*(USER))