= Normalization and improvement of database schema
Due to the complexity of the restaurant management system and the large number of attributes tied to different logical responsibilities, it is not feasible to normalize all data in a single relation. Instead, normalization is applied to functional modules individually, based on the logical responsibilities within the system.
The system is divided into three major functional domains:
1. Reservation Management – comprising: Reservation, FrontStaff and Customer, Table, FrontStaff_Manage_Reservation
2. Shifts Management – comprising: Manager, Shift, Assignment, and Employee
3. Order Management – comprising: Employee, TabOrder, OnlineOrder, Payment, Table, Customer, OrderItem, Product, Inventory, and Category



== Reservation Management
||= fs_manage_reservation_id =||= employee_id =||= e_email =||= e_city =||= e_street =||= e_phone_number =||= e_password =||= net_salary =||= gross_salary =||= staffRole_id =||= name =||= stay_length =||= creation_timestamp =||= datetime =||= number_of_people =||= reservation_id =||= customer_id =||= c_email =||= c_street =||= c_city =||= c_phone_num =||= c_password =||= table_number =||= seat_capacity
|| 1 || 1 || test@hotmail.com || Strumica || Mladinska 3 || 70003005 || password1 || 30000 || 40000 || 1 || Server || NULL || 15.7.2025 10:33 || 15.7.2025 20:00 || 4 || 1 || 2 || test2@hotmail.com || Marsal Tito 10 || Strumica || 70001002 || password2 || 1 || 4
|| 2 || 1 || test@hotmail.com || Strumica || Mladinska 3 || 70003005 || password1 || 30000 || 40000 || 1 || Server || NULL || 16.7.2025 10:33 || 17.7.2025 20:00 || 6 || 2 || 2 || test2@hotmail.com || Marsal Tito 10 || Strumica || 70001002 || password2 || 2 || 8
|| 3 || 5 || test5@hotmail.com || Strumica || Mladinska 12 3 || 70005005 || password1 || 20000 || 37000 || 4 || Hostess || NULL || 18.7.2025 10:33 || 18.7.2025 20:00 || 6 || 3 || 2 || test2@hotmail.com || Marsal Tito 10 || Strumica || 70001002 || password2 || 2 || 8
|| 4 || 5 || test5@hotmail.com || Strumica || Mladinska 12 3 || 70005005 || password1 || 20000 || 37000 || 4 || Hostess || NULL || 18.7.2025 10:33 || 18.7.2025 21:00 || 6 || 4 || 3 || test3@hotmail.com || Mladinska 5 || Strumica || 70003003 || password2 || 3 || 8

R = {fs_manage_reservation_id, employee_id, e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id, name, stay_length, creation_timestamp, datetime, number_of_people, reservation_id, customer_id, c_email, c_street, c_city, c_phone_number, c_password, table_number, seat_capacity}

Functional Dependencies:

fs_manage_reservation_id → reservation_id, employee_id, table_number

employee_id → e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id

staffRole_id → name

reservation_id → stay_length, creation_timestamp, datetime, number_of_people, customer_id, table_number

customer_id → c_email, c_street, c_city, c_phone_number, c_password

table_number → seat_capacity

Left only: fs_manage_reservation_id

Left and right: employee_id, staffRole_id, customer_id, table_number, reservation_id

Right only: e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, name, stay_length, creation_timestamp, datetime, number_of_people, c_email, c_street, c_city, c_phone_number, c_password

{fs_manage_reservation_id}* = R => fs_manage_reservation_id is the primary key

We can see that 1NF and 2NF are satisfied due to having atomic attributes and a single primary key, but BNCF is not satisfied due to transitive dependencies.

Decomposition: Step 1: Decompose R by employee_id

{e_id}* = {employee_id, e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id, name}

R1 = {employee_id, e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id, name}

R2 = {employee_id, fs_manage_reservation_id, stay_length, creation_timestamp, datetime, number_of_people, reservation_id, customer_id, c_email, c_street, c_city, c_phone_number,c_password, table_number, seat_capacity}

Step 2: Decompose R1 by staffRole_id

{staffRole_id}*= {staffRole_id, name}

R3 = {staffRole_id, name}

R4 = {employee_id, e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id}

Step 3: Decompose R2 by customer_id

{customer_id}* = {customer_id, c_email, c_street, c_city, c_phone_number, c_password}

R5 = {customer_id, c_email, c_street, c_city, c_phone_number, c_password}

R6 = {fs_manage_reservation_id, reservation_id, employee_id, stay_length, creation_timestamp, datetime, number_of_people, customer_id, table_number, seat_capacity}

Step 4: Decompose R6 by table_number

{table_number}* = {table_number, seat_capacity}

R7 = {fs_manage_reservation_id, reservation_id, employee_id, stay_length, creation_timestamp, datetime, number_of_people, customer_id, table_number}

