Changes between Initial Version and Version 1 of Normalization

Timestamp:

01/21/26 22:07:34 (3 weeks ago)

Author:

233144

Comment:

--

Normalization

@@ +1 @@
+= Normalization
+
+Our database has the following identificators: user_id, component_id, build_id, review_id and suggestion_id.\\
+Some of the relations have composite primary keys, such as:\\
+(build_id, component_id) - build_component\\
+(build_id, user_id) - rating_build\\
+(build_id, user_id) - favorite_build\\
+The component subtypes like CPU, GPU, Motherboard etc. do not have separate identificators, because they use component_id as both primary and foreign key referencing the **components** table.
+
+== Functional Dependencies
+
+user_id → username, password, email\\
+user_id → / (admins is a subtype of user with no additional attributes)\\
+
+component_id → name, brand, price, type, img_url\\
+
+component_id → socket, cores, threads, base_clock, boost_clock, tdp                 (cpu)\\
+component_id → vram, tdp, base_clock, boost_clock, chipset, length                (gpu)\\
+component_id → memory_type, speed, capacity, modules                              (memory)\\
+component_id → storage_type, capacity, form_factor                                (storage)\\
+component_id → psu_type, wattage, form_factor                                     (power_supply)\\
+component_id → socket, chipset, form_factor, ram_type, num_ram_slots, max_ram_capacity, pci_express_slots                                (motherboard)\\
+component_id → cooler_max_height, gpu_max_length                                   (pc_case)\\
+component_id → cooler_type, height, max_tdp_supported                               (cooler)\\
+component_id → num_slots, interface                                                (memory_card)\\
+component_id → form_factor, type, interface, write_speed, read_speed               (optical_drive)\\
+component_id → sample_rate, bit_depth, chipset, interface, channel                 (sound_carad)\\
+component_id → length_cm, type                                                     (cable)\\
+component_id → wifi_version, interface, num_antennas                               (network_adapter)\\
+component_id → num_ports, speed, interface                                        (network_card)\\
+
+build_id → user_id, name, created_at, description, total_price, is_approved\\
+build_id, component_id → / (build_component)\\
+
+build_id, user_id → / (favorite_build)\\
+build_id, user_id → value (rating_build)\\
+
+review_id → build_id, user_id, content, created_at\\
+
+suggestion_id → user_id, admin_id, link, description, admin_comment, status, component_type\\
+
+== Universal Relation (R)
+
+R = {
+user_id, username, password, email,
+component_id, name, brand, price, type, img_url,
+cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp,
+gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length,
+memory_type, memory_speed, memory_capacity, memory_modules,
+storage_type, storage_capacity, storage_form_factor,
+psu_type, psu_wattage, psu_form_factor,
+motherboard_socket, motherboard_chipset, motherboard_form_factor,
+ram_type, num_ram_slots, max_ram_capacity, pci_express_slots,
+case_cooler_max_height, case_gpu_max_length,
+cooler_type, cooler_height, max_tdp_supported,
+build_id, build_name, created_at, description, total_price, is_approved,
+rating_value,
+review_id, review_content, review_created_at,
+suggestion_id, link, admin_comment, status, component_type
+}
+
+Left (determinants):\\
+user_id\\
+component_id\\
+build_id\\
+review_id\\
+suggestion_id\\
+
+Right (descriptive attributes), attributes with similar names are prefixed (cpu_, gpu_, build_, etc.):\\
+username, password, email,\\
+name, brand, price, type, img_url,\\
+cpu_*, gpu_*, memory_*, storage_*, psu_*, motherboard_*, case_*, cooler_*,\\
+build_name, created_at, description, total_price, is_approved,\\
+rating_value,\\
+review_content, review_created_at,\\
+link, admin_comment, status, component_type\\
+
+Left and right:\\
+user_id\\
+component_id\\
+build_id\\
+
+== Attribute Closures
+
+user_id+ = { user_id, username, password, email }
+
+component_id+ = { component_id, name, brand, price, type, img_url, 
+cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp,
+gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length,
+memory_type, memory_speed, memory_capacity, memory_modules,
+storage_type, storage_capacity, storage_form_factor,
+psu_type, psu_wattage, psu_form_factor,
+motherboard_socket, motherboard_chipset, motherboard_form_factor,
+ram_type, num_ram_slots, max_ram_capacity, pci_express_slots,
+case_cooler_max_height, case_gpu_max_length,
+cooler_type, cooler_height, max_tdp_supported,
+num_slots, interface, form_factor, type, write_speed, read_speed,
+sample_rate, bit_depth, chipset, interface, channel,
+length_cm, type,
+wifi_version, interface, num_antennas,
+num_ports, speed, interface }
+
+build_id+ = { build_id, user_id, name, created_at, description, total_price, is_approved }
+
+(build_id, component_id)+ = { build_id, component_id }   -- Build_Component
+
+(build_id, user_id)+ = { build_id, user_id, rating_value }   -- RatingBuild
+
+review_id+ = { review_id, build_id, user_id, content, created_at }
+
+suggestion_id+ = { suggestion_id, user_id, admin_id, link, description, admin_comment, status, component_type }
+
+
+The union of\\
+{ user_id, component_id, build_id, review_id, suggestion_id }+\\
+covers all attributes of R, so 1NF (First Normal Form) is satisfied. Now in this (lets call it) "super-table", if we consider **component_id** + other IDs as a composite key for the super-table, attributes like **cpu_socket** depend on **component_id** only, not on other IDs like **build_id** or **user_id**. This is a partial dependency, which means that 2NF is not satisfied.
+
+== 2NF Decomposition
+
+**First we will identify the partial dependencies:**\\
+
+user_id → username, email, password, is_admin\\
+
+component_id → name, brand, price, type, img_url, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp,
+gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length,
+memory_type, memory_speed, memory_capacity, memory_modules,
+storage_type, storage_capacity, storage_form_factor,
+psu_type, psu_wattage, psu_form_factor,
+motherboard_socket, motherboard_chipset, motherboard_form_factor,
+ram_type, num_ram_slots, max_ram_capacity, pci_express_slots,
+case_cooler_max_height, case_gpu_max_length,
+cooler_type, cooler_height, max_tdp_supported
+
+build_id → user_id, name, created_at, description, total_price, is_approved
+
+(build_id, user_id) → rating_value
+
+review_id → review_build_id (FK build_id), review_user_id (FK user_id), review_content
+
+suggestion_id → suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
+
+(build_id, component_id) → /
+
+(build_id, user_id) → /
+
+**Then we will extract the proper relations:**\\
+
+users { user_id, username, email, password } - BCNF
+admins { user_id } → FK to users - BCNF
+
+components { component_id, name, brand, price, type, img_url } - BCNF
+
+cpu { component_id, socket, cores, threads, base_clock, boost_clock, tdp } → FK to components - BCNF\\
+gpu { component_id, vram, tdp, base_clock, boost_clock, chipset, length } → FK to components - BCNF\\
+memory { component_id, type, speed, capacity, modules } → FK to components - BCNF\\
+storage { component_id, type, capacity, form_factor } → FK to components - BCNF\\
+power_supply { component_id, type, wattage, form_factor } → FK to components - BCNF\\
+motherboard{ component_id, socket, chipset, form_factor, ram_type, num_ram_slots, max_ram_capacity, pci_express_slots } → FK to components - BCNF\\
+pc_case { component_id, cooler_max_height, gpu_max_length } → FK to components - BCNF\\
+case_storage_form_factors { case_id, form_factor, num_slots } → FK to pc_case - BCNF\\
+case_ps_form_factors { case_id, form_factor } → FK to pc_case - BCNF\\
+case_mobo_form_factors { case_id, form_factor } → FK to pc_case - BCNF\\
+cooler { component_id, type, height, max_tdp_supported } → FK to components - BCNF\\
+cooler_cpu_sockets { cooler_id, socket } → FK to cooler - BCNF\\
+memory_card { component_id, num_slots, interface } → FK to components - BCNF\\
+optical_drive { component_id, form_factor, type, interface, write_speed, read_speed } → FK to components - BCNF\\
+sound_card { component_id, sample_rate, bit_depth, chipset, interface, channel } → FK to components - BCNF\\
+cables { component_id, length_cm, type } → FK to components - BCNF\\
+network_adapter { component_id, wifi_version, interface, num_antennas } → FK to components - BCNF\\
+network_card { component_id, num_ports, speed, interface } → FK to components - BCNF\\
+
+build { build_id, build_user_id, build_name, created_at, description, total_price, is_approved } → FK to users - BCNF\\
+build_component { build_id, component_id } → FK to build and components - BCNF\\
+
+favorite_build { build_id, user_id } → FK to build and users - BCNF\\
+rating_build { build_id, user_id, value } → FK to build and users - BCNF\\
+review { review_id, build_id, user_id, content, created_at } → FK to build and users - BCNF\\
+
+suggestions { id, user_id, admin_id, link, admin_comment, description, status, component_type } → FK to users/admins - BCNF\\
+
+== Check 3NF and BCNF
+
+We can see that all foreign keys point to primary keys, so no transitive dependencies can be detected here. All component subtypes are in separate tables which removes the partial dependencies from before. We also separated the user roles, and in the relations all non-key attributes fully depend on their primary key. 
+
+3NF/BCNF is satisfied.