Normalization

1. Definition of the Universal Relation

We start from a single universal relation containing all attributes from the domain.

Universal_Relation( 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, pc_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 )

2. Functional Dependencies

We identify all functional dependencies that hold in the universal relation:

F = {

FD1: user_id → username, password, email

FD2: admin_user_id → ∅

FD3: component_id → component_name, component_brand, component_price, component_type, component_img_url

FD4: component_id → cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp

FD5: component_id → gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length

FD6: component_id → memory_type, memory_speed, memory_capacity, memory_modules

FD7: component_id → storage_type, storage_capacity, storage_form_factor

FD8: component_id → power_supply_type, power_supply_wattage, power_supply_form_factor

FD9: component_id → 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_cooler_max_height, pc_case_gpu_max_length

FD11: component_id → cooler_type, cooler_height, cooler_max_tdp_supported

FD12: component_id → memory_card_num_slots, memory_card_interface

FD13: component_id → optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed

FD14: component_id → sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel

FD15: component_id → cables_length_cm, cables_type

FD16: component_id → network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas

FD17: component_id → 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)

FD22: review_id → review_build_id, review_user_id, review_content, review_created_at

FD23: suggestion_id → suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type

FD24: (component_id, pc_case_storage_form_factor) → pc_case_storage_num_slots

FD25: (component_id, pc_case_mobo_form_factor) → ∅

FD26: (component_id, pc_case_ps_form_factor) → ∅

FD27: (component_id, cooler_cpu_socket) → ∅

}

3. Candidate Keys and Primary Key

3.1 Determination of Candidate Keys

To find a candidate key, we must find a minimal set of attributes whose closure contains all attributes of the relation.

3.2 Attribute Classification (Left / Right Side)

Attribute	Left Side	Right Side	Classification
user_id	✓ (FD1)	✗	Left only
username	✗	✓ (FD1)	Right only
password	✗	✓ (FD1)	Right only
email	✗	✓ (FD1)	Right only
admin_user_id	✓ (FD2)	✗	Left only
component_id	✓ (FD3-FD17, FD24-FD27)	✗	Left only
component_name	✗	✓ (FD3)	Right only
component_brand	✗	✓ (FD3)	Right only
component_price	✗	✓ (FD3)	Right only
component_type	✗	✓ (FD3)	Right only
component_img_url	✗	✓ (FD3)	Right only
[CPU attributes]	✗	✓ (FD4)	Right only
[GPU attributes]	✗	✓ (FD5)	Right only
[Memory attributes]	✗	✓ (FD6)	Right only
[Storage attributes]	✗	✓ (FD7)	Right only
[Power Supply attributes]	✗	✓ (FD8)	Right only
[Motherboard attributes]	✗	✓ (FD9)	Right only
pc_case_cooler_max_height	✗	✓ (FD10)	Right only
pc_case_gpu_max_length	✗	✓ (FD10)	Right only
pc_case_storage_form_factor	✓ (FD24)	✗	Left only
pc_case_storage_num_slots	✗	✓ (FD24)	Right only
pc_case_ps_form_factor	✓ (FD26)	✗	Left only
pc_case_mobo_form_factor	✓ (FD25)	✗	Left only
cooler_type	✗	✓ (FD11)	Right only
cooler_height	✗	✓ (FD11)	Right only
cooler_max_tdp_supported	✗	✓ (FD11)	Right only
cooler_cpu_socket	✓ (FD27)	✗	Left only
[Memory Card attributes]	✗	✓ (FD12)	Right only
[Optical Drive attributes]	✗	✓ (FD13)	Right only
[Sound Card attributes]	✗	✓ (FD14)	Right only
[Cables attributes]	✗	✓ (FD15)	Right only
[Network Adapter attributes]	✗	✓ (FD16)	Right only
[Network Card attributes]	✗	✓ (FD17)	Right only
build_id	✓ (FD18-FD21)	✗	Left only
build_user_id	✗	✓ (FD18)	Right only
build_name	✗	✓ (FD18)	Right only
build_created_at	✗	✓ (FD18)	Right only
build_description	✗	✓ (FD18)	Right only
build_total_price	✗	✓ (FD18)	Right only
build_is_approved	✗	✓ (FD18)	Right only
build_component_num_components	✗	✓ (FD19)	Right only
rating_build_value	✗	✓ (FD20)	Right only
review_id	✓ (FD22)	✗	Left only
review_build_id	✗	✓ (FD22)	Right only
review_user_id	✗	✓ (FD22)	Right only
review_content	✗	✓ (FD22)	Right only
review_created_at	✗	✓ (FD22)	Right only
suggestion_id	✓ (FD23)	✗	Left only
suggestion_user_id	✗	✓ (FD23)	Right only
suggestion_admin_id	✗	✓ (FD23)	Right only
suggestion_link	✗	✓ (FD23)	Right only
suggestion_admin_comment	✗	✓ (FD23)	Right only
suggestion_description	✗	✓ (FD23)	Right only
suggestion_status	✗	✓ (FD23)	Right only
suggestion_component_type	✗	✓ (FD23)	Right only

3.3 Attributes That Appear ONLY on the Left Side

The following attributes appear only on the left side of functional dependencies and never on the right side:

• user_id – must be part of every candidate key
• component_id – must be part of every candidate key
• build_id – must be part of every candidate key
• review_id – must be part of every candidate key
• suggestion_id – must be part of every candidate key
• pc_case_storage_form_factor – must be part of every candidate key
• pc_case_ps_form_factor – must be part of every candidate key
• pc_case_mobo_form_factor – must be part of every candidate key
• cooler_cpu_socket – must be part of every candidate key
• admin_user_id – must be part of every candidate key

3.4 Closure Computation