R8 = {table_number, seat_capacity}

Step 5: Decompose R7 by reservation_id

{reservation_id}* = {reservation_id, stay_length, creation_timestamp, datetime, number_of_people, customer_id}

R9 = {fs_manage_reservation_id, reservation_id, employee_id, table_number}

R10 = {reservation_id, stay_length, creation_timestamp, datetime, number_of_people, customer_id}

Final BCNF Relations:

R3(StaffRole), R4(FrontStaff), R5(Customer), R10(Reservation), R8(Table) R9 (FrontStaff_Managed_reservation)

== Shift Management 

For manager I will use m, for employee e.

R = {m_id,m_email,m_city,m_street, m_phone_number, m_password, m_net_salary, m_gross_salary,e_id,e_email,e_city,e_street, e_phone_number, e_password, e_net_salary, e_gross_salary ,  shift_id, date, start, end, assignment_id, clock_in_time, clock_out_time, is_assigned}

Functional Dependencies:
- e_id → e_email, e_city, e_street, e_phone_number, e_password, e_net_salary, e_gross_salary
- m_id → m_email, m_city, m_street, m_phone_number, m_password, m_net_salary, m_gross_salary
- shift_id → date, start, end, m_id
- assignment_id → shift_id, e_id, clock_in_time, clock_out_time, is_assigned

Left only: assignment_id

Left and right: e_id, m_id, shift_id

Right only: m_email,m_city,m_street, m_phone_number, m_password, m_net_salary, m_gross_salary, e_email, e_city, e_street, e_phone_number, e_password, e_net_salary, e_gross_salary , date, start, end, clock_in_time, clock_out_time, is_assigned

{assignment_id}* = R => assignment_id is the primary key

We can see that 1NF and 2NF are satisfied due to having atomic attributes and a single primary key, but BNCF is not satisfied due to transitive dependencies.

Decomposition:

Step 1: Decompose R by shift_id
  {shift_id}* = {shift_id, date, start, end, m_id,m_email,m_city,m_street, m_phone_number, m_password, m_net_salary, m_gross_salary}

  R1 = {shift_id, date, start, end, m_id,m_email,m_city,m_street, m_phone_number, m_password, m_net_salary, m_gross_salary}

  R2 = {shift_id, e_id, e_email,e_city,e_street, e_phone_number, e_password, e_net_salary, e_gross_salary, assignment_id, clock_in_time, clock_out_time, is_assigned}

Step 2: Decompose R1 by m_id
  {m_id}*= {m_id,m_email,m_city,m_street, m_phone_number, m_password, m_net_salary, m_gross_salary}

  R3 = {m_id, m_email, m_city, m_street, m_phone_number, m_password, m_net_salary, m_gross_salary}

  R4 = {m_id, shift_id, date, start, end}

Step 3: Decompose R2 by e_id
  {e_id}* = {e_id, e_email,e_city,e_street, e_phone_number, e_password, e_net_salary, e_gross_salary}

  R5 = {e_id, e_email,e_city,e_street, e_phone_number, e_password, e_net_salary, e_gross_salary}

  R6 = {e_id, shift_id, assignment_id, clock_in_time, clock_out_time, is_assigned}

Final BCNF Relations:
- R3(Manager), R4(Shifts), R5(Employees), R6(Assigments)

== Order Management 
I will refer to order as o, orderItem as oi, customer as c, payment as p, product as pr,category as ca,inventory as i

R = {order_id, employee_id, o_timestamp, o_status, seat_capacity, table_number, delivery_address, tip_amount, p_id, p_timestamp, payment_type, amount, oi_id, oi_timestamp, oi_is_processed, oi_price, oi_quantity, pr_id, pr_description, tax_class, pr_price, pr_name, manage_inventory, ca_id, ca_name, ca_is_available, i_quantity, i_restock_level}

Functional Dependencies:

- order_id -> employee_id, o_timestamp, o_status
- order_id -> table_number
- order_id -> delivery_address, customer_id
- table_number -> seat_capacity
- p_id -> p_timestamp, payment_type, amount, tip_amount, order_id
- oi_id -> oi_timestamp, oi_is_processed, oi_price, pr_id, order_id, oi_quantity
- pr_id -> pr_description, tax_class, pr_price, pr_name, manage_inventory, i_quantity, i_restock_level, ca_id
- manage_inventory -> i_quantity, i_restock_level
- ca_id -> ca_name, ca_is_available

Left only: oi_id, p_id, 

Left and right: table_number, order_id, pr_id, ca_id,

Right only: employee_id, o_timestamp, o_status, delivery_address, customer_id, seat_capacity, p_timestamp, payment_type, amount, tip_amount, oi_timestamp, oi_is_processed, oi_price, oi_quantity, i_quantity, i_restock_level, ca_name, ca_is_available, manage_inventory, pr_description, tax_class, pr_price, pr_name

