wiki:Normalization

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, 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–FD23 (all except FD24–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 ✓
  • 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
  1. Admins(admin_user_id)
    • PK: admin_user_id
    • FK: admin_user_id → Users.user_id
  1. Components(component_id, component_name, component_brand, component_price, component_type, component_img_url)
    • PK: component_id
  1. 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
  1. 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
  1. Memory(component_id, memory_type, memory_speed, memory_capacity, memory_modules)
    • PK: component_id
    • FK: component_id → Components.component_id
  1. Storage(component_id, storage_type, storage_capacity, storage_form_factor)
    • PK: component_id
    • FK: component_id → Components.component_id
  1. Power_Supply(component_id, power_supply_type, power_supply_wattage, power_supply_form_factor)
    • PK: component_id
    • FK: component_id → Components.component_id
  1. 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
  1. Pc_Case(component_id, pc_case_cooler_max_height, pc_case_gpu_max_length)
    • PK: component_id
    • FK: component_id → Components.component_id
  1. Cooler(component_id, cooler_type, cooler_height, cooler_max_tdp_supported)
    • PK: component_id
    • FK: component_id → Components.component_id
  1. Memory_Card(component_id, memory_card_num_slots, memory_card_interface)
    • PK: component_id
    • FK: component_id → Components.component_id
  1. 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
  1. 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
  1. Cables(component_id, cables_length_cm, cables_type)
    • PK: component_id
    • FK: component_id → Components.component_id
  1. Network_Adapter(component_id, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas)
    • PK: component_id
    • FK: component_id → Components.component_id
  1. Network_Card(component_id, network_card_num_ports, network_card_speed, network_card_interface)
    • PK: component_id
    • FK: component_id → Components.component_id
  1. 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
  1. 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
  1. 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
  1. Favorite_Build(build_id, user_id)
    • PK: (build_id, user_id)
    • FK: build_id → Build.build_id
    • FK: user_id → Users.user_id
  1. 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
  1. 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
  1. 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
  1. 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
  1. 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
  1. Cooler_Cpu_Sockets(component_id, cooler_cpu_socket)
    • PK: (component_id, cooler_cpu_socket)
    • FK: component_id → Components.component_id

Final discussion and summary

The normalization process completely validated the structure defined in the previous conceptual phase. This confirms that the initial ER model was designed following sound database design principles, including minimal redundancy, clear separation of concepts, and accurate identification of entities and relationships.

As a result, the final relational schema remains consistent with the model developed in Phase 2.

Last modified 3 days ago Last modified on 02/25/26 20:22:53
Note: See TracWiki for help on using the wiki.