Step 1: We start with {user_id, component_id, build_id, review_id, suggestion_id, pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor, cooler_cpu_socket, admin_user_id} and compute the closure:

{user_id, component_id, build_id, review_id, suggestion_id, pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor, cooler_cpu_socket, admin_user_id}⁺:

• From FD1 (user_id → username, password, email): We obtain username, password, email
• From FD2 (admin_user_id → ∅): No new attributes
• From FD3–FD17 (component_id → ...): We obtain 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
• From FD18 (build_id → ...): We obtain build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved
• From FD19 ((build_id, component_id) → build_component_num_components): We obtain build_component_num_components (both build_id and component_id already in closure)
• From FD20 ((build_id, user_id) → rating_build_value): We obtain rating_build_value (both build_id and user_id already in closure)
• From FD21 ((build_id, user_id) → ∅): No new attributes
• From FD22 (review_id → ...): We obtain review_build_id, review_user_id, review_content, review_created_at
• From FD23 (suggestion_id → ...): We obtain suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
• From FD24 ((component_id, pc_case_storage_form_factor) → pc_case_storage_num_slots): We obtain pc_case_storage_num_slots (both attributes already in closure)
• From FD25–FD27 (composite FDs with empty RHS): No new attributes

Closure = Universal_Relation ✓ (all attributes are present)

3.5 Minimality Check

Testing proper subsets of K = {user_id, component_id, build_id, review_id, suggestion_id, pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor, cooler_cpu_socket, admin_user_id}:

Subset	Closure Equals Universal_Relation?	Justification
K − {user_id}	✗ NO	Cannot derive username, password, email (FD1 requires user_id)
K − {component_id}	✗ NO	Cannot derive component attributes (FD3–FD17 require component_id)
K − {build_id}	✗ NO	Cannot derive build attributes (FD18 requires build_id)
K − {review_id}	✗ NO	Cannot derive review attributes (FD22 requires review_id)
K − {suggestion_id}	✗ NO	Cannot derive suggestion attributes (FD23 requires suggestion_id)
K − {pc_case_storage_form_factor}	✗ NO	FD24 requires pc_case_storage_form_factor
K − {pc_case_ps_form_factor}	✗ NO	FD26 requires pc_case_ps_form_factor
K − {pc_case_mobo_form_factor}	✗ NO	FD25 requires pc_case_mobo_form_factor
K − {cooler_cpu_socket}	✗ NO	FD27 requires cooler_cpu_socket
K − {admin_user_id}	✗ NO	admin_user_id is a left-only attribute and must be in every candidate key

Conclusion: K = {user_id, component_id, build_id, review_id, suggestion_id, pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor, cooler_cpu_socket, admin_user_id} is minimal and is the only candidate key.

3.6 Choice of Primary Key

Chosen Primary Key: {user_id, component_id, build_id, review_id, suggestion_id, pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor, cooler_cpu_socket, admin_user_id}

Justification: This is the primary key (minimal candidate key) because only with these attributes can we derive all remaining attributes from the universal relation through the closure of functional dependencies.

---

4. 1NF Decomposition

4.1 1NF Violation Identification

The 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. Each component_id can have multiple values for each of these attributes.

4.2 1NF Decomposition

Relation R1: Case_Storage_Form_Factors

Case_Storage_Form_Factors(component_id, pc_case_storage_form_factor, pc_case_storage_num_slots) PK: (component_id, pc_case_storage_form_factor)

Attributes Included: component_id, pc_case_storage_form_factor, pc_case_storage_num_slots

Functional Dependency Enforced: FD24: (component_id, pc_case_storage_form_factor) → pc_case_storage_num_slots

Lossless Join Check:

• This relation is decomposed directly from the universal relation by extracting the multivalued attribute group pc_case_storage_form_factor
• The composite primary key (component_id, pc_case_storage_form_factor) uniquely identifies each tuple
• When joined back with the main relation via component_id, no spurious tuples are introduced
• The decomposition captures the exact relationship defined by FD24
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD24: (component_id, pc_case_storage_form_factor) → pc_case_storage_num_slots
  • Determinant: (component_id, pc_case_storage_form_factor)
  • Dependent: pc_case_storage_num_slots
  • All attributes of FD24 appear together in this relation ✓
• The functional dependency is directly enforceable within this schema
• Result: PRESERVED ✓

---

Relation R2: Case_Ps_Form_Factors

Case_Ps_Form_Factors(component_id, pc_case_ps_form_factor) PK: (component_id, pc_case_ps_form_factor)

Attributes Included: component_id, pc_case_ps_form_factor

Functional Dependency Enforced: FD26: (component_id, pc_case_ps_form_factor) → ∅

Lossless Join Check:

• Decomposed directly from the multivalued attribute pc_case_ps_form_factor
• The primary key (component_id, pc_case_ps_form_factor) uniquely identifies the relationship
• Joining with the main relation on component_id preserves all original tuples
• No information loss occurs during decomposition or reconstruction
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD26: (component_id, pc_case_ps_form_factor) → ∅
  • This is a relationship-only functional dependency with empty RHS
  • The determinant (component_id, pc_case_ps_form_factor) is present as the primary key
  • All attributes involved in FD26 are contained in this relation ✓
• Result: PRESERVED ✓

---

Relation R3: Case_Mobo_Form_Factors

Case_Mobo_Form_Factors(component_id, pc_case_mobo_form_factor) PK: (component_id, pc_case_mobo_form_factor)

Attributes Included: component_id, pc_case_mobo_form_factor

Functional Dependency Enforced: FD25: (component_id, pc_case_mobo_form_factor) → ∅

Lossless Join Check:

• Decomposed from the multivalued attribute pc_case_mobo_form_factor
• The composite key fully represents the relationship between component and motherboard form factor
• Natural join on component_id with the main relation produces no spurious tuples
• Complete reconstruction of the original relationship is possible
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD25: (component_id, pc_case_mobo_form_factor) → ∅
  • The relationship-only FD is preserved through the composite primary key
  • All attributes of FD25 present in this schema ✓
