Changes between Version 12 and Version 13 of Normalization
- Timestamp:
- 02/03/26 01:34:19 (6 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Normalization
v12 v13 4 4 5 5 R(user_id, username, password, email, 6 component_id, component_name, brand, component_price, component_type, 6 component_id, component_name, brand, component_price, component_type, img_url, 7 7 cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp, 8 8 gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length, … … 12 12 motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots, 13 13 pc_case_cooler_max_height, pc_case_gpu_max_length, 14 pc_case_storage_form_factor, num_slots 14 pc_case_storage_form_factor, num_slots, 15 15 pc_case_ps_form_factor, 16 16 pc_case_mobo_form_factor, … … 29 29 suggestion_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type) 30 30 31 Before anything else, we need to note that pc_case_storage_form_factor (composite attribute with num_slots), pc_case_ps_form_factor, pc_case_mobo_form_factor and cooler_cpu_socket are multivalued attributes.31 Before anything else, we need to note that pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor and cooler_cpu_socket are multivalued attributes. 32 32 33 33 Now we will list the functional dependencies(the primary/composite keys are on the left side). For component_id the name of the proper subclass will be shown in parenthesis for better presentation. … … 84 84 username, password, email, component_name, brand, component_price, component_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, power_supply_type, power_supply_capacity, power_supply_form_factor, motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots, pc_case_cooler_max_height, pc_case_gpu_max_length, num_slots, pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor, cooler_cpu_socket, cooler_type, cooler_height, cooler_max_tdp_supported, memory_card_num_slots, memory_card_interface, optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed, sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel, cables_length_cm, cables_type, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas, network_card_num_ports, network_card_speed, network_card_interface, build_name, build_created_at, build_description, build_total_price, build_is_approved, rating_build_value, review_content, review_created_at, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type 85 85 86 Attributes that never appear on the right side(only left) are component_id, review_id, suggestion_id. This means that they must be a part of the candidate key because nothing elsecan determine them.87 88 Let K = { component_id, review_id, suggestion_id }86 Attributes that never appear on the right side(only left) are component_id, review_id, suggestion_id. This means that they must be in every candidate key because nothing can determine them. 87 88 Lets test the minimal candidate key K = { component_id, review_id, suggestion_id } 89 89 90 90 We now compute the closure K+ … … 124 124 num_components, rating_build_value 125 125 126 If we remove the component_id - then the component attributes cant be determined. Same thing goes for review_id and suggestion_id. 126 127 After excluding multivalued attributes which we handle separatelu in 1NF, K+ contains all attributes of R, which means that K is a superkey. 127 128 128 The universal relation violates 1NF because the attributes case_storage_form_factor, case_ps_form_factor,case_mobo_form_factor, and cooler_cpu_socket represent multivalued attributes, and can contain multiple values for a single component_id. This will be resolved by decomposing them into separate relations.129 The universal relation violates 1NF because the attributes pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor, and cooler_cpu_socket represent multivalued attributes, and can contain multiple values for a single component_id. This will be resolved by decomposing them into separate relations. 129 130 130 131 == 1NF Decomposition … … 263 264 Cpu { component_id, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp } 264 265 265 Gpu { {component_id, gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length }266 Gpu { component_id, gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length } 266 267 267 268 Memory { component_id, memory_type, memory_speed, memory_capacity, memory_modules } … … 311 312 For better contextualization we will add the Admins table which is just a specialization of Users, that has admin_id which references user_id in Users. 312 313 313 Admins { admin_id } - BCNF314 315 316 317 318 319 320 314 Admins { admin_id (PK and FK to user_id(Users) } - BCNF 315 316 317 318 319 320 321
