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, admin_user_id, component_id, component_name, component_brand, component_price, component_type, component_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_wattage, 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, pc_case_storage_form_factor, case_storage_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_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved, build_component_num_components, rating_build_value, review_id, review_build_id, review_user_id, review_content, review_created_at, suggestion_id, suggestion_user_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 with case_storage_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: admin_user_id -> user_id (FK relationship)

FD3: component_id -> component_name, component_brand, component_price, component_type, component_img_url

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

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

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

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

FD8: component_id(power_supply) -> power_supply_type, power_supply_wattage, power_supply_form_factor

FD9: 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

FD10: component_id(pc_case) -> pc_case_cooler_max_height, pc_case_gpu_max_length

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

FD12: component_id(memory_card) -> memory_card_num_slots, memory_card_interface

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

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

FD15: component_id(cables) -> cables_length_cm, cables_type

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

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

FD18: build_id -> build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved

FD19: build_id, component_id -> build_component_num_components

FD20: build_id, user_id -> rating_build_value

FD21: build_id, user_id -> (represents favorite_build relationship, no additional attributes)

FD22: review_id -> review_build_id, review_user_id, review_content, review_created_at

FD23: review_build_id, review_user_id -> review_id (alternate key constraint from UNIQUE)

FD24: suggestion_id -> suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type

Left: user_id, admin_user_id, component_id, build_id, review_id, suggestion_id

Left and right: build_user_id, user_id (via various FDs), review_build_id, review_user_id

Right: username, password, email, component_name, component_brand, component_price, component_type, component_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_wattage, 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, case_storage_num_slots, pc_case_storage_form_factor, 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_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved, build_component_num_components, rating_build_value, review_build_id, review_user_id, review_content, review_created_at, suggestion_user_id, 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 user_id, admin_user_id, component_id, build_id, review_id, suggestion_id. This means that they must be in every candidate key because nothing can determine them.

Let's compute the closures of each individual left-side attribute to see what we can derive from each:

{user_id}+ : By applying FD1 we add username, password, email. So {user_id}+ = {user_id, username, password, email}. This is NOT a superkey (missing all other attributes).

{admin_user_id}+ : By applying FD2, admin_user_id → user_id. By applying FD1, user_id → username, password, email. So {admin_user_id}+ = {admin_user_id, user_id, username, password, email}. This is NOT a superkey (missing component_id, build_id, review_id, suggestion_id and their dependents).

{component_id}+ : By applying FD3-FD17 we add component_name, component_brand, component_price, component_type, component_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_wattage, 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, 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. So {component_id}+ contains all component attributes but is NOT a superkey (missing user_id, admin_user_id, build_id, review_id, suggestion_id and their dependents).

{build_id}+ : By applying FD18 we add build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved. By applying FD19 we need component_id which we don't have. By applying FD20 and FD21 we need user_id which we don't have. So {build_id}+ = {build_id, build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved}. This is NOT a superkey.

{review_id}+ : By applying FD22 we add review_build_id, review_user_id, review_content, review_created_at. We cannot derive build_id or user_id directly from the relation. So {review_id}+ = {review_id, review_build_id, review_user_id, review_content, review_created_at}. This is NOT a superkey.

{suggestion_id}+ : By applying FD24 we add suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type. We cannot derive admin_user_id directly. So {suggestion_id}+ = {suggestion_id, suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type}. This is NOT a superkey.

Since no single left-side attribute can determine all others, we must combine them. Let's test the candidate key K = {user_id, admin_user_id, component_id, review_id, suggestion_id, build_id}.

We now compute the closure K+

Start with K+ = {user_id, admin_user_id, component_id, review_id, suggestion_id, build_id}

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

By applying FD2 – we verify admin specialization (admin_user_id is FK to user_id, already in set)

By applying FD3 – FD17, we add: component_name, component_brand, component_price, component_type, component_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_wattage, 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, 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 FD18, we add: build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved

By applying FD19 (we have build_id and component_id), we add: build_component_num_components

By applying FD20 and FD21 (we have build_id and user_id), we add: rating_build_value

By applying FD22, we add: review_build_id, review_user_id, review_content, review_created_at

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

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

To verify that K = {user_id, admin_user_id, component_id, review_id, suggestion_id, build_id} is a candidate key, we test all proper subsets:

{admin_user_id, component_id, review_id, suggestion_id, build_id}+ is missing user_id which blocks FD20 and FD21 (need user_id for build_id, user_id -> rating_build_value and favorite_build).

{user_id, component_id, review_id, suggestion_id, build_id}+ is missing admin_user_id which blocks FD24 completely (need to derive suggestion_admin_id from admin_user_id).

{user_id, admin_user_id, review_id, suggestion_id, build_id}+ is missing component_id which blocks FD19 (need build_id, component_id for build_component_num_components) and all component attributes.