• Result: PRESERVED ✓

---

Relation R4: Cooler_Cpu_Sockets

Cooler_Cpu_Sockets(component_id, cooler_cpu_socket) PK: (component_id, cooler_cpu_socket)

Attributes Included: component_id, cooler_cpu_socket

Functional Dependency Enforced: FD27: (component_id, cooler_cpu_socket) → ∅

Lossless Join Check:

• Decomposed directly from the multivalued attribute cooler_cpu_socket
• The primary key captures the complete relationship
• Rejoining via component_id preserves all original tuples without duplication
• No information loss in decomposition or reconstruction
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD27: (component_id, cooler_cpu_socket) → ∅
  • The relationship-only FD is preserved by the composite key structure
  • All attributes involved in FD27 are present ✓
• Result: PRESERVED ✓

---

Relation R5: Universal_Relation_1NF

Universal_Relation_1NF(

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_num_slots, 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_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

) PK: (user_id, component_id, build_id, review_id, suggestion_id, admin_user_id)

Attributes Included: All original attributes except the multivalued ones (pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor, cooler_cpu_socket)

Functional Dependencies Enforced: FD1–FD24 (all except FD25–FD27)

Lossless Join Check:

• The four decomposed multivalued relations can be joined back to this relation via component_id
• Each tuple in this main relation corresponds to a set of tuples in each multivalued relation
• The natural join R5 ⋈ R1 ⋈ R2 ⋈ R3 ⋈ R4 reconstructs the original universal relation without spurious tuples
• All join paths are on component_id, a common attribute present in all relations
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD1: user_id → username, password, email — All attributes present ✓
• FD2: admin_user_id → ∅ — Determinant present ✓
• FD3–FD23: component_id or composite keys → dependent attributes — All attributes present ✓
• FD24: (component_id, pc_case_storage_form_factor) → pc_case_storage_num_slots — pc_case_storage_num_slots retained ✓
• All functional dependencies relevant to this relation are preserved
• Result: PRESERVED ✓

---

5. 2NF Decomposition

5.1 2NF Violation Analysis

The Universal_Relation_1NF (R5) violates 2NF due to partial dependencies.

Primary Key: (user_id, component_id, build_id, review_id, suggestion_id, admin_user_id)

A partial dependency exists when a non-key attribute depends on only a proper subset of the composite primary key rather than the entire key.

Identified Partial Dependencies:

FD	Determinant	Proper Subset?	Non-key Attributes Dependent	2NF Violation?
FD1	user_id	✓ YES	username, password, email	✓ YES
FD2	admin_user_id	✓ YES	(specialization)	✓ YES
FD3–FD17	component_id	✓ YES	component attributes	✓ YES
FD18	build_id	✓ YES	build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved	✓ YES
FD22	review_id	✓ YES	review_build_id, review_user_id, review_content, review_created_at	✓ YES
FD23	suggestion_id	✓ YES	suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type	✓ YES
FD19	(build_id, component_id)	✓ YES	build_component_num_components	✓ YES
FD20	(build_id, user_id)	✓ YES	rating_build_value	✓ YES
FD21	(build_id, user_id)	✓ YES	(relationship only)	✓ YES

5.2 2NF Decomposition

We eliminate partial dependencies by creating new relations where each partial dependency determinant becomes a primary key.

---

2NF Relation 1: Users

Users(user_id, username, password, email) PK: user_id

Derived From: FD1 (user_id → username, password, email)

Functional Dependency Enforced: FD1

Lossless Join Check:

• Before decomposition, user_id was part of the composite key in Universal_Relation_1NF
• Now user_id becomes the sole primary key in Users
• Intersection of Users and Universal_Relation_1NF: I = {user_id}
• FD1 guarantees: user_id → username, password, email
• The join condition is on user_id, which is a candidate key for Users
• Natural join Users ⋈ Universal_Relation_1NF on user_id produces no spurious tuples
• All original tuples can be reconstructed: ✓
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD1: user_id → username, password, email
  • Determinant (user_id) and all dependent attributes (username, password, email) are present in Users ✓
  • The FD is directly enforceable within this relation
• No other FD involves only user_id and its dependents in Universal_Relation_1NF
• Result: PRESERVED ✓

---

2NF Relation 2: Admins

Admins(admin_user_id) PK: admin_user_id FK: admin_user_id → Users.user_id

Derived From: FD2 (admin_user_id → ∅)

Functional Dependency Enforced: FD2

Lossless Join Check:

• admin_user_id was part of the composite key in Universal_Relation_1NF
• Now admin_user_id becomes the sole primary key in Admins
• Intersection: I = {admin_user_id}
• FD2 is a relationship-only dependency with empty dependent set
• The join Admins ⋈ Universal_Relation_1NF on admin_user_id reconstructs the relationship
• No spurious tuples introduced
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD2: admin_user_id → ∅
  • The determinant (admin_user_id) is the primary key ✓
  • This relationship-only FD is preserved through the key structure
• Result: PRESERVED ✓

---

2NF Relation 3: Components

Components(component_id, component_name, component_brand, component_price, component_type, component_img_url) PK: component_id

Derived From: FD3 (component_id → component_name, component_brand, component_price, component_type, component_img_url)

Functional Dependency Enforced: FD3

Lossless Join Check:

• component_id was part of the composite key in Universal_Relation_1NF
• Now component_id becomes the sole primary key in Components
• Intersection: I = {component_id}
• FD3 guarantees: component_id uniquely determines all dependent attributes
• Join Components ⋈ Universal_Relation_1NF on component_id is lossless
• No spurious tuples introduced
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD3: component_id → component_name, component_brand, component_price, component_type, component_img_url
  • All attributes of FD3 present in Components ✓
  • The FD is directly enforceable within this relation
