Changes between Version 13 and Version 14 of Normalization


Ignore:
Timestamp:
02/03/26 03:07:33 (6 days ago)
Author:
233144
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Normalization

    v13 v14  
    11= Normalization
     2
    23== Initial de-normalized relation and functional dependencies
     4
    35Attributes with identical names originating from different relations were renamed using entity-based prefixes in order to avoid duplicate attribute names in the universal relation.
    46
    57R(user_id, username, password, email,
    6 component_id, component_name, brand, component_price, component_type, img_url,
     8admin_user_id,
     9component_id, component_name, component_brand, component_price, component_type, component_img_url,
    710cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp,
    811gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length,
    912memory_type, memory_speed, memory_capacity, memory_modules,
    1013storage_type, storage_capacity, storage_form_factor,
    11 power_supply_type, power_supply_capacity, power_supply_form_factor,
     14power_supply_type, power_supply_wattage, power_supply_form_factor,
    1215motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots,
    1316pc_case_cooler_max_height, pc_case_gpu_max_length,
    14 pc_case_storage_form_factor, num_slots,
     17pc_case_storage_form_factor, case_storage_num_slots,
    1518pc_case_ps_form_factor,
    1619pc_case_mobo_form_factor,
    17 cooler_type, cooler_height, cooler_max_tdp_supported, 
     20cooler_type, cooler_height, cooler_max_tdp_supported,
    1821cooler_cpu_socket,
    1922memory_card_num_slots, memory_card_interface,
    … …  
    2326network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas,
    2427network_card_num_ports, network_card_speed, network_card_interface,
    25 build_id, build_name, build_created_at, build_description, build_total_price, build_is_approved,
    26 num_components,
     28build_id, build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved,
     29build_component_num_components,
    2730rating_build_value,
    28 review_id, review_content, review_created_at,
    29 suggestion_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type)
    30 
    31 Before anything else, we need to note that pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor and cooler_cpu_socket are multivalued attributes.
    32 
    33 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.
     31review_id, review_build_id, review_user_id, review_content, review_created_at,
     32suggestion_id, suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type)
     33
     34Before anything else, we need to note that pc_case_storage_form_factor (composite with case_storage_num_slots), pc_case_ps_form_factor, pc_case_mobo_form_factor, and cooler_cpu_socket are multivalued attributes.
     35
     36Now 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.
    3437
    3538FD1: user_id -> username, password, email
    3639
    37 FD2: component_id -> component_name, brand, component_price, component_type, img_url
    38 
    39 FD3: component_id(cpu) -> cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp
    40 
    41 FD4: component_id(gpu) -> gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length
    42 
    43 FD5: component_id(memory) -> memory_type, memory_speed, memory_capacity, memory_modules
    44 
    45 FD6: component_id(storage) -> storage_type, storage_capacity, storage_form_factor
    46 
    47 FD7: component_id (power_supply) -> power_supply_type, power_supply_capacity, power_supply_form_factor
    48 
    49 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
    50 
    51 FD9: component_id(pc_case) -> pc_case_cooler_max_height, pc_case_gpu_max_length
    52 
    53 FD10: component_id(cooler) -> cooler_type, cooler_height, cooler_max_tdp_supported
    54 
    55 FD11: component_id(memory_card) -> memory_card_num_slots, memory_card_interface
    56 
    57 FD12: component_id(optical_drive) -> optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed
    58 
    59 FD13: component_id(sound_card) -> sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel
    60 
    61 FD14: component_id(cables) -> cables_length_cm, cables_type
    62 
    63 FD15: component_id(network_adapter) -> network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas
    64 
    65 FD16: component_id(network_card) -> network_card_num_ports, network_card_speed, network_card_interface
    66 
    67 FD17: build_id -> user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved
    68 
    69 FD18: build_id, component_id -> num_components
    70 
    71 FD19: build_id, user_id -> /
     40FD2: admin_user_id -> user_id (FK relationship)
     41
     42FD3: component_id -> component_name, component_brand, component_price, component_type, component_img_url
     43
     44FD4: component_id(cpu) -> cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp
     45
     46FD5: component_id(gpu) -> gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length
     47
     48FD6: component_id(memory) -> memory_type, memory_speed, memory_capacity, memory_modules
     49
     50FD7: component_id(storage) -> storage_type, storage_capacity, storage_form_factor
     51
     52FD8: component_id(power_supply) -> power_supply_type, power_supply_wattage, power_supply_form_factor
     53
     54FD9: 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
     55
     56FD10: component_id(pc_case) -> pc_case_cooler_max_height, pc_case_gpu_max_length
     57
     58FD11: component_id(cooler) -> cooler_type, cooler_height, cooler_max_tdp_supported
     59
     60FD12: component_id(memory_card) -> memory_card_num_slots, memory_card_interface
     61
     62FD13: component_id(optical_drive) -> optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed
     63
     64FD14: component_id(sound_card) -> sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel
     65
     66FD15: component_id(cables) -> cables_length_cm, cables_type
     67
     68FD16: component_id(network_adapter) -> network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas
     69
     70FD17: component_id(network_card) -> network_card_num_ports, network_card_speed, network_card_interface
     71
     72FD18: build_id -> build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved
     73
     74FD19: build_id, component_id -> build_component_num_components
    7275
    7376FD20: build_id, user_id -> rating_build_value
    7477
    75 FD21: review_id -> build_id, user_id, review_content, review_created_at
    76 
    77 FD22: suggestion_id -> user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
    78 
    79 Left: user_id, component_id, build_id, review_id, suggestion_id
    80 
    81 Left and right: build_id, user_id
     78FD21: build_id, user_id -> (represents favorite_build relationship, no additional attributes)
     79
     80FD22: review_id -> review_build_id, review_user_id, review_content, review_created_at
     81
     82FD23: review_build_id, review_user_id -> review_id (alternate key constraint from UNIQUE)
     83
     84FD24: suggestion_id -> suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
     85
     86Left: user_id, admin_user_id, component_id, build_id, review_id, suggestion_id
     87
     88Left and right: build_user_id, user_id (via various FDs), review_build_id, review_user_id
    8289
    8390Right:
    84 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_cooler_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
    85 
    86 Attributes that never appear on the right side(only left) are component_id, review_id, suggestion_id. This means that they must be in every candidate key because nothing can determine them.
    87 
    88 Lets test the minimal candidate key K = { component_id, review_id, suggestion_id }
     91username, password, email, component_name, component_brand, component_price, component_type, component_img_url, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp, gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length, memory_type, memory_speed, memory_capacity, memory_modules, storage_type, storage_capacity, storage_form_factor, power_supply_type, power_supply_wattage, power_supply_form_factor, motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots, pc_case_cooler_max_height, pc_case_gpu_max_length, case_storage_num_slots, pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor, cooler_type, cooler_height, cooler_max_tdp_supported, cooler_cpu_socket, memory_card_num_slots, memory_card_interface, optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed, sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel, cables_length_cm, cables_type, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas, network_card_num_ports, network_card_speed, network_card_interface, build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved, build_component_num_components, rating_build_value, review_build_id, review_user_id, review_content, review_created_at, suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
     92
     93Attributes that never appear on the right side (only left) are user_id, admin_user_id, component_id, build_id, review_id, suggestion_id. This means that they must be in every candidate key because nothing can determine them.
     94
     95Let's compute the closures of each individual left-side attribute to see what we can derive from each:
     96
     97{user_id}+ : By applying FD1 we add username, password, email. So {user_id}+ = {user_id, username, password, email}. This is NOT a superkey (missing all other attributes).
     98
     99{admin_user_id}+ : By applying FD2, admin_user_id → user_id. By applying FD1, user_id → username, password, email. So {admin_user_id}+ = {admin_user_id, user_id, username, password, email}. This is NOT a superkey (missing component_id, build_id, review_id, suggestion_id and their dependents).
     100
     101{component_id}+ : By applying FD3-FD17 we add component_name, component_brand, component_price, component_type, component_img_url, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp, gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length, memory_type, memory_speed, memory_capacity, memory_modules, storage_type, storage_capacity, storage_form_factor, power_supply_type, power_supply_wattage, power_supply_form_factor, motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots, pc_case_cooler_max_height, pc_case_gpu_max_length, cooler_type, cooler_height, cooler_max_tdp_supported, memory_card_num_slots, memory_card_interface, optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed, sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel, cables_length_cm, cables_type, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas, network_card_num_ports, network_card_speed, network_card_interface. So {component_id}+ contains all component attributes but is NOT a superkey (missing user_id, admin_user_id, build_id, review_id, suggestion_id and their dependents).
     102
     103{build_id}+ : By applying FD18 we add build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved. By applying FD19 we need component_id which we don't have. By applying FD20 and FD21 we need user_id which we don't have. So {build_id}+ = {build_id, build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved}. This is NOT a superkey.
     104
     105{review_id}+ : By applying FD22 we add review_build_id, review_user_id, review_content, review_created_at. We cannot derive build_id or user_id directly from the relation. So {review_id}+ = {review_id, review_build_id, review_user_id, review_content, review_created_at}. This is NOT a superkey.
     106
     107{suggestion_id}+ : By applying FD24 we add suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type. We cannot derive admin_user_id directly. So {suggestion_id}+ = {suggestion_id, suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type}. This is NOT a superkey.
     108
     109Since no single left-side attribute can determine all others, we must combine them. Let's test the candidate key K = {user_id, admin_user_id, component_id, review_id, suggestion_id, build_id}.
    89110
    90111We now compute the closure K+
    91112
    92 Start with K+ = { component_id, review_id, suggestion_id }
    93 
    94 By applying FD2 – FD16, we add:
    95 component_name, brand, component_price, component_type,
     113Start with K+ = {user_id, admin_user_id, component_id, review_id, suggestion_id, build_id}
     114
     115By applying FD1 – we add:
     116username, password, email
     117
     118By applying FD2 – we verify admin specialization (admin_user_id is FK to user_id, already in set)
     119
     120By applying FD3 – FD17, we add:
     121component_name, component_brand, component_price, component_type, component_img_url,
    96122cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp,
    97123gpu_vram, gpu_tdp, gpu_base_clock, gpu_boost_clock, gpu_chipset, gpu_length,
    98124memory_type, memory_speed, memory_capacity, memory_modules,
    99125storage_type, storage_capacity, storage_form_factor,
    100 power_supply_type, power_supply_capacity, power_supply_form_factor,
     126power_supply_type, power_supply_wattage, power_supply_form_factor,
    101127motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots,
    102128pc_case_cooler_max_height, pc_case_gpu_max_length,
    103 cooler_type, cooler_height, cooler_max_tdp_supported, 
     129cooler_type, cooler_height, cooler_max_tdp_supported,
    104130memory_card_num_slots, memory_card_interface,
    105131optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed,
    … …  
    109135network_card_num_ports, network_card_speed, network_card_interface
    110136
    111 By applying FD21, we add:
    112 build_id, user_id, review_content, review_created_at
    113 
    114 By applying FD17, we add:
    115 build_name, build_created_at, build_description, build_total_price, build_is_approved
    116 
    117 By applying FD1, we add:
    118 username, password, email
    119 
    120 By applying FD22, we  add:
    121 suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
    122 
    123 By applying FD18 and FD20, we add:
    124 num_components, rating_build_value
    125 
    126 If we remove the component_id - then the component attributes cant be determined. Same thing goes for review_id and suggestion_id.
    127 After excluding multivalued attributes which we handle separatelu in 1NF, K+ contains all attributes of R, which means that K is a superkey.
     137By applying FD18, we add:
     138build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved
     139
     140By applying FD19 (we have build_id and component_id), we add:
     141build_component_num_components
     142
     143By applying FD20 and FD21 (we have build_id and user_id), we add:
     144rating_build_value
     145
     146By applying FD22, we add:
     147review_build_id, review_user_id, review_content, review_created_at
     148
     149By applying FD24, we add:
     150suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
     151
     152After excluding multivalued attributes which we handle separately in 1NF, K+ contains all attributes of R, which means that K is a superkey.
     153
     154To verify that K = {user_id, admin_user_id, component_id, review_id, suggestion_id, build_id} is a candidate key, we test all proper subsets:
     155
     156{admin_user_id, component_id, review_id, suggestion_id, build_id}+ is missing user_id which blocks FD20 and FD21 (need user_id for build_id, user_id -> rating_build_value and favorite_build).
     157
     158{user_id, component_id, review_id, suggestion_id, build_id}+ is missing admin_user_id which blocks FD24 completely (need to derive suggestion_admin_id from admin_user_id).
     159
     160{user_id, admin_user_id, review_id, suggestion_id, build_id}+ is missing component_id which blocks FD19 (need build_id, component_id for build_component_num_components) and all component attributes.
     161
     162{user_id, admin_user_id, component_id, suggestion_id, build_id}+ is missing review_id which blocks FD22 (need review_id -> review attributes).
     163
     164{user_id, admin_user_id, component_id, review_id, build_id}+ is missing suggestion_id which blocks FD24 (need suggestion_id -> suggestion attributes).
     165
     166{user_id, admin_user_id, component_id, review_id, suggestion_id}+ is missing build_id which blocks FD18, FD19, FD20, FD21 (need build_id -> build attributes and build_id for various composite FDs).
     167
     168Therefore, K = {user_id, admin_user_id, component_id, review_id, suggestion_id, build_id} is the minimal candidate key (no proper subset is a superkey).
    128169
    129170The universal relation violates 1NF because the attributes pc_case_storage_form_factor, pc_case_ps_form_factor, pc_case_mobo_form_factor, and cooler_cpu_socket represent multivalued attributes, and can contain multiple values for a single component_id. This will be resolved by decomposing them into separate relations.
    130171
    131172== 1NF Decomposition
     173
    132174To achieve 1NF, we decompose the multivalued attributes from R into:
    133175
    134 component_id , pc_case_storage_form_factor -> num_slots
     176component_id, pc_case_storage_form_factor -> case_storage_num_slots
    135177
    136178component_id, pc_case_ps_form_factor -> no non-key attributes
    … …  
    1421841NF is satisfied now. 2NF is not satisfied because of partial dependencies. We can see this through the functional dependencies:
    143185
    144 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.
    145 
    146 We will decompose by creating new relations from R following FD2 to FD16 where the attributes will depend only on component_id:
    147 
    148 component_id -> component_id, component_name, brand, component_price, component_type, img_url
     186FD1: user_id is part of the key, but these attributes such as username, password, email depend only on user_id.
     187
     188FD2: admin_user_id is part of the key, but user_id depends only on admin_user_id.
     189
     190FD3-FD17: component_id is part of the key, but these attributes such as component_name, component_brand, component_price etc. depend only on component_id.
     191
     192FD18: build_id is part of the key, but these attributes such as build_user_id, build_name etc. depend only on build_id.
     193
     194FD19: (build_id, component_id) is part of the key, but build_component_num_components depends on (build_id, component_id).
     195
     196FD20: (build_id, user_id) is part of the key, but rating_build_value depends on (build_id, user_id).
     197
     198FD21: (build_id, user_id) is part of the key, but represents favorite_build (no attributes).
     199
     200FD22: review_id is part of the key, but these attributes such as review_build_id, review_user_id, review_content, review_created_at depend only on review_id.
     201
     202FD24: suggestion_id is part of the key, but these attributes such as suggestion_user_id, suggestion_admin_id etc. depend only on suggestion_id.
     203
     204We will decompose by creating new relations from R following FD1, FD2 to FD24 where the attributes will depend only on the determinant attribute(s), which will become the primary key:
     205
     206user_id -> username, password, email
     207
     208admin_user_id -> user_id
     209
     210component_id -> component_name, component_brand, component_price, component_type, component_img_url
    149211
    150212component_id -> cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp
    … …  
    156218component_id -> storage_type, storage_capacity, storage_form_factor
    157219
    158 component_id -> power_supply_type, power_supply_capacity, power_supply_form_factor
    159 
    160 component_id -> motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity,
    161 motherboard_pci_express_slots
     220component_id -> power_supply_type, power_supply_wattage, power_supply_form_factor
     221
     222component_id -> motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots
    162223
    163224component_id -> pc_case_cooler_max_height, pc_case_gpu_max_length
    164225
    165 component_id -> cooler_type, cooler_height, cooler_max_tdp_supported, cooler_cpu_socket
    166 
    167 component_id -> memory_card_num_slots, memory_card_interface
    168 
    169 component_id -> optical_drive_form_factor, optical_drive_type, optical_drive_interface, optical_drive_write_speed, optical_drive_read_speed
    170 
    171 component_id -> sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel
    172 
    173 component_id -> cables_length_cm, cables_type
    174 
    175 component_id -> network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas
    176 
    177 component_id -> network_card_num_ports, network_card_speed, network_card_interface
    178 
    179 Primary key in all of these is component_id.
    180 
    181 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.
    182 
    183 We will solve this partial dependency by creating a new relation:
    184 
    185 review_id -> build_id, user_id, review_content, review_created_at
    186 
    187 Primary key here is review_id.
    188 
    189 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.
    190 
    191 We will solve this partial dependency by creating a new relation:
    192 
    193 suggestion_id -> user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
    194 
    195 We get the following relations:
    196 
    197 R1 { review_id, build_id, user_id, review_content, review_created_at } where PK = review_id - BCNF
    198 
    199 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
    200 
    201 R3 { component_id, component_name, brand, component_price, component_type, img_url } where PK = component_id - BCNF
     226build_id -> build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved
     227
     228build_id, component_id -> build_component_num_components
     229
     230build_id, user_id -> rating_build_value
     231
     232build_id, user_id -> (represents favorite_build)
     233
     234review_id -> review_build_id, review_user_id, review_content, review_created_at
     235
     236suggestion_id -> suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type
     237
     238Primary key in all of these is the attribute(s) on the left side.
     239
     240== 2NF Decomposition
     241
     242All these partial dependencies are addressed in 2NF. We will solve these partial dependencies by creating new relations where each determinant becomes a primary key:
     243
     244R1 { user_id, username, password, email } where PK = user_id - BCNF
     245
     246R2 { admin_user_id, user_id } where PK = admin_user_id, FK = user_id -> R1.user_id - BCNF
     247
     248R3 { component_id, component_name, component_brand, component_price, component_type, component_img_url } where PK = component_id - BCNF
    202249
    203250R4 { component_id, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp } where PK = component_id - BCNF
    … …  
    209256R7 { component_id, storage_type, storage_capacity, storage_form_factor } where PK = component_id - BCNF
    210257
    211 R8 { component_id, power_supply_type, power_supply_capacity, power_supply_form_factor } where PK = component_id - BCNF
     258R8 { component_id, power_supply_type, power_supply_wattage, power_supply_form_factor } where PK = component_id - BCNF
    212259
    213260R9 { 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
    214261
    215 R10 { component_id,  pc_case_cooler_max_height, pc_case_gpu_max_length } where PK = component_id - BCNF
    216 
    217 R11 { component_id, cooler_type, cooler_height, cooler_max_tdp_supported } where PK = component_id - BCNF
    218 
    219 R12 { component_id, memory_card_num_slots, memory_card_interface } where PK = component_id - BCNF
    220 
    221 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
    222 
    223 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
    224 
    225 R15 { component_id, cables_length_cm, cables_type } where PK = component_id - BCNF
    226 
    227 R16 { component_id, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas } where PK = component_id - BCNF
    228 
    229 R17 { component_id, network_card_num_ports, network_card_speed, network_card_interface } where PK = component_id - BCNF
    230 
    231 R18 { component_id,  pc_case_storage_form_factor , num_slots } where PK = component_id and pc_case_storage_form_factor - BCNF
    232 
    233 R19 { component_id, pc_case_ps_form_factor } where PK = component_id and pc_case_ps_form_factor - BCNF
    234 
    235 R20 { component_id, pc_case_mobo_form_factor } where PK = component_id and pc_case_mobo_form_factor - BCNF
    236 
    237 R21 { component_id, cooler_cpu_socket } where PK = component_id and cooler_cpu_socket - BCNF
    238 
    239 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)
    240 
    241 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.
    242 
    243 == 3NF decomposition
    244 R22 { user_id, username, password, email } where PK = user_id - BCNF
    245 
    246 R23 { build_id, user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved } where PK = build_id – BCNF
    247 
    248 Leftover relation R { component_id, review_id, suggestion_id, build_id, user_id, num_components, rating_build_value }
    249 
    250 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
    251 
    252 R24 { build_id , component_id, num_components } where PK = build_id, component_id - BCNF
    253 
    254 R25 { build_id, user_id, rating_build_value } where PK = build_id, user_id - BCNF
    255 
    256 R26 { build_id, user_id } where PK = build_id, user_id – BCNF
     262R10 { component_id, pc_case_cooler_max_height, pc_case_gpu_max_length } where PK = component_id - BCNF
     263
     264R11 { build_id, build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved } where PK = build_id - BCNF
     265
     266R12 { build_id, component_id, build_component_num_components } where PK = (build_id, component_id) - BCNF
     267
     268R13 { build_id, user_id, rating_build_value } where PK = (build_id, user_id) - BCNF
     269
     270R14 { build_id, user_id } where PK = (build_id, user_id) - BCNF
     271
     272R15 { component_id, cooler_type, cooler_height, cooler_max_tdp_supported } where PK = component_id - BCNF
     273
     274R16 { component_id, memory_card_num_slots, memory_card_interface } where PK = component_id - BCNF
     275
     276R17 { 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
     277
     278R18 { component_id, sound_card_sample_rate, sound_card_bit_depth, sound_card_chipset, sound_card_interface, sound_card_channel } where PK = component_id - BCNF
     279
     280R19 { component_id, cables_length_cm, cables_type } where PK = component_id - BCNF
     281
     282R20 { component_id, network_adapter_wifi_version, network_adapter_interface, network_adapter_num_antennas } where PK = component_id - BCNF
     283
     284R21 { component_id, network_card_num_ports, network_card_speed, network_card_interface } where PK = component_id - BCNF
     285
     286R22 { review_id, review_build_id, review_user_id, review_content, review_created_at } where PK = review_id, Alt Key = (review_build_id, review_user_id) - BCNF
     287
     288R23 { suggestion_id, suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type } where PK = suggestion_id - BCNF
     289
     290The remainder of R { component_id, review_id, suggestion_id, build_id, user_id, admin_user_id } where PK = K (component_id, review_id, suggestion_id, build_id, user_id, admin_user_id)
     291
     2922NF is satisfied for all relations. All partial dependencies have been eliminated. No transitive dependencies can exist in relations with only key attributes or attributes derived entirely from the key.
     293
     294All relations are already in BCNF since every determinant of every functional dependency is a candidate key of its respective relation.
    257295
    258296== Final BCNF relations
    … …  
    260298Users { user_id, username, password, email }
    261299
    262 Components { component_id, component_name, brand, component_price, component_type, img_url }
     300Admins { admin_user_id, user_id (FK to Users.user_id) }
     301
     302Components { component_id, component_name, component_brand, component_price, component_type, component_img_url }
    263303
    264304Cpu { component_id, cpu_socket, cpu_cores, cpu_threads, cpu_base_clock, cpu_boost_clock, cpu_tdp }
    … …  
    270310Storage { component_id, storage_type, storage_capacity, storage_form_factor }
    271311
    272 Power_Supply { component_id, power_supply_type, power_supply_capacity, power_supply_form_factor }
     312Power_Supply { component_id, power_supply_type, power_supply_wattage, power_supply_form_factor }
    273313
    274314Motherboard { component_id, motherboard_socket, motherboard_chipset, motherboard_form_factor, motherboard_ram_type, motherboard_num_ram_slots, motherboard_max_ram_capacity, motherboard_pci_express_slots }
    275315
    276 Pc_Case { component_id,  pc_case_cooler_max_height, pc_case_gpu_max_length }
     316Pc_Case { component_id, pc_case_cooler_max_height, pc_case_gpu_max_length }
     317
     318Case_Storage_Form_Factors { component_id, pc_case_storage_form_factor, case_storage_num_slots }
     319
     320Case_Ps_Form_Factors { component_id, pc_case_ps_form_factor }
     321
     322Case_Mobo_Form_Factors { component_id, pc_case_mobo_form_factor }
    277323
    278324Cooler { component_id, cooler_type, cooler_height, cooler_max_tdp_supported }
    279325
     326Cooler_Cpu_Sockets { component_id, cooler_cpu_socket }
     327
    280328Memory_Card { component_id, memory_card_num_slots, memory_card_interface }
    281329
    … …  
    290338Network_Card { component_id, network_card_num_ports, network_card_speed, network_card_interface }
    291339
    292 Pc_Case_Storage_Form_Factor { component_id,  pc_case_storage_form_factor , num_slots }
    293 
    294 Pc_Case_Ps_Form_Factor { component_id, pc_case_ps_form_factor }
    295 
    296 Pc_Case_Mobo_Form_Factor { component_id, pc_case_mobo_form_factor }
    297 
    298 Cooler_Cpu_Socket { component_id, cooler_cpu_socket }
    299 
    300 Build { build_id, user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved }
    301 
    302 Build_Component { build_id, component_id, num_components }
     340Build { build_id, build_user_id, build_name, build_created_at, build_description, build_total_price, build_is_approved }
     341
     342Build_Component { build_id, component_id, build_component_num_components }
    303343
    304344Rating_Build { build_id, user_id, rating_build_value }
    … …  
    306346Favorite_Build { build_id, user_id }
    307347
    308 Review { review_id, build_id, user_id, review_content, review_created_at }
    309 
    310 Suggestion { suggestion_id, user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type }
    311 
    312 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.
    313 
    314 Admins { admin_id (PK and FK to user_id(Users) } - BCNF
    315 
    316 
    317 
    318 
    319 
    320 
    321 
     348Review { review_id, review_build_id, review_user_id, review_content, review_created_at }
     349
     350Suggestions { suggestion_id, suggestion_user_id, suggestion_admin_id, suggestion_link, suggestion_admin_comment, suggestion_description, suggestion_status, suggestion_component_type }
     351