Changes between Version 4 and Version 5 of Normalization

Timestamp:

09/18/25 16:08:26 (7 hours ago)

Author:

226030

Comment:

--

Normalization

@@ -2 +2 @@
 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
+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
@@ -9 +9 @@
 
 == 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}
+||= 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:
-- 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
+
+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 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}
+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 = {e_id, e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, 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}* = {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}
+
+{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), R6(Reservation)
+
+R3(StaffRole), R4(FrontStaff), R5(Customer), R10(Reservation), R8(Table) R9 (FrontStaff_Managed_reservation)
 
 == Shift Management 
@@ -132 +158 @@
 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}*= {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. 
@@ -142 +168 @@
 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}
+{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
@@ -160 +186 @@
   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}
+  {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}
@@ -172 +198 @@
   table_number -> seat_capacity
 
-  {table_number}+ = {table_number, seat_capacity}
+  {table_number}* = {table_number, seat_capacity}
 
   R5 = {table_number, seat_capacity}
@@ -188 +214 @@
   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}
+  {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}
@@ -203 +229 @@
   ca_id -> ca_name, ca_is_available
 
-  {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}