• Result: PRESERVED ✓

---

2NF Relation 4: Cpu

Cpu(component_id, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp) PK: component_id FK: component_id → Components.component_id

Derived From: FD4 (component_id → cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp)

Functional Dependency Enforced: FD4

Lossless Join Check:

• component_id is a proper subset of the original composite key
• component_id becomes the primary key in Cpu
• Intersection: I = {component_id}
• FD4 ensures all non-key attributes are uniquely determined by component_id
• Join with Universal_Relation_1NF on component_id is lossless
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD4: component_id → cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp
  • All attributes present in Cpu ✓
  • Directly enforceable
• Result: PRESERVED ✓

---

2NF Relation 5: Gpu

Gpu(component_id, gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length) PK: component_id FK: component_id → Components.component_id

Derived From: FD5

Lossless Join Check: LOSSLESS ✓ (same reasoning as Cpu)

Dependency Preservation Check: FD5 directly enforceable ✓ PRESERVED ✓

---

2NF Relation 6: Memory

Memory(component_id, memory_type, memory_speed, memory_capacity, memory_modules) PK: component_id FK: component_id → Components.component_id

Derived From: FD6

Lossless Join Check: LOSSLESS ✓

Dependency Preservation Check: PRESERVED ✓

---

2NF Relation 7: Storage

Storage(component_id, storage_type, storage_capacity, storage_form_factor) PK: component_id FK: component_id → Components.component_id

Derived From: FD7

Lossless Join Check: LOSSLESS ✓

Dependency Preservation Check: PRESERVED ✓

---

2NF Relation 8: Power_Supply

Power_Supply(component_id, power_supply_type, power_supply_wattage, power_supply_form_factor) PK: component_id FK: component_id → Components.component_id

Derived From: FD8

Lossless Join Check: LOSSLESS ✓

Dependency Preservation Check: PRESERVED ✓

---

2NF Relation 9: Motherboard

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) PK: component_id FK: component_id → Components.component_id

Derived From: FD9

Lossless Join Check: LOSSLESS ✓

Dependency Preservation Check: PRESERVED ✓

---

2NF Relation 10: Pc_Case

Pc_Case(component_id, pc_case_cooler_max_height, pc_case_gpu_max_length) PK: component_id FK: component_id → Components.component_id

Derived From: FD10

Lossless Join Check: LOSSLESS ✓

Dependency Preservation Check: PRESERVED ✓

---

2NF Relation 11: Cooler

Cooler(component_id, cooler_type, cooler_height, cooler_max_tdp_supported) PK: component_id FK: component_id → Components.component_id

Derived From: FD11

Lossless Join Check: LOSSLESS ✓

Dependency Preservation Check: PRESERVED ✓

---

2NF Relation 12: Memory_Card

Memory_Card(component_id, memory_card_num_slots, memory_card_interface) PK: component_id FK: component_id → Components.component_id

Derived From: FD12

Lossless Join Check: LOSSLESS ✓

Dependency Preservation Check: PRESERVED ✓

---

2NF Relation 13: Optical_Drive

Optical_Drive(component_id, optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed) PK: component_id FK: component_id → Components.component_id

Derived From: FD13

Lossless Join Check: LOSSLESS ✓

Dependency Preservation Check: PRESERVED ✓

---

2NF Relation 14: Sound_Card

Sound_Card(component_id, sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel) PK: component_id FK: component_id → Components.component_id

Derived From: FD14

Lossless Join Check: LOSSLESS ✓

Dependency Preservation Check: PRESERVED ✓

---

2NF Relation 15: Cables

Cables(component_id, cables_length_cm, cables_type) PK: component_id FK: component_id → Components.component_id

Derived From: FD15

Lossless Join Check: LOSSLESS ✓

Dependency Preservation Check: PRESERVED ✓

---

2NF Relation 16: Network_Adapter

Network_Adapter(component_id, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas) PK: component_id FK: component_id → Components.component_id

Derived From: FD16

Lossless Join Check: LOSSLESS ✓

Dependency Preservation Check: PRESERVED ✓

---

2NF Relation 17: Network_Card

Network_Card(component_id, network_card_num_ports, network_card_speed, network_card_interface) PK: component_id FK: component_id → Components.component_id

Derived From: FD17

Lossless Join Check: LOSSLESS ✓

Dependency Preservation Check: PRESERVED ✓

---

2NF Relation 18: Build

Build(build_id, build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved) PK: build_id FK: build_user_id → Users.user_id

Derived From: FD18 (build_id → build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved)

Functional Dependency Enforced: FD18

Lossless Join Check:

• build_id was part of the composite key in Universal_Relation_1NF
• Now build_id becomes the sole primary key in Build
• Intersection: I = {build_id}
• FD18 guarantees all non-key attributes are uniquely determined by build_id
• Join Build ⋈ Universal_Relation_1NF on build_id is lossless
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD18: build_id → build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved
  • All attributes present in Build ✓
  • Directly enforceable
• Result: PRESERVED ✓

---

2NF Relation 19: Build_Component

Build_Component(build_id, component_id, build_component_num_components) PK: (build_id, component_id) FK: build_id → Build.build_id FK: component_id → Components.component_id

Derived From: FD19 ((build_id, component_id) → build_component_num_components)

Functional Dependency Enforced: FD19

Lossless Join Check:

• (build_id, component_id) is NOT a proper subset; it equals the original combination for this FD
• The composite key becomes the primary key of Build_Component
• Intersection: I = {build_id, component_id}
• FD19 guarantees the composite key determines the non-key attribute
• Join with Universal_Relation_1NF on (build_id, component_id) is lossless
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD19: (build_id, component_id) → build_component_num_components
  • All attributes present ✓
  • Directly enforceable
