Changes between Version 7 and Version 8 of Normalization

Timestamp:

02/01/26 19:54:20 (7 days ago)

Author:

233144

Comment:

--

Normalization

@@ -1 +1 @@
 = Normalization
-
-== Initial de-normalized relation (R) and functional dependencies
-
-R(
-  user_id, username, password, email,
-  component_id, component_name, brand, price, component_type, img_url,
-  cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp,
-  gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length,
-  memory_type, memory_speed, memory_capacity, memory_modules,
-  storage_type, storage_capacity, storage_form_factor,
-  psu_type, psu_wattage, psu_form_factor,
-  motherboard_socket, motherboard_chipset, motherboard_form_factor,
-  ram_type, num_ram_slots, max_ram_capacity, pci_express_slots,
-  case_cooler_max_height, case_gpu_max_length,
-  case_storage_form_factor, case_storage_slots,
-  case_ps_form_factor,
-  case_mobo_form_factor,
-  cooler_type, cooler_height, cooler_max_tdp,
-  cooler_socket,
-  memory_card_slots, memory_card_interface,
-  optical_form_factor, optical_type, optical_interface,
-  optical_write_speed, optical_read_speed,
-  sound_sample_rate, sound_bit_depth, sound_chipset, sound_interface, sound_channel,
-  cable_length, cable_type,
-  net_adapter_wifi, net_adapter_interface, net_adapter_antennas,
-  net_card_ports, net_card_speed, net_card_interface,
-  build_id, build_name, build_created_at, build_description,
-  build_total_price, build_is_approved,
-  build_component_id,
-  rating_value, review_content, review_created_at,
-  suggestion_id, suggestion_link, suggestion_description,
-  suggestion_status, suggestion_admin_comment, suggestion_component_type
-)
-
-Functional dependencies:
-
-user_id → username, password, email
-
-component_id → name, brand, price, component_type, img_url
-
-component_id → cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp
-
-component_id → gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length
-
-component_id → memory_type, memory_speed, memory_capacity, memory_modules
-
-component_id → storage_type, storage_capacity, storage_form_factor
-
-component_id → psu_type, psu_wattage, psu_form_factor
-
-component_id → motherboard_socket, motherboard_chipset, motherboard_form_factor, ram_type, num_ram_slots, max_ram_capacity, pci_express_slots
-
-component_id → case_cooler_max_height, case_gpu_max_length
-
-component_id → cooler_type, cooler_height, cooler_max_tdp
-
-component_id → memory_card_slots, memory_card_interface
-
-component_id → optical_form_factor, optical_type, optical_interface, optical_write_speed, optical_read_speed
-
-component_id → sound_sample_rate, sound_bit_depth, sound_chipset, sound_interface, sound_channel
-
-component_id → cable_length, cable_type
-
-component_id → net_adapter_wifi, net_adapter_interface, net_adapter_antennas
-
-component_id → net_card_ports, net_card_speed, net_card_interface
- 
-(component_id, form_factor) → /, for case motherboard and power supply form factors
-
-(component_id, form_factor) → num_slots, for case storage form factors
-
-(component_id, socket) → /, for cooler cpu sockets
-
-build_id → user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved
-
-build_component_id → build_id, user_id
-
-(build_id, user_id) → rating_value
-
-review_id → build_id, user_id, review_content, review_created_at
-
-suggestion_id → user_id, admin_id, link, description, status, admin_comment, component_type
-
-The de-normalized relation violates 1NF because it contains multi-valued attributes.
-
-== Candidate keys of the de-normalized relation
-
-Because the universal relation combines users, builds, components, reviews, and suggestions, a minimal key must uniquely identify all of them.
-
-The artifical candidate key is {user_id, build_id, component_id, review_id, suggestion_id, build_component_id}
-
-Universal relation primary key = {user_id, build_id, component_id, review_id, suggestion_id, build_component_id}
+== Initial de-normalized relation and functional dependencies
+Attributes with identical names originating from different relations were renamed using entity-based prefixes in order to avoid duplicate attribute names in the universal relation.
+
+R(user_id, username, password, email,
+component_id, component_name, brand, component_price, component_type,
+cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp,
+gpu_vram, gpu_tdp, gpu_base_clock, gpu¬_boost_clock, gpu_chipset, gpu_length,
+memory_type, memory_speed, memory_capacity, memory_modules,
+storage_type, storage_capacity, storage_form_factor,
+power_supply_type, power_supply_capacity, power_supply_form_factor,
+motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots,
+pc_case_coooler_max_height, pc_case_gpu_max_length,
+pc_case_storage_form_factor, num_slots
+pc_case_ps_form_factor,
+pc_case_mobo_form_factor,
+cooler_type, cooler_height, cooler_max_tdp_supported, 
+cooler_cpu_socket,
+memory_card_num_slots, memory_card_interface,
+optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed,
+sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel,
+cables_length_cm, cables_type,
+network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas,
+network_card_num_ports, network_card_speed, network_card_interface,
+build_id, build_name, build_created_at, build_description, build_total_price, build_is_approved,
+num_components,
+rating_build_value,
+review_id, review_content, review_created_at,
+suggestion_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
+
+Before anything else, we need to note that pc_case_storage_form_factor (composite attribute with num_slots), pc_case_ps_form_factor, pc_case_mobo_form_factor and cooler_cpu_socket are multivalued attributes.
+
+Now we will list the functional dependencies(the primary/composite keys are on the left side). For component_id the name of the proper subclass will be shown in parenthesis for better presentation.
+
+FD1: user_id -> username, password, email
+
+FD2: component_id -> component_name, brand, component_price, component_type, img_url
+
+FD3: component_id(cpu) -> cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp
+
+FD4: component_id(gpu) -> gpu_vram, gpu_tdp, gpu_base_clock, gpu¬_boost_clock, gpu_chipset, gpu_length
+
+FD5: component_id(memory) -> memory_type, memory_speed, memory_capacity, memory_modules
+
+FD6: component_id(storage) -> storage_type, storage_capacity, storage_form_factor
+
+FD7: component_id (power_supply) -> power_supply_type, power_supply_capacity, power_supply_form_factor
+
+FD8: component_id(motherboard) -> motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots
+
+FD9: component_id(pc_case) -> pc_case_coooler_max_height, pc_case_gpu_max_length
+
+FD10: component_id(cooler) -> cooler_type, cooler_height, cooler_max_tdp_supported
+
+FD11: component_id(memory_card) -> memory_card_num_slots, memory_card_interface
+
+FD12: component_id(optical_drive) -> optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed
+
+FD13: component_id(sound_card) -> sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel
+
+FD14: component_id(cables) -> cables_length_cm, cables_type
+
+FD15: component_id(network_adapter) -> network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas
+
+FD16: component_id(network_card) -> network_card_num_ports, network_card_speed, network_card_interface
+
+FD17: build_id -> user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved
+
+FD18: build_id, component_id -> num_components
+
+FD19: build_id, user_id -> /
+
+FD20: build_id, user_id -> rating_build_value
+
+FD21: review_id -> build_id, user_id, review_content, review_created_at
+
+FD22: suggestion_id -> user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
+
+Left: user_id, component_id, build_id, review_id, suggestion_id
+
+Left and right: build_id, user_id
+
+Right:
+username, password, email, component_name, brand, component_price, component_type, img_url, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp, gpu_vram, gpu_tdp, gpu_base_clock, gpu¬_boost_clock, gpu_chipset, gpu_length, memory_type, memory_speed, memory_capacity, memory_modules, storage_type, storage_capacity, storage_form_factor, power_supply_type, power_supply_capacity, power_supply_form_factor, motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots, pc_case_coooler_max_height, pc_case_gpu_max_length, num_slots, pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor, cooler_cpu_socket, cooler_type, cooler_height, cooler_max_tdp_supported, memory_card_num_slots, memory_card_interface, optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed, sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel, cables_length_cm, cables_type, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas, network_card_num_ports, network_card_speed, network_card_interface, build_name, build_created_at, build_description, build_total_price, build_is_approved, rating_build_value, review_content, review_created_at, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
+
+Attributes that never appear on the right side(only left) are component_id, review_id, suggestion_id. This means that they must be a part of the candidate key because nothing else can determine them.
+
+Let K = { component_id, review_id, suggestion_id }
+
+We now compute the closure K+
+
+Start with K+ = { component_id, review_id, suggestion_id }
+
+By applying FD2 – FD16, we add:
+component_name, brand, component_price, component_type,
+cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp,
+gpu_vram, gpu_tdp, gpu_base_clock, gpu¬_boost_clock, gpu_chipset, gpu_length,
+memory_type, memory_speed, memory_capacity, memory_modules,
+storage_type, storage_capacity, storage_form_factor,
+power_supply_type, power_supply_capacity, power_supply_form_factor,
+motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots,
+pc_case_coooler_max_height, pc_case_gpu_max_length,
+cooler_type, cooler_height, cooler_max_tdp_supported, 
+memory_card_num_slots, memory_card_interface,
+optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed,
+sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel,
+cables_length_cm, cables_type,
+network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas,
+network_card_num_ports, network_card_speed, network_card_interface
+
+By applying FD21, we add:
+build_id, user_id, review_content, review_created_at
+
+By applying FD17, we add:
+build_name, build_created_at, build_description, build_total_price, build_is_approved
+
+By applying FD1, we add:
+username, password, email
+
+By applying FD22, we  add:
+suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
+
+By applying FD18 and FD20, we add:
+num_components, rating_build_value
+
+After excluding multivalued attributes which we handle separatelu in 1NF, K+ contains all attributes of R, which means that K is a superkey.
+
+The universal relation violates 1NF because the attributes case_storage_form_factor, case_ps_form_factor, case_mobo_form_factor, and cooler_cpu_socket represent multivalued attributes, and can contain multiple values for a single component_id. This will be resolved by decomposing them into separate relations.
 
 == 1NF Decomposition
-
-Here we have to separate the multi-valued attributes into individual relations. This will ensure that all values are atomic.
-
-case_storage_form_factors(case_id, form_factor, num_slots)
-
-case_ps_form_factors(case_id, form_factor)
-
-case_mobo_form_factors(case_id, form_factor)
-
-cooler_cpu_sockets(cooler_id, socket)
-
-
-We separate obvious entities in individual relations:
-
-users(user_id, username, password, email)
-
-admins(user_id)
-
-components(component_id, name, brand, price, component_type, img_url)
-
-cpu(component_id, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp)
-
-gpu(component_id, gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length)
-
-memory(component_id, memory_type, memory_speed, memory_capacity, memory_modules)
-
-storage(component_id, storage_type, storage_capacity, storage_form_factor)
-
-power_supply(component_id, psu_type, psu_wattage, psu_form_factor)
-
-motherboard(component_id, motherboard_socket, motherboard_chipset, motherboard_form_factor, ram_type, num_ram_slots, max_ram_capacity, pci_express_slots)
-
-pc_case(component_id, case_cooler_max_height, case_gpu_max_length)
-
-cooler(component_id, cooler_type, cooler_height, cooler_max_tdp)
-
-memory_card(component_id, memory_card_slots, memory_card_interface)
-
-optical_drive(component_id, optical_form_factor, optical_type, optical_interface, optical_write_speed, optical_read_speed)
-
-sound_card(component_id, sound_sample_rate, sound_bit_depth, sound_chipset, sound_interface, sound_channel)
-
-cables(component_id, cable_length, cable_type)
-
-network_adapter(component_id, net_adapter_wifi, net_adapter_interface, net_adapter_antennas)
-
-network_card(component_id, net_card_ports, net_card_speed, net_card_interface)
-
-build(build_id, user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved)
-
-build_component(build_component_id, build_id, component_id)
-
-review(review_id, build_id, user_id, review_content, review_created_at)
-
-rating_build(build_id, user_id, rating_value)
-
-suggestions(suggestion_id, user_id, admin_id, link, description, status, admin_comment, component_type)
-
-all of which satisfy 1NF.
-
-
-Candidate keys & primary keys:
-
-users → user_id
-
-admins → user_id
-
-components → component_id
-
-component subclasses (like cpu, gpu etc.) → component_id
-
-build → build_id
-
-build_component → build_component_id
-
-review → review_id
-
-rating_build → (build_id, user_id)
-
-favorite_build → (build_id, user_id)
-
-suggestions → suggestion_id
-
-== Second Normal Form (2NF)
-
-2NF focuses on partial dependencies, which can only occur when a relation has a composite primary key and a non-key attribute depends on only part of that key. Relations with a single-attribute primary key were considered to be automatically in 2NF. Most of the relations obtained after 1NF like users, components, build, review and suggestions, have single-attribute primary keys so partial dependencies cannot exist in these relations. The remaining relations that have composite primary keys favorite_build and rating_build were analyzed separately.
-
-favorite_build(build_id, user_id) - doesn't have non-key attributes so it is already 2NF
-
-rating_build(build_id, user_id, rating_value) - rating_value depends on full key so it is 2NF
-
-In the case of associative tables like build_component and favorite_build no non-key attributes are present so partial dependencies are not possible. For rating_build the non-key attribute depend on the full composite key (a rating is uniquely determined by both the user and the build). Since no non-key attribute was found to be partially dependent all relations satisfy 2NF.
-
-== Third normal Form (3NF)
-
-users(user_id, username, password, email) - all attributes depend directly on user_id so 3NF is satisfied
-
-components(component_id, name, brand, price, component_type, img_url) - all attributes depend directly on component_id so this is 3NF
-
-build(build_id, user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved) - all attributes depend on build_id which means that 3NF is satisfied
-
-component subclasses (like cpu, gpu, memory etc.) - all attributes depend directly on component_id so 3NF is satisfied
-
-review(review_id, build_id, user_id, review_content, review_created_at) - attributes depend on review_id, 3NF is satisfied
-
-rating_build(build_id, user_id, rating_value) → rating_value depends on (build_id, user_id), 3NF is satisfied
-
-favorite_build(build_id, user_id) - no non-key attributes so this automatically satisfies 3NF
-
-suggestions(suggestion_id, user_id, admin_id, link, description, status, admin_comment, component_type) - attributes depend on suggestion_id, 3NF is satisfied
-
-case_storage_form_factors(case_id, form_factor, num_slots) - no transitive dependencies, 3NF is satisfied
-
-case_ps_form_factors(case_id, form_factor) - no transitive dependencies, 3NF is satisfied
-
-case_mobo_form_factors(case_id, form_factor) - no transitive dependencies, 3NF is satisfied
-
-cooler_cpu_sockets(cooler_id, socket) - no transitive dependencies, 3NF is satisfied
-
-build_component(build_component_id, build_id, component_id) - no transitive dependencies because all attributes depend on build_component_id, therefore 3NF is satisfied.
-
-== BCNF decomposition
-
-BCNF checks if every determinant is a super-key so we need to analyze all 3NF relations.
-
-users(user_id, username, password, email):
-
- FD: user_id → username, password, email
-
- Determinant user_id is the primary key (superkey), BCNF
-
-
-admins(user_id):
-
- Determinant user_id is the primary key (superkey), BCNF
-
-
-components(component_id, name, brand, price, component_type, img_url):
-
- FD: component_id → name, brand, price, component_type, img_url
-
- Determinant component_id is the primary key (superkey), BCNF
-
-
-Component specialization tables (cpu, gpu, memory, etc.):
-
- Each table: primary key = component_id
-
- All attributes depend only on component_id
-
- Determinant is primary key(superkey), BCNF
-
-
-build(build_id, user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved):
-
- FD: build_id → user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved
-
- Determinant build_id is primary key(superkey), BCNF
-
-
-build_component(build_component_id, build_id, component_id):
-
- FD: build_component_id → build_id, component_id
-
- Determinant build_component_id is primary key(superkey), BCNF
-
-
-favorite_build(build_id, user_id):
-
- FD: Composite key (build_id, user_id) → /
-
- No non-key attributes so its by default BCNF 
-
-
-rating_build(build_id, user_id, rating_value):
-
- FD: (build_id, user_id) → rating_value
-
- Determinant is full primary key (superkey), BCNF
-
-
-review(review_id, build_id, user_id, review_content, review_created_at):
-
- FD: review_id → build_id, user_id, review_content, review_created_at
-
- Determinant = primary key (superkey), BCNF
-
-
-suggestions(suggestion_id, user_id, admin_id, link, description, status, admin_comment, component_type):
-
- FD: suggestion_id → user_id, admin_id, link, description, status, admin_comment, component_type
-
- Determinant is primary key (superkey)
-
-
-case_storage_form_factors(case_id, form_factor, num_slots):
-
- FD: (case_id, form_factor) → num_slots
-
- Full composite key is determinant (superkey), BCNF
-
-
-case_ps_form_factors(case_id, form_factor):
-
- FD: (case_id, form_factor) → /
-
- Full composite key is determinant (superkey), BCNF
-
-
-case_mobo_form_factors(case_id, form_factor):
- 
- FD: (case_id, form_factor) → /
-
- Full composite key is determinant (superkey), BCNF
-
- 
-cooler_cpu_sockets(cooler_id, socket):
-
- FD: (cooler_id, socket) → /
-
- Full composite key is determinant (superkey), BCNF
-
-
-We can now see that all determinants are superkeys, so BCNF is satisfied.
-
-
+To achieve 1NF, we decompose the multivalued attributes from R into:
+
+component_id , pc_case_storage_form_factor -> num_slots
+
+component_id, pc_case_ps_form_factor -> no non-key attributes
+
+component_id, pc_case_mobo_form_factor -> no non-key attributes
+
+component_id, cooler_cpu_socket -> no non-key attributes
+
+1NF is satisfied now. 2NF is not satisfied because of partial dependencies. We can see this through the functional dependencies:
+
+FD2-FD16: component_id is part of the key, but the these attributes such as component_name, brand, component_price etc. depend only on component_id.
+
+We will decompose by creating new relations from R following FD2 to FD16 where the attributes will depend only on component_id:
+
+component_id -> component_id, component_name, brand, component_price, component_type, img_url
+
+component_id -> cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp
+
+component_id -> gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length
+
+component_id -> memory_type, memory_speed, memory_capacity, memory_modules
+
+component_id -> storage_type, storage_capacity, storage_form_factor
+
+component_id -> power_supply_type, power_supply_capacity, power_supply_form_factor
+
+component_id -> motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, 
+motherboard_pci_express_slots
+
+component_id -> pc_case_coooler_max_height, pc_case_gpu_max_length
+
+component_id -> cooler_type, cooler_height, cooler_max_tdp_supported, cooler_cpu_socket
+
+component_id -> memory_card_num_slots, memory_card_interface
+
+component_id -> optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed
+
+component_id -> sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel
+
+component_id -> cables_length_cm, cables_type
+
+component_id -> network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas
+
+component_id -> network_card_num_ports, network_card_speed, network_card_interface
+
+Primary key in all of these is component_id.
+
+FD21: review_id is part of the key, but these attributes build_id, user_id, review_content, review_created_at depend  only on review_id.
+
+We will solve this partial dependency by creating a new relation:
+
+review_id -> build_id, user_id, review_content, review_created_at
+
+Primary key here is review_id.
+
+FD22: suggestion_id is part of the key, but user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type depend only on suggestion_id.
+
+We will solve this partial dependency by creating a new relation:
+
+suggestion_id -> user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
+
+We get the following relations:
+
+R1 { review_id, build_id, user_id, review_content, review_created_at } where PK = review_id - BCNF
+
+R2 { suggestion_id, user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type } where PK = suggestion_id - BCNF
+
+R3 { component_id, component_name, brand, component_price, component_type, img_url } where PK = component_id - BCNF
+
+R4 { component_id, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp } where PK = component_id - BCNF
+
+R5 { component_id, gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length } where PK = component_id - BCNF
+
+R6 { component_id, memory_type, memory_speed, memory_capacity, memory_modules } where PK = component_id - BCNF
+
+R7 { component_id, storage_type, storage_capacity, storage_form_factor } where PK = component_id - BCNF
+
+R8 { component_id, power_supply_type, power_supply_capacity, power_supply_form_factor } where PK = component_id - BCNF
+
+R9 { component_id, motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots } where PK = component_id - BCNF
+
+R10 { component_id,  pc_case_coooler_max_height, pc_case_gpu_max_length } where PK = component_id - BCNF
+
+R11 { component_id, cooler_type, cooler_height, cooler_max_tdp_supported, cooler_cpu_socket } where PK = component_id - BCNF
+
+R12 { component_id, memory_card_num_slots, memory_card_interface } where PK = component_id - BCNF
+
+R13 { component_id, optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed } where PK = component_id - BCNF
+
+R14 { component_id, sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel } where PK = component_id - BCNF
+
+R15 { component_id, cables_length_cm, cables_type } where PK = component_id - BCNF
+
+R16 { component_id, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas } where PK = component_id - BCNF
+
+R17 { component_id, network_card_num_ports, network_card_speed, network_card_interface } where PK = component_id - BCNF
+
+R18 { component_id,  pc_case_storage_form_factor , num_slots } where PK = component_id and pc_case_storage_form_factor - BCNF
+
+R19 { component_id, pc_case_ps_form_factor } where PK = component_id and pc_case_ps_form_factor - BCNF
+
+R20 { component_id, pc_case_mobo_form_factor } where PK = component_id and pc_case_mobo_form_factor - BCNF
+
+R21 { component_id, cooler_cpu_socket } where PK = component_id and cooler_cpu_socket - BCNF
+
+The remainder of R { component_id, review_id, suggestion_id, build_id, user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved, num_components, rating_build_value, username, password, email } where PK = K (component_id, review_id, suggestion_id)
+
+2NF is satisfied but the remaining of R has transitive dependencies which violate 3NF. When looking at FD17: build_id -> build_name, build_created_at, build_description, build_total_price, build_is_approved, here build_id is not part of the candidate key K, but it determines build_name and other attributes. The same thing goes for user_id -> username, password, email when looking at FD1. We will handle these transitive dependencies by creating separate tables.
+
+== 3NF decomposition
+R22 { user_id, username, password, email } where PK = user_id - BCNF
+
+R23 { build_id, user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved } where PK = build_id – BCNF
+
+Leftover relation R { component_id, review_id, suggestion_id, build_id, user_id, num_components, rating_build_value }
+
+From FD18 (build_id, component_id) → num_components we see that build_id and component_id is not a superkey of leftover relation R, this violates BCNF. Same thing goes for FD19 (build_id, user_id) -> / and FD20 (build_id, user_id) → rating_build_value, build_id and user_id is not a superkey of the leftover relation. We will decompose by creating new relations
+
+R24 { build_id , component_id, num_components } where PK = build_id, component_id - BCNF
+
+R25 { build_id, user_id, rating_build_value } where PK = build_id, user_id - BCNF
+
+R26 { build_id, user_id } where PK = build_id, user_id – BCNF
+
+== Final BCNF relations
+
+Users { user_id, username, password, email }
+
+Components { component_id, component_name, brand, component_price, component_type, img_url }
+
+Cpu { component_id, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp }
+
+Gpu { { component_id, gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length }
+
+Memory { component_id, memory_type, memory_speed, memory_capacity, memory_modules }
+
+Storage { component_id, storage_type, storage_capacity, storage_form_factor }
+
+Power_Supply { component_id, power_supply_type, power_supply_capacity, power_supply_form_factor }
+
+Motherboard { component_id, motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots }
+
+Pc_Case { component_id,  pc_case_coooler_max_height, pc_case_gpu_max_length }
+
+Cooler { component_id, cooler_type, cooler_height, cooler_max_tdp_supported }
+
+Memory_Card { component_id, memory_card_num_slots, memory_card_interface }
+
+Optical_Drive { component_id, optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed }
+
+Sound_Card { component_id, sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel }
+
+Cables { component_id, cables_length_cm, cables_type }
+
+Network_Adapter { component_id, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas }
+
+Network_Card { component_id, network_card_num_ports, network_card_speed, network_card_interface }
+
+Pc_Case_Storage_Form_Factor { component_id,  pc_case_storage_form_factor , num_slots }
+
+Pc_Case_Ps_Form_Factor { component_id, pc_case_ps_form_factor }
+
+Pc_Case_Mobo_Form_Factor { component_id, pc_case_mobo_form_factor }
+
+Cooler_Cpu_Socket { component_id, cooler_cpu_socket }
+
+Build { build_id, user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved }
+
+Build_Component { build_id, component_id, num_components }
+
+Rating_Build { build_id, user_id, rating_build_value }
+
+Favorite_Build { build_id, user_id }
+
+Review { review_id, build_id, user_id, review_content, review_created_at }
+
+Suggestion { suggestion_id, user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type }
+
+For better contextualization we will add the Admins table which is just a specialization of Users, that has admin_id which references user_id in Users.
+
+Admins { admin_id } - BCNF
+
+
+
+
+
+
+