From our analysis we will try {oi_id, p_id} as a candidate key:

{oi_id, p_id}*= {oi_id, p_id, oi_timestamp, oi_is_processed, oi_price, pr_id, order_id, oi_quantity, pr_description, tax_class, pr_price, pr_name, manage_inventory, i_quantity, i_restock_level, ca_id, ca_name, ca_is_available,p_timestamp, payment_type, amount, tip_amount, employee_id, o_timestamp, o_status, table_number, delivery_address, customer_id, seat_capacity}

{oi_id, p_id} is the only candidate key and primary key. 

There are no multi-valued dependencies in our relation, and therefore it satisfies the first normal form.

But our previous defined relation isn’t in 2NF because there is a partial dependency:

p_id -> p_timestamp, payment_type, amount, tip_amount, order_id

{p_id}*= {p_id, p_timestamp, payment_type, amount, tip_amount, order_id, employee_id, o_timestamp, o_status, table_number, delivery_address, customer_id, seat_capacity}

Step 1: Decompose R by p_id
  R1 = {p_id, p_timestamp, payment_type, amount, tip_amount, order_id, employee_id, o_timestamp, o_status, table_number, delivery_address, customer_id, seat_capacity}

  R2 = {oi_id, oi_timestamp, oi_is_processed, oi_price, order_id, pr_id, oi_quantity, pr_description, tax_class, pr_price, pr_name, manage_inventory, i_quantity, i_restock_level, ca_id, ca_name, ca_is_available}

Step 2. Decompose R1 by order_id
  Functional dependencies in R1:

  p_id -> p_timestamp, payment_type, amount, tip_amount, order_id

  order_id -> employee_id, o_timestamp, o_status, table_number, delivery_address, customer_id

  table_number -> seat_capacity

  R1 is not in BCNF because of transitive dependencies

  {order_id}* = {order_id, employee_id, o_timestamp, o_status, table_number, delivery_address, customer_id, seat_capacity}

  R3 = {order_id, employee_id, o_timestamp, o_status, table_number, delivery_address, customer_id, seat_capacity}
  
  R4 = {p_id, p_timestamp, payment_type, amount, tip_amount, order_id}
Step 3. Decompose R3 by table_number
  R3 is still not in BCNF because of transitive dependency
  
  order_id -> employee_id, o_timestamp, o_status, table_number, delivery_address, customer_id

  table_number -> seat_capacity

  {table_number}* = {table_number, seat_capacity}

  R5 = {table_number, seat_capacity}

  R6 = {order_id, employee_id, o_timestamp, o_status, table_number, delivery_address, customer_id}

Step 4. Decompose R2 by pr_id

  R2 is not in BCNF because of transitive dependencies

  oi_id -> oi_timestamp, oi_is_processed, oi_price, pr_id, order_id, oi_quantity

  pr_id -> pr_description, tax_class, pr_price, pr_name, manage_inventory, i_quantity, i_restock_level, ca_id

  ca_id -> ca_name, ca_is_available

  {pr_id}* = {pr_id, pr_description, tax_class, pr_price, pr_name, manage_inventory, i_quantity, i_restock_level, ca_id}

  R7 = {pr_id, pr_description, tax_class, pr_price, pr_name, manage_inventory, i_quantity, i_restock_level, ca_id, ca_name, ca_is_available}

  R8 = {oi_id, oi_timestamp, oi_is_processed, oi_price, pr_id, order_id, oi_quantity}

  In R8, I exclude attributes that are functionally dependent on order_id, as these dependencies have already been addressed in the decompositions from Steps 2 and 3 to avoid redundancy.

Step 5. Decomposing R7 by ca_id
  R7 is not in BCNF due to transitive dependencies

  pr_id -> pr_description, tax_class, pr_price, pr_name, manage_inventory, i_quantity, i_restock_level, ca_id

  ca_id -> ca_name, ca_is_available

  {ca_id}* = {ca_id, ca_name, ca_is_available}

  R9 = {ca_id, ca_name, ca_is_available}

  R10 = {pr_id, pr_description, tax_class, pr_price, pr_name, manage_inventory, i_quantity, i_restock_level, ca_id}

From the normalization steps, we obtain the relations R4 (Payments), R5 (Tables), R6 (Orders), R8 (OrderItems), R9 (Categories), and R10 (Products). These do not exactly match our initial database design. The difference comes from our goal to minimize NULL values and improve data organization. We chose to decompose the Orders table into TabOrders and OnlineOrders to separate dine-in and delivery-specific attributes. We also store inventory data in a separate Inventory table, where the primary key is product_id, because we give the user the option to store inventory information only for products they wish to track. This way, products without inventory data do not require NULL values in the main Products table.