• Result: PRESERVED ✓

---

2NF Relation 20: Rating_Build

Rating_Build(build_id, user_id, rating_build_value) PK: (build_id, user_id) FK: build_id → Build.build_id FK: user_id → Users.user_id

Derived From: FD20 ((build_id, user_id) → rating_build_value)

Functional Dependency Enforced: FD20

Lossless Join Check:

• (build_id, user_id) is NOT a proper subset of the original composite key
• The composite key becomes the primary key of Rating_Build
• Intersection: I = {build_id, user_id}
• FD20 guarantees the composite key determines the non-key attribute
• Join with Universal_Relation_1NF on (build_id, user_id) is lossless
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD20: (build_id, user_id) → rating_build_value
  • All attributes present ✓
  • Directly enforceable
• Result: PRESERVED ✓

---

2NF Relation 21: Favorite_Build

Favorite_Build(build_id, user_id) PK: (build_id, user_id) FK: build_id → Build.build_id FK: user_id → Users.user_id

Derived From: FD21 ((build_id, user_id) → ∅)

Functional Dependency Enforced: FD21

Lossless Join Check:

• (build_id, user_id) forms the complete schema of Favorite_Build
• Intersection: I = {build_id, user_id}
• The composite key captures the entire relationship
• Join with Universal_Relation_1NF on (build_id, user_id) is lossless
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD21: (build_id, user_id) → ∅
  • The relationship-only FD is preserved through the composite key structure ✓
  • Determinant present as primary key
• Result: PRESERVED ✓

---

2NF Relation 22: Review

Review(review_id, review_build_id, review_user_id, review_content, review_created_at) PK: review_id Alt Key: (review_build_id, review_user_id) FK: review_build_id → Build.build_id FK: review_user_id → Users.user_id

Derived From: FD22 (review_id → review_build_id, review_user_id, review_content, review_created_at)

Functional Dependency Enforced: FD22

Lossless Join Check:

• review_id was part of the composite key in Universal_Relation_1NF
• Now review_id becomes the sole primary key in Review
• Intersection: I = {review_id}
• FD22 guarantees review_id uniquely determines all non-key attributes
• Join with Universal_Relation_1NF on review_id is lossless
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD22: review_id → review_build_id, review_user_id, review_content, review_created_at
  • All attributes present in Review ✓
  • Directly enforceable
• Result: PRESERVED ✓

---

2NF Relation 23: Suggestions

Suggestions(suggestion_id, suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type) PK: suggestion_id FK: suggestion_user_id → Users.user_id FK: suggestion_admin_id → Admins.admin_user_id

Derived From: FD23 (suggestion_id → suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type)

Functional Dependency Enforced: FD23

Lossless Join Check:

• suggestion_id was part of the composite key in Universal_Relation_1NF
• Now suggestion_id becomes the sole primary key in Suggestions
• Intersection: I = {suggestion_id}
• FD23 guarantees suggestion_id uniquely determines all non-key attributes
• Join with Universal_Relation_1NF on suggestion_id is lossless
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD23: suggestion_id → suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
  • All attributes present ✓
  • Directly enforceable
• Result: PRESERVED ✓

---

5.3 1NF Multivalued Relations – Already in 2NF

The four relations decomposed in the 1NF step (R1–R4: Case_Storage_Form_Factors, Case_Ps_Form_Factors, Case_Mobo_Form_Factors, Cooler_Cpu_Sockets) are already in 2NF because they contain only primary key attributes or attributes fully determined by their composite keys. No further decomposition is required.

Relation R1: Case_Storage_Form_Factors

Case_Storage_Form_Factors(component_id, pc_case_storage_form_factor, pc_case_storage_num_slots) PK: (component_id, pc_case_storage_form_factor) FK: component_id → Components.component_id

2NF Status: IN 2NF ✓

• Composite primary key (component_id, pc_case_storage_form_factor)
• The only non-key attribute pc_case_storage_num_slots depends on the entire composite key
• No partial dependencies exist

Lossless Join Check:

• Preserved from 1NF decomposition
• Already verified as lossless in 1NF section
• Result: LOSSLESS ✓

Dependency Preservation Check:

• FD24: (component_id, pc_case_storage_form_factor) → pc_case_storage_num_slots
  • All attributes present ✓
  • Already verified as preserved in 1NF section
• Result: PRESERVED ✓

---

Relation R2: Case_Ps_Form_Factors

Case_Ps_Form_Factors(component_id, pc_case_ps_form_factor) PK: (component_id, pc_case_ps_form_factor) FK: component_id → Components.component_id

2NF Status: IN 2NF ✓

• Only key attributes; no non-key attributes to form partial dependencies

Lossless Join Check: LOSSLESS ✓ (preserved from 1NF)

Dependency Preservation Check: PRESERVED ✓ (FD26 preserved in 1NF)

---

Relation R3: Case_Mobo_Form_Factors

Case_Mobo_Form_Factors(component_id, pc_case_mobo_form_factor) PK: (component_id, pc_case_mobo_form_factor) FK: component_id → Components.component_id

2NF Status: IN 2NF ✓

• Only key attributes

Lossless Join Check: LOSSLESS ✓ (preserved from 1NF)

Dependency Preservation Check: PRESERVED ✓ (FD25 preserved in 1NF)

---

Relation R4: Cooler_Cpu_Sockets

Cooler_Cpu_Sockets(component_id, cooler_cpu_socket) PK: (component_id, cooler_cpu_socket) FK: component_id → Components.component_id

2NF Status: IN 2NF ✓

• Only key attributes

Lossless Join Check: LOSSLESS ✓ (preserved from 1NF)

Dependency Preservation Check: PRESERVED ✓ (FD27 preserved in 1NF)

