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), address_id*(ADDRESS),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(restaurant_id, address_id*(ADDRESS), 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), item_id*(ITEM), 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))

DDL Script for deletion and creation of tables

ddl.sql

DML Script for insertion of dummy data into tables

dml.sql

Relational Schema Diagram