Relational Mapping - Partial Transformation

Notations

• Primary keys are marked with bold and underlined letters
  
• Foreign keys are marked with *

Relational Schema

1. User(id, email, street, city, phone_number, password)
2. Employee(user_id*, net_salary, gross_salary)
3. Customer(user_id*)
4. Manager(user_id*)
5. FrontStaff(employee_id*, tip_percent, staff_role_id*)
6. BackStaff(employee_id*, staff_role_id*)
7. StaffRole(staff_role_id, name)
8. Shift(shift_id, date, start, end, manager_id*)
9. Assignment(id, clock_in_time, clock_out_time, manager_id*, employee_id*, shift_id*)

10. Reservation(id, stay_length, datetime, creation_timestamp, number_of_people, user_id*)
11. Reservation_managed_FrontStaff(reservation_id*, employee_id*, table_number*)

12. Category(id, name, is_available)
13. Product(id, name, price, tax_class, description, manage_inventory, category_id*)
14. Inventory(product_id*, quantity, restock_level)
15. OrderItem(id, quantity, price, is_processed, timestamp, product_id*, order_id*)

16. Order(id, timestamp, status)
17. TabOrder(order_id*, table_number*, frontstaff_id*)
18. OnlineOrder(order_id*, customer_id*, delivery_adress)
19. Table(table_number, seat_capacity)
20. Payment(id, tip_amount, timestamp, payment_type, amount, order_id*)

DDL Script for Dropping and Creating Tables

ddl (1).sql​​

DML Script for Populating Tables with Data

dml.sql​

Tables