---

6. Third Normal Form (3NF) Analysis

6.1 Transitive Dependency Analysis for Each 2NF Relation

Relation 1: Users

Users(user_id, username, password, email) PK: user_id FD: FD1 (user_id → username, password, email)

Non-key Attributes: username, password, email

Transitive Dependency Check:

• Direct dependencies exist:
  • user_id → username (direct, via FD1)
  • user_id → password (direct, via FD1)
  • user_id → email (direct, via FD1)
• Potential transitive chains: None
  • username does not determine any attribute
  • password does not determine any attribute
  • email does not determine any attribute

Conclusion: NO transitive dependencies exist ✓

• All non-key attributes depend directly on the primary key
• 3NF Status: IN 3NF ✓

---

Relation 2: Admins

Admins(admin_user_id) PK: admin_user_id FD: FD2 (admin_user_id → ∅)

Non-key Attributes: user_id

Transitive Dependency Check:

• user_id is a foreign key (reference to Users.user_id)
• Foreign keys are not derived attributes; they are stored references to another table
• No chain exists because:
  • user_id is not functionally determined by any intermediate non-key attribute
  • user_id is directly stored in the relation as a reference
  • No further attributes depend on user_id within this relation

Conclusion: NO transitive dependencies exist ✓

• Foreign keys do not participate in transitive dependencies (they are direct references, not derived values)
• 3NF Status: IN 3NF ✓

---

Relation 3: Components

Components(component_id, component_name, component_brand, component_price, component_type, component_img_url) PK: component_id FD: FD3 (component_id → component_name, component_brand, component_price, component_type, component_img_url)

Non-key Attributes: component_name, component_brand, component_price, component_type, component_img_url

Transitive Dependency Check:

• All non-key attributes depend directly on component_id:
  • component_id → component_name (direct)
  • component_id → component_brand (direct)
  • component_id → component_price (direct)
  • component_id → component_type (direct)
  • component_id → component_img_url (direct)
• No attribute depends on another non-key attribute

Conclusion: NO transitive dependencies exist ✓

• All attributes are directly determined by the primary key
• No chains of the form PK → B → A exist
• 3NF Status: IN 3NF ✓

---

Relation 4: Cpu

Cpu(component_id, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp) PK: component_id FD: FD4 (component_id → cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp)

Non-key Attributes: cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp

Transitive Dependency Check:

• All non-key attributes depend directly on component_id
• No non-key attribute determines another non-key attribute

Conclusion: NO transitive dependencies exist ✓

• 3NF Status: IN 3NF ✓

---

Relation 5–17: Gpu, Memory, Storage, Power_Supply, Motherboard, Pc_Case, Cooler, Memory_Card, Optical_Drive, Sound_Card, Cables, Network_Adapter, Network_Card

All 13 of these relations follow the identical pattern:

Pattern: ` Relation(component_id, [component_specific_attributes]) PK: component_id FD: component_id → [all dependent attributes] `

Transitive Dependency Check (for each):

• All non-key attributes depend directly on component_id
• No attribute depends on another non-key attribute
• No chains of the form PK → B → A exist

Conclusion for Relations 5–17: NO transitive dependencies exist ✓

• 3NF Status: ALL IN 3NF ✓

---

Relation 18: Build

Build(build_id, build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved) PK: build_id FD: FD18 (build_id → build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved)

Non-key Attributes: build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved

Transitive Dependency Check:

• Direct dependencies:
  • build_id → build_user_id (direct)
  • build_id → build_name (direct)
  • build_id → build_created_at (direct)
  • build_id → build_description (direct)
  • build_id → build_total_price (direct)
  • build_id → build_is_approved (direct)
• build_user_id is a foreign key reference (not derived)
• No non-key attribute determines another non-key attribute
• No chains exist:
  • build_user_id does not determine build_name, build_created_at, etc.
  • Each attribute stands independently, directly determined by build_id

Conclusion: NO transitive dependencies exist ✓

• All attributes depend directly on the primary key
• Foreign key does not create transitive dependencies
• 3NF Status: IN 3NF ✓

---

Relation 19: Build_Component

Build_Component(build_id, component_id, build_component_num_components) PK: (build_id, component_id) FD: FD19 ((build_id, component_id) → build_component_num_components)

Non-key Attributes: build_component_num_components

Transitive Dependency Check:

• Only one non-key attribute exists: build_component_num_components
• It depends directly on the composite primary key (build_id, component_id)

Conclusion: NO transitive dependencies exist ✓

• Only one non-key attribute present
• Direct dependency on composite PK
• 3NF Status: IN 3NF ✓

---

Relation 20: Rating_Build

Rating_Build(build_id, user_id, rating_build_value) PK: (build_id, user_id) FD: FD20 ((build_id, user_id) → rating_build_value)

Non-key Attributes: rating_build_value

Transitive Dependency Check:

• Only one non-key attribute: rating_build_value
• Depends directly on (build_id, user_id)

Conclusion: NO transitive dependencies exist ✓

• Single non-key attribute
• 3NF Status: IN 3NF ✓

---

Relation 21: Favorite_Build

Favorite_Build(build_id, user_id) PK: (build_id, user_id) FD: FD21 ((build_id, user_id) → ∅)

Non-key Attributes: (none)

Transitive Dependency Check:

• No non-key attributes exist
• Therefore, no transitive dependencies can occur

Conclusion: NO transitive dependencies exist ✓

• 3NF Status: IN 3NF ✓

---

Relation 22: Review

Review(review_id, review_build_id, review_user_id, review_content, review_created_at) PK: review_id Alt Key: (review_build_id, review_user_id) FD: FD22 (review_id → review_build_id, review_user_id, review_content, review_created_at)

Non-key Attributes: review_build_id, review_user_id, review_content, review_created_at

