Changes between Initial Version and Version 1 of Normalization

Timestamp:

07/16/25 21:08:32 (3 months ago)

Author:

226030

Comment:

--

Normalization

@@ +1 @@
+= 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@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 || 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 ||
+|| 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 ||
+|| 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 ||
+
+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} – OrderItems
+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.