Normalization

Initial de-normalized relation and functional dependencies

Attributes with identical names originating from different relations were renamed using entity-based prefixes in order to avoid duplicate attribute names in the universal relation.

R(user_id, username, password, email, component_id, component_name, brand, component_price, component_type, 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_coooler_max_height, pc_case_gpu_max_length, pc_case_storage_form_factor, num_slots pc_case_ps_form_factor, pc_case_mobo_form_factor, cooler_type, cooler_height, cooler_max_tdp_supported, cooler_cpu_socket, 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_id, build_name, build_created_at, build_description, build_total_price, build_is_approved, num_components, rating_build_value, review_id, review_content, review_created_at, suggestion_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type)

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.

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.

FD1: user_id -> username, password, email

FD2: component_id -> component_name, brand, component_price, component_type, img_url

FD3: component_id(cpu) -> cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp

FD4: component_id(gpu) -> gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length

FD5: component_id(memory) -> memory_type, memory_speed, memory_capacity, memory_modules

FD6: component_id(storage) -> storage_type, storage_capacity, storage_form_factor

FD7: component_id (power_supply) -> power_supply_type, power_supply_capacity, power_supply_form_factor

FD8: component_id(motherboard) -> motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots

FD9: component_id(pc_case) -> pc_case_coooler_max_height, pc_case_gpu_max_length

FD10: component_id(cooler) -> cooler_type, cooler_height, cooler_max_tdp_supported

FD11: component_id(memory_card) -> memory_card_num_slots, memory_card_interface

FD12: component_id(optical_drive) -> optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed

FD13: component_id(sound_card) -> sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel

FD14: component_id(cables) -> cables_length_cm, cables_type

FD15: component_id(network_adapter) -> network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas

FD16: component_id(network_card) -> network_card_num_ports, network_card_speed, network_card_interface

FD17: build_id -> user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved

FD18: build_id, component_id -> num_components

FD19: build_id, user_id -> /

FD20: build_id, user_id -> rating_build_value

FD21: review_id -> build_id, user_id, review_content, review_created_at

FD22: suggestion_id -> user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type

Left: user_id, component_id, build_id, review_id, suggestion_id

Left and right: build_id, user_id

Right: 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_coooler_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

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 else can determine them.

Let K = { component_id, review_id, suggestion_id }

We now compute the closure K+

Start with K+ = { component_id, review_id, suggestion_id }

By applying FD2 – FD16, we add: component_name, brand, component_price, component_type, 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_coooler_max_height, pc_case_gpu_max_length, 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

By applying FD21, we add: build_id, user_id, review_content, review_created_at

By applying FD17, we add: build_name, build_created_at, build_description, build_total_price, build_is_approved

By applying FD1, we add: username, password, email

By applying FD22, we add: suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type

By applying FD18 and FD20, we add: num_components, rating_build_value

After excluding multivalued attributes which we handle separatelu in 1NF, K+ contains all attributes of R, which means that K is a superkey.

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.

1NF Decomposition

To achieve 1NF, we decompose the multivalued attributes from R into:

component_id , pc_case_storage_form_factor -> num_slots

component_id, pc_case_ps_form_factor -> no non-key attributes

component_id, pc_case_mobo_form_factor -> no non-key attributes

component_id, cooler_cpu_socket -> no non-key attributes

1NF is satisfied now. 2NF is not satisfied because of partial dependencies. We can see this through the functional dependencies:

FD2-FD16: component_id is part of the key, but the these attributes such as component_name, brand, component_price etc. depend only on component_id.

We will decompose by creating new relations from R following FD2 to FD16 where the attributes will depend only on component_id:

component_id -> component_id, component_name, brand, component_price, component_type, img_url

component_id -> cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp

component_id -> gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length

component_id -> memory_type, memory_speed, memory_capacity, memory_modules

component_id -> storage_type, storage_capacity, storage_form_factor

component_id -> power_supply_type, power_supply_capacity, power_supply_form_factor

component_id -> motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots

component_id -> pc_case_coooler_max_height, pc_case_gpu_max_length

component_id -> cooler_type, cooler_height, cooler_max_tdp_supported, cooler_cpu_socket

component_id -> memory_card_num_slots, memory_card_interface

component_id -> optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed

component_id -> sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel

component_id -> cables_length_cm, cables_type

component_id -> network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas

component_id -> network_card_num_ports, network_card_speed, network_card_interface

Primary key in all of these is component_id.

FD21: review_id is part of the key, but these attributes build_id, user_id, review_content, review_created_at depend only on review_id.

We will solve this partial dependency by creating a new relation:

review_id -> build_id, user_id, review_content, review_created_at

Primary key here is review_id.

FD22: suggestion_id is part of the key, but user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type depend only on suggestion_id.

We will solve this partial dependency by creating a new relation:

suggestion_id -> user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type

We get the following relations:

R1 { review_id, build_id, user_id, review_content, review_created_at } where PK = review_id - BCNF

R2 { suggestion_id, user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type } where PK = suggestion_id - BCNF

R3 { component_id, component_name, brand, component_price, component_type, img_url } where PK = component_id - BCNF

R4 { component_id, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp } where PK = component_id - BCNF

R5 { component_id, gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length } where PK = component_id - BCNF

R6 { component_id, memory_type, memory_speed, memory_capacity, memory_modules } where PK = component_id - BCNF

R7 { component_id, storage_type, storage_capacity, storage_form_factor } where PK = component_id - BCNF

R8 { component_id, power_supply_type, power_supply_capacity, power_supply_form_factor } where PK = component_id - BCNF

R9 { component_id, motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots } where PK = component_id - BCNF

R10 { component_id, pc_case_coooler_max_height, pc_case_gpu_max_length } where PK = component_id - BCNF

R11 { component_id, cooler_type, cooler_height, cooler_max_tdp_supported } where PK = component_id - BCNF

R12 { component_id, memory_card_num_slots, memory_card_interface } where PK = component_id - BCNF

R13 { component_id, optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed } where PK = component_id - BCNF

R14 { component_id, sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel } where PK = component_id - BCNF

R15 { component_id, cables_length_cm, cables_type } where PK = component_id - BCNF

R16 { component_id, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas } where PK = component_id - BCNF

R17 { component_id, network_card_num_ports, network_card_speed, network_card_interface } where PK = component_id - BCNF

R18 { component_id, pc_case_storage_form_factor , num_slots } where PK = component_id and pc_case_storage_form_factor - BCNF

R19 { component_id, pc_case_ps_form_factor } where PK = component_id and pc_case_ps_form_factor - BCNF

R20 { component_id, pc_case_mobo_form_factor } where PK = component_id and pc_case_mobo_form_factor - BCNF

R21 { component_id, cooler_cpu_socket } where PK = component_id and cooler_cpu_socket - BCNF

The remainder of R { component_id, review_id, suggestion_id, build_id, user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved, num_components, rating_build_value, username, password, email } where PK = K (component_id, review_id, suggestion_id)

2NF is satisfied but the remaining of R has transitive dependencies which violate 3NF. When looking at FD17: build_id -> build_name, build_created_at, build_description, build_total_price, build_is_approved, here build_id is not part of the candidate key K, but it determines build_name and other attributes. The same thing goes for user_id -> username, password, email when looking at FD1. We will handle these transitive dependencies by creating separate tables.

3NF decomposition

R22 { user_id, username, password, email } where PK = user_id - BCNF

R23 { build_id, user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved } where PK = build_id – BCNF

Leftover relation R { component_id, review_id, suggestion_id, build_id, user_id, num_components, rating_build_value }

From FD18 (build_id, component_id) → num_components we see that build_id and component_id is not a superkey of leftover relation R, this violates BCNF. Same thing goes for FD19 (build_id, user_id) -> / and FD20 (build_id, user_id) → rating_build_value, build_id and user_id is not a superkey of the leftover relation. We will decompose by creating new relations

R24 { build_id , component_id, num_components } where PK = build_id, component_id - BCNF

R25 { build_id, user_id, rating_build_value } where PK = build_id, user_id - BCNF

R26 { build_id, user_id } where PK = build_id, user_id – BCNF

Final BCNF relations

Users { user_id, username, password, email }

Components { component_id, component_name, brand, component_price, component_type, img_url }

Cpu { component_id, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp }

Gpu { { component_id, gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length }

Memory { component_id, memory_type, memory_speed, memory_capacity, memory_modules }

Storage { component_id, storage_type, storage_capacity, storage_form_factor }

Power_Supply { component_id, power_supply_type, power_supply_capacity, power_supply_form_factor }

Motherboard { component_id, motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots }

Pc_Case { component_id, pc_case_coooler_max_height, pc_case_gpu_max_length }

Cooler { component_id, cooler_type, cooler_height, cooler_max_tdp_supported }

Memory_Card { component_id, memory_card_num_slots, memory_card_interface }

Optical_Drive { component_id, optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed }

Sound_Card { component_id, sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel }

Cables { component_id, cables_length_cm, cables_type }

Network_Adapter { component_id, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas }

Network_Card { component_id, network_card_num_ports, network_card_speed, network_card_interface }

Pc_Case_Storage_Form_Factor { component_id, pc_case_storage_form_factor , num_slots }

Pc_Case_Ps_Form_Factor { component_id, pc_case_ps_form_factor }

Pc_Case_Mobo_Form_Factor { component_id, pc_case_mobo_form_factor }

Cooler_Cpu_Socket { component_id, cooler_cpu_socket }

Build { build_id, user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved }

Build_Component { build_id, component_id, num_components }

Rating_Build { build_id, user_id, rating_build_value }

Favorite_Build { build_id, user_id }

Review { review_id, build_id, user_id, review_content, review_created_at }

Suggestion { suggestion_id, user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type }

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.

Admins { admin_id } - BCNF