Transitive Dependency Check:

• Direct dependencies on review_id:
  • review_id → review_build_id (direct, foreign key reference)
  • review_id → review_user_id (direct, foreign key reference)
  • review_id → review_content (direct)
  • review_id → review_created_at (direct)
• review_build_id and review_user_id are foreign keys (not derived)
• No non-key attribute determines another non-key attribute:
  • review_build_id does not determine review_content or review_created_at
  • review_user_id does not determine review_content or review_created_at
  • review_content does not determine review_created_at

Conclusion: NO transitive dependencies exist ✓

• All attributes depend directly on review_id
• Foreign keys do not create transitive dependencies
• 3NF Status: IN 3NF ✓

---

Relation 23: Suggestions

Suggestions(suggestion_id, suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type) PK: suggestion_id FD: FD23 (suggestion_id → suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type)

Non-key Attributes: suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type

Transitive Dependency Check:

• All non-key attributes depend directly on suggestion_id:
  • suggestion_id → suggestion_user_id (direct, foreign key)
  • suggestion_id → suggestion_admin_id (direct, foreign key)
  • suggestion_id → suggestion_link (direct)
  • suggestion_id → suggestion_admin_comment (direct)
  • suggestion_id → suggestion_description (direct)
  • suggestion_id → suggestion_status (direct)
  • suggestion_id → suggestion_component_type (direct)
• No non-key attribute determines another non-key attribute
• No chains of form PK → B → A exist

Conclusion: NO transitive dependencies exist ✓

• All attributes depend directly on the primary key
• Foreign keys are not derived values
• 3NF Status: IN 3NF ✓

---

Relation 24: Case_Storage_Form_Factors

Case_Storage_Form_Factors(component_id, pc_case_storage_form_factor, pc_case_storage_num_slots) PK: (component_id, pc_case_storage_form_factor) FD: FD24 ((component_id, pc_case_storage_form_factor) → pc_case_storage_num_slots)

Non-key Attributes: pc_case_storage_num_slots

Transitive Dependency Check:

• Only one non-key attribute: pc_case_storage_num_slots
• Depends directly on composite PK (component_id, pc_case_storage_form_factor)

Conclusion: NO transitive dependencies exist ✓

• 3NF Status: IN 3NF ✓

---

Relations 25–27: Case_Ps_Form_Factors, Case_Mobo_Form_Factors, Cooler_Cpu_Sockets

All three relations follow the identical pattern:

Pattern: ` Relation(component_id, [attribute]) PK: (component_id, [attribute]) FD: ([attributes] → ∅) `

Transitive Dependency Check (for each):

• No non-key attributes exist
• Therefore, no transitive dependencies can occur

Conclusion for Relations 25–27: NO transitive dependencies exist ✓

• 3NF Status: ALL IN 3NF

Relation	PK Type	Non-Key Attributes	Transitive Dependency?	3NF Status
Users	Single (user_id)	3 attributes	NO	✓ IN 3NF
Admins	Single (admin_user_id)	0 attributes	NO	✓ IN 3NF
Components	Single (component_id)	5 attributes	NO	✓ IN 3NF
Cpu–Network_Card (14 relations)	Single (component_id)	Variable	NO	✓ IN 3NF
Build	Single (build_id)	6 attributes	NO	✓ IN 3NF
Build_Component	Composite	1 attribute	NO	✓ IN 3NF
Rating_Build	Composite	1 attribute	NO	✓ IN 3NF
Favorite_Build	Composite	0 attributes	NO	✓ IN 3NF
Review	Single (review_id)	4 attributes	NO	✓ IN 3NF
Suggestions	Single (suggestion_id)	7 attributes	NO	✓ IN 3NF
Case_Storage_Form_Factors	Composite	1 attribute	NO	✓ IN 3NF
Case_Ps_Form_Factors	Composite	0 attributes	NO	✓ IN 3NF
Case_Mobo_Form_Factors	Composite	0 attributes	NO	✓ IN 3NF
Cooler_Cpu_Sockets	Composite	0 attributes	NO	✓ IN 3NF

BCNF Analysis

Relation	Primary Key	FD	Determinant	Is Determinant a CK?	Dependent Attributes	BCNF Satisfied
Cpu	component_id	FD4	component_id	YES (PK)	cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp	✓ YES
Gpu	component_id	FD5	component_id	YES (PK)	gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length	✓ YES
Memory	component_id	FD6	component_id	YES (PK)	memory_type, memory_speed, memory_capacity, memory_modules	✓ YES
Storage	component_id	FD7	component_id	YES (PK)	storage_type, storage_capacity, storage_form_factor	✓ YES
Power_Supply	component_id	FD8	component_id	YES (PK)	power_supply_type, power_supply_wattage, power_supply_form_factor	✓ YES
Motherboard	component_id	FD9	component_id	YES (PK)	motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots	✓ YES
Pc_Case	component_id	FD10	component_id	YES (PK)	pc_case_cooler_max_height, pc_case_gpu_max_length	✓ YES
Cooler	component_id	FD11	component_id	YES (PK)	cooler_type, cooler_height, cooler_max_tdp_supported	✓ YES
Memory_Card	component_id	FD12	component_id	YES (PK)	memory_card_num_slots, memory_card_interface	✓ YES
Optical_Drive	component_id	FD13	component_id	YES (PK)	optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed	✓ YES
Sound_Card	component_id	FD14	component_id	YES (PK)	sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel	✓ YES
Cables	component_id	FD15	component_id	YES (PK)	cables_length_cm, cables_type	✓ YES
Network_Adapter	component_id	FD16	component_id	YES (PK)	network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas	✓ YES
Network_Card	component_id	FD17	component_id	YES (PK)	network_card_num_ports, network_card_speed, network_card_interface	✓ YES

