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

user_id	email	city	street	phone_number	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
1	test@…	Strumica	Mladinska 3	70003005	password1	30000	40000	1	Server	NULL	15.7.2025 10:33	15.7.2025 20:00	4	1	2	test2@…	Marsal Tito 10	Strumica	70001002	password2
1	test@…	Strumica	Mladinska 3	70003005	password1	30000	40000	1	Server	NULL	16.7.2025 10:33	17.7.2025 20:00	6	2	2	test2@…	Marsal Tito 10	Strumica	70001002	password2
5	test5@…	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@…	Marsal Tito 10	Strumica	70001002	password2
5	test5@…	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@…	Mladinska 5	Strumica	70003003	password2

R = {e_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}

Functional Dependencies:

• e_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 → e_id, stay_length, creation_timestamp, datetime, number_of_people, customer_id
• customer_id → c_email, c_street, c_city, c_phone_number, c_password

Left only: reservation_id

Left and right: e_id, staffRole_id, customer_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

{reservation_id}* = R => 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 e_id

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

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

R2 = {e_id, stay_length, creation_timestamp, datetime, number_of_people, reservation_id, customer_id, c_email, c_street, c_city, c_phone_number,c_password}

Step 2: Decompose R1 by staffRole_id

{staffRole_id}*= {staffRole_id, name}

R3 = {staffRole_id, name}

R4 = {e_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}* = {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 = {reservation_id, e_id, stay_length, creation_timestamp, datetime, number_of_people, customer_id}

Final BCNF Relations:

• R3(StaffRole), R4(FrontStaff), R5(Customer), R6(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

There isn’t one attribute that is a candidate for primary key so BCNF is violated.

Step 1: Decompose R by Order_id

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

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

R2 = {order_id, 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}

Step 2: Decompose R1 by table_number

{table_number}*= {table_number, seat_capacity}

R3={table_number,seat_capacity}

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

Due to the issue that when there is a TabOrder the delivery_address and customer_id will always be NULL values and when there is an OnlineOrder table_number will always have NULL value we split R4 into

R5= {order_id, employee_id, o_timestamp, o_status}

R6= {order_id, table_number}

R7= {order_id, delivery_address, customer_id}

Step3: Decompose R2 by p_id

{p_id}I* = { order_id, tip_amount, p_id, p_timestamp, payment_type, amount}

R8 = {order_id, tip_amount, p_id, p_timestamp, payment_type, amount}

Because we have order_id in R8 we don’t need to keep p_id in R9.

R9 = {order_id, 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

Step 4: Decompose R9 by pr_id

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

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

Step 5: Decompose R10 by ca_id

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

R12= {ca_id, ca_name, ca_is_available}

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

Step 6: Decompose Inventory from R13

{i_quantity, i_restock_level} are functionally dependent on pr_id only when manage_inventory is true. If manage_inventory is false it leads to NULL values in our table so we decompose R13 to

R14 = {pr_id, pr_description, tax_class, pr_price, pr_name, manage_inventory, ca_id}

And

R15= {pr_id, i_quantity,i_restock_level}

Final BCNF Realtions:

R3(Tables), R5(Orders), R6(TabOrders), R7(OnlineOrders), R8(Payments), R11(OrderItems), R14(Products), R15(Inventory)

Conclusion

Ultimately, the process of normalization validated our initial design. The final tables derived through formal decomposition were a perfect match for the original tables represented in the ER diagram, confirming the correctness of the schema.