{user_id, admin_user_id, component_id, suggestion_id, build_id}+ is missing review_id which blocks FD22 (need review_id -> review attributes).

{user_id, admin_user_id, component_id, review_id, build_id}+ is missing suggestion_id which blocks FD24 (need suggestion_id -> suggestion attributes).

{user_id, admin_user_id, component_id, review_id, suggestion_id}+ is missing build_id which blocks FD18, FD19, FD20, FD21 (need build_id -> build attributes and build_id for various composite FDs).

Therefore, K = {user_id, admin_user_id, component_id, review_id, suggestion_id, build_id} is the minimal candidate key (no proper subset is a superkey).

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.

1NF Decomposition

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

component_id, pc_case_storage_form_factor -> case_storage_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:

FD1: user_id is part of the key, but these attributes such as username, password, email depend only on user_id.

FD2: admin_user_id is part of the key, but user_id depends only on admin_user_id.

FD3-FD17: component_id is part of the key, but these attributes such as component_name, component_brand, component_price etc. depend only on component_id.

FD18: build_id is part of the key, but these attributes such as build_user_id, build_name etc. depend only on build_id.

FD19: (build_id, component_id) is part of the key, but build_component_num_components depends on (build_id, component_id).

FD20: (build_id, user_id) is part of the key, but rating_build_value depends on (build_id, user_id).

FD21: (build_id, user_id) is part of the key, but represents favorite_build (no attributes).

FD22: review_id is part of the key, but these attributes such as review_build_id, review_user_id, review_content, review_created_at depend only on review_id.

FD24: suggestion_id is part of the key, but these attributes such as suggestion_user_id, suggestion_admin_id etc. depend only on suggestion_id.

We will decompose by creating new relations from R following FD1, FD2 to FD24 where the attributes will depend only on the determinant attribute(s), which will become the primary key:

user_id -> username, password, email

admin_user_id -> user_id

component_id -> component_name, component_brand, component_price, component_type, component_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_wattage, 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_cooler_max_height, pc_case_gpu_max_length

build_id -> build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved

build_id, component_id -> build_component_num_components

build_id, user_id -> rating_build_value

build_id, user_id -> (represents favorite_build)

review_id -> review_build_id, review_user_id, review_content, review_created_at

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

Primary key in all of these is the attribute(s) on the left side.

2NF Decomposition

All these partial dependencies are addressed in 2NF. We will solve these partial dependencies by creating new relations where each determinant becomes a primary key:

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

R2 { admin_user_id, user_id } where PK = admin_user_id, FK = user_id -> R1.user_id - BCNF

R3 { component_id, component_name, component_brand, component_price, component_type, component_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_wattage, 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_cooler_max_height, pc_case_gpu_max_length } where PK = component_id - BCNF

R11 { build_id, build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved } where PK = build_id - BCNF

R12 { build_id, component_id, build_component_num_components } where PK = (build_id, component_id) - BCNF

R13 { build_id, user_id, rating_build_value } where PK = (build_id, user_id) - BCNF

R14 { build_id, user_id } where PK = (build_id, user_id) - BCNF

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

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

R17 { 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

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

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

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

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

R22 { review_id, review_build_id, review_user_id, review_content, review_created_at } where PK = review_id, Alt Key = (review_build_id, review_user_id) - BCNF

R23 { suggestion_id, suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type } where PK = suggestion_id - BCNF

The remainder of R { component_id, review_id, suggestion_id, build_id, user_id, admin_user_id } where PK = K (component_id, review_id, suggestion_id, build_id, user_id, admin_user_id)

2NF is satisfied for all relations. All partial dependencies have been eliminated. No transitive dependencies can exist in relations with only key attributes or attributes derived entirely from the key.

All relations are already in BCNF since every determinant of every functional dependency is a candidate key of its respective relation.

Final BCNF relations

Users { user_id, username, password, email }

Admins { admin_user_id, user_id (FK to Users.user_id) }

Components { component_id, component_name, component_brand, component_price, component_type, component_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_wattage, 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_cooler_max_height, pc_case_gpu_max_length }

Case_Storage_Form_Factors { component_id, pc_case_storage_form_factor, case_storage_num_slots }

Case_Ps_Form_Factors { component_id, pc_case_ps_form_factor }

Case_Mobo_Form_Factors { component_id, pc_case_mobo_form_factor }

Cooler { component_id, cooler_type, cooler_height, cooler_max_tdp_supported }

Cooler_Cpu_Sockets { component_id, cooler_cpu_socket }

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 }

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

Build_Component { build_id, component_id, build_component_num_components }

Rating_Build { build_id, user_id, rating_build_value }

Favorite_Build { build_id, user_id }

Review { review_id, review_build_id, review_user_id, review_content, review_created_at }

Suggestions { suggestion_id, suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type }