Users	user_id	FD1	user_id	YES (PK)	username, password, email	✓ YES
Admins	admin_user_id	FD2	admin_user_id	YES (PK)	(none)	✓ YES
Components	component_id	FD3	component_id	YES (PK)	component_name, component_brand, component_price, component_type, component_img_url	✓ YES
Build	build_id	FD18	build_id	YES (PK)	build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved	✓ YES
Review	review_id	FD22	review_id	YES (PK)	review_build_id, review_user_id, review_content, review_created_at	✓ YES

Build_Component	(build_id, component_id)	FD19	(build_id, component_id)	YES (PK)	build_component_num_components	✓ YES
Rating_Build	(build_id, user_id)	FD20	(build_id, user_id)	YES (PK)	rating_build_value	✓ YES
Favorite_Build	(build_id, user_id)	FD21	(build_id, user_id)	YES (PK)	(none)	✓ YES
Suggestions	suggestion_id	FD23	suggestion_id	YES (PK)	suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type	✓ YES

Case_Storage_Form_Factors	(component_id, pc_case_storage_form_factor)	FD24	(component_id, pc_case_storage_form_factor)	YES (PK)	pc_case_storage_num_slots	✓ YES
Case_Ps_Form_Factors	(component_id, pc_case_ps_form_factor)	FD26	(component_id, pc_case_ps_form_factor)	YES (PK)	(none)	✓ YES
Case_Mobo_Form_Factors	(component_id, pc_case_mobo_form_factor)	FD25	(component_id, pc_case_mobo_form_factor)	YES (PK)	(none)	✓ YES
Cooler_Cpu_Sockets	(component_id, cooler_cpu_socket)	FD27	(component_id, cooler_cpu_socket)	YES (PK)	(none)	✓ YES

6. Final BCNF Decomposed Schema

6.1 Complete List of All Relations

1. Users(user_id, username, password, email)
   • PK: user_id

2. Admins(admin_user_id)
   • PK: admin_user_id
   • FK: admin_user_id → Users.user_id

3. Components(component_id, component_name, component_brand, component_price, component_type, component_img_url)
   • PK: component_id

4. Cpu(component_id, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp)
   • PK: component_id
   • FK: component_id → Components.component_id

5. Gpu(component_id, gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length)
   • PK: component_id
   • FK: component_id → Components.component_id

6. Memory(component_id, memory_type, memory_speed, memory_capacity, memory_modules)
   • PK: component_id
   • FK: component_id → Components.component_id

7. Storage(component_id, storage_type, storage_capacity, storage_form_factor)
   • PK: component_id
   • FK: component_id → Components.component_id

8. Power_Supply(component_id, power_supply_type, power_supply_wattage, power_supply_form_factor)
   • PK: component_id
   • FK: component_id → Components.component_id

9. 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)
   • PK: component_id
   • FK: component_id → Components.component_id

10. Pc_Case(component_id, pc_case_cooler_max_height, pc_case_gpu_max_length)
    • PK: component_id
    • FK: component_id → Components.component_id

11. Cooler(component_id, cooler_type, cooler_height, cooler_max_tdp_supported)
    • PK: component_id
    • FK: component_id → Components.component_id

12. Memory_Card(component_id, memory_card_num_slots, memory_card_interface)
    • PK: component_id
    • FK: component_id → Components.component_id

13. Optical_Drive(component_id, optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed)
    • PK: component_id
    • FK: component_id → Components.component_id

14. Sound_Card(component_id, sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel)
    • PK: component_id
    • FK: component_id → Components.component_id

15. Cables(component_id, cables_length_cm, cables_type)
    • PK: component_id
    • FK: component_id → Components.component_id

16. Network_Adapter(component_id, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas)
    • PK: component_id
    • FK: component_id → Components.component_id

17. Network_Card(component_id, network_card_num_ports, network_card_speed, network_card_interface)
    • PK: component_id
    • FK: component_id → Components.component_id

18. Build(build_id, build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved)
    • PK: build_id
    • FK: build_user_id → Users.user_id

19. Build_Component(build_id, component_id, build_component_num_components)
    • PK: (build_id, component_id)
    • FK: build_id → Build.build_id
    • FK: component_id → Components.component_id

20. Rating_Build(build_id, user_id, rating_build_value)
    • PK: (build_id, user_id)
    • FK: build_id → Build.build_id
    • FK: user_id → Users.user_id

21. Favorite_Build(build_id, user_id)
    • PK: (build_id, user_id)
    • FK: build_id → Build.build_id
    • FK: user_id → Users.user_id

22. Review(review_id, review_build_id, review_user_id, review_content, review_created_at)
    • PK: review_id
    • Alt Key: (review_build_id, review_user_id)
    • FK: review_build_id → Build.build_id
    • FK: review_user_id → Users.user_id

23. Suggestions(suggestion_id, suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type)
    • PK: suggestion_id
    • FK: suggestion_user_id → Users.user_id
    • FK: suggestion_admin_id → Admins.admin_user_id

24. Case_Storage_Form_Factors(component_id, pc_case_storage_form_factor, pc_case_storage_num_slots)
    • PK: (component_id, pc_case_storage_form_factor)
    • FK: component_id → Components.component_id

25. Case_Ps_Form_Factors(component_id, pc_case_ps_form_factor)
    • PK: (component_id, pc_case_ps_form_factor)
    • FK: component_id → Components.component_id

26. Case_Mobo_Form_Factors(component_id, pc_case_mobo_form_factor)
    • PK: (component_id, pc_case_mobo_form_factor)
    • FK: component_id → Components.component_id

27. Cooler_Cpu_Sockets(component_id, cooler_cpu_socket)
    • PK: (component_id, cooler_cpu_socket)
    • FK: component_id → Components.component_id