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 written in bold and underline.
• Foreign keys are marked with asterisk after the attribute name.

Relations - Relational Schema

APP_USER(user_id, first_name, last_name, email, password, phone, administrator_id*(ADMINNISTRATOR))

ADMINISTRATOR(user_id*(APP_USER), authorized_from, authorized_to)

RESTAURANT_OWNER(user_id*(APP_USER))

DELIVERY_MAN(user_id*(APP_USER))

CUSTOMER(user_id*(APP_USER))

ORDERS(order_id, address_id*(ADDRESS), restaurant_id*(RESTAURANT), deliveryman_id*(DELIVERY_MAN), user_id*(CUSTOMER), address_id*(ADDRESS),order_date, comment, order_status, total_amount)

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), created_at, updated_at, name)

CATEGORY(category_id, name, description)

MENU_ITEM(menu_id*(MENU), *item_id(ITEM))

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))

SHOPPING_CART(cart_id, user_id*(USER))

CART_ITEMS(cart_id*(SHOPPING_CART), item_id*(ITEM), quantity)

DDL Script for deletion and creation of tables

DDL.sql​

DML Script for insertion of dummy data into tables

DML.sql​

Relational Schema Diagram