Changes between Version 2 and Version 3 of Normalization

Timestamp:

08/13/25 20:56:31 (5 days ago)

Author:

226030

Comment:

--

Normalization

@@ -124 +124 @@
 - 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.
+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.