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

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