Changes between Version 21 and Version 22 of Normalization

Timestamp:

09/15/26 12:58:12 (12 days ago)

Author:

232012

Comment:

--

Normalization

@@ -272 +272 @@
 
 In each case, a proper subset of the candidate key determines non-prime attributes. Therefore, R contains partial functional dependencies
-and is not in 2NF.
-
-==== 1. PRODUCT(product_id, format, price, product_description, stock)
-
-
-{{{
-R1 = R - {release_id, format, price, product_description, stock}
-
-R1 = {user_id, email, username, password, date_created, shipping_address, telephone_number, admin_type, discount_percentage, points_collected,
-      artist_id, artist_name, artist_description, artist_photo, release_id, title, record_label, genre, release_date, cover_photo, duration,
-      album_id, song_id, song_name, song_duration, order_id, payment_method, purchase_date, points_earned, points_used, status, modification_id,
-      admin_id, date_modified, type_of_modification, discount, wishlist_id, quantity, price_at_purchase, added_at, release_ordinal,
-      type, song_ordinal, product_id}
-}}}
-
-* **Lossless join:** The original relation can be reconstructed through a join using {{{product_id}}}.
-
-* **Dependency preservation:** The dependency {{{product_id → release_id, format, price, product_description, stock}}} is preserved in the new ''PRODUCT'' relation.
+and is not in 2NF, requiring a 2NF decomposition.
+
+
+The order of decomposition is not arbitrary. Relations are extracted in an order that ensures that an intermediate determinant remains available until every dependency that requires it has been handled.
+
+In particular:
+
+* {{{release_id}}} must remain available until RELEASE_ARTISTS is extracted, because {{{(release_id, artist_id) → release_ordinal, type}}}.
+* {{{admin_id}}} must remain available until ADMIN is extracted, before it is later removed together with MODIFICATION.
+* {{{user_id}}} must remain available until USER, CONSUMER and WISHLIST are extracted, before it is later removed as part of ORDER.
+* {{{wishlist_id}}} must remain available until WISHLIST_PRODUCTS is extracted.
+* {{{release_id}}} is only removed from the residual relation when PRODUCT is decomposed at the end.
+
+This order therefore preserves the determinants required by later decompositions and avoids removing an attribute before all dependencies involving it have been handled.
+
+
+==== 1. RELEASE(release_id, title, record_label, genre, release_date, cover_photo, duration)
+
+The attributes of RELEASE are determined by {{{release_id}}}:
+
+{{{
+release_id → title, record_label, genre, release_date, cover_photo, duration
+}}}
+
+Although {{{release_id}}} is not itself part of K, it is determined by the proper subset {{{product_id}}} of K:
+
+{{{
+product_id → release_id
+}}}
+
+Therefore, by transitivity:
+
+{{{
+product_id → title, record_label, genre, release_date, cover_photo, duration
+}}}
+
+so these attributes are partially dependent on the candidate key.
+
+{{{
+R1 = R - {title, record_label, genre, release_date, cover_photo, duration}
+
+R1 = {user_id, email, username, password, date_created, shipping_address, telephone_number, admin_id, admin_type, discount_percentage,
+      points_collected, artist_id, artist_name, artist_description, artist_photo, release_id, album_id, song_id, song_name, song_duration, 
+      product_id, format, price, product_description, stock, order_id, payment_method, purchase_date, points_earned, points_used, status,
+      modification_id, date_modified, type_of_modification, discount, wishlist_id, quantity, price_at_purchase, added_at, release_ordinal, type, 
+      song_ordinal}
+}}}
+
+* **Lossless join:** The intersection between RELEASE and R1 is {{{release_id}}}. Since {{{release_id → title, record_label, genre, release_date, cover_photo, duration}}}, {{{release_id}}} functionally determines all attributes of RELEASE. Therefore, joining RELEASE and R1 on {{{release_id}}} reconstructs the original information.
+
+* **Dependency preservation:** FD07 is represented entirely inside RELEASE. Therefore {{{release_id → title, record_label, genre, release_date, cover_photo, duration}}} can still be enforced directly on this relation without joining it to another relation.
 
 
 ==== 2. ARTIST(artist_id, artist_name, artist_description, artist_photo)
 
+The determinant {{{artist_id}}} is a proper subset of K:
+
+{{{
+artist_id → artist_name, artist_description, artist_photo
+}}}
+
+Therefore these attributes are partially dependent on K.
 
 {{{
 R2 = R1 - {artist_name, artist_description, artist_photo}
 
-R2 = {user_id, email, username, password, date_created, shipping_address, telephone_number, admin_type, discount_percentage, points_collected,
-      artist_id, release_id, title, record_label, genre, release_date, cover_photo, duration, album_id, song_id, song_name, song_duration,
-      product_id, order_id, payment_method, purchase_date, points_earned, points_used, status, modification_id, admin_id, date_modified,
-      type_of_modification, discount, wishlist_id, quantity, price_at_purchase, added_at, release_ordinal, type, song_ordinal}
-}}}
-
-* **Lossless join:** The original relation can be reconstructed through a join using {{{artist_id}}}.
-
-* **Dependency preservation:** The dependency {{{artist_id → artist_name, artist_description, artist_photo}}} is preserved in the new ''ARTIST'' relation.
-
-
-==== 3. RELEASE(release_id, title, record_label, genre, release_date, cover_photo, duration)
-
-{{{
-R3 = R2 - {title, record_label, genre, release_date, cover_photo, duration}
-
-R3 = {user_id, email, username, password, date_created, shipping_address, telephone_number, admin_type, discount_percentage, points_collected,
-      artist_id, release_id, album_id, song_id, song_name, song_duration, product_id, order_id, payment_method, purchase_date,
-      points_earned, points_used, status, modification_id, admin_id, date_modified, type_of_modification, discount, wishlist_id,
-      quantity, price_at_purchase, added_at, release_ordinal, type, song_ordinal}
-}}}
-
-* **Lossless join:** The original relation can be reconstructed through a join using {{{release_id}}}.
-
-* **Dependency preservation:** The dependency {{{release_id → title, record_label, genre, release_date, cover_photo}}} is preserved in the new ''RELEASE'' relation.
-
-==== 4. SONG(song_id, song_name, song_duration)
-
-{{{
-R4 = R3 - {song_name, song_duration}
-
-R4 = {user_id, email, username, password, date_created, shipping_address, telephone_number, admin_type, discount_percentage, points_collected,
-      artist_id, release_id, album_id, song_id, product_id, order_id, payment_method, purchase_date, points_earned, points_used, status,
-      modification_id, admin_id, date_modified, type_of_modification, discount, wishlist_id, quantity, price_at_purchase, added_at,
+R2 = {user_id, email, username, password, date_created, shipping_address, telephone_number, admin_id, admin_type, discount_percentage, 
+      points_collected, artist_id, release_id, album_id, song_id, song_name, song_duration, product_id, format, price, product_description, stock,
+      order_id, payment_method, purchase_date, points_earned, points_used, status, modification_id, date_modified, type_of_modification, discount,
+      wishlist_id, quantity, price_at_purchase, added_at, release_ordinal, type, song_ordinal}
+}}}
+
+* **Lossless join:** ARTIST and R2 intersect on {{{artist_id}}}. Since {{{artist_id}}} is the key of ARTIST and determines all its other attributes, the original relation can be reconstructed by joining on {{{artist_id}}} without creating spurious tuples.
+
+* **Dependency preservation:** FD06 is fully contained in ARTIST, so {{{artist_id → artist_name, artist_description, artist_photo}}} remains directly enforceable.
+
+
+==== 3. SONG(song_id, song_name, song_duration)
+
+{{{
+song_id → song_name, song_duration
+}}}
+
+Since {{{song_id}}} is a proper subset of K, this is a partial dependency.
+
+{{{
+R3 = R2 - {song_name, song_duration}
+
+R3 = {user_id, email, username, password, date_created, shipping_address, telephone_number, admin_id, admin_type, discount_percentage, 
+      points_collected, artist_id, release_id, album_id, song_id, product_id, format, price, product_description, stock, order_id, payment_method, 
+      purchase_date, points_earned, points_used, status, modification_id, date_modified, type_of_modification, discount, wishlist_id, quantity, 
+      price_at_purchase, added_at, release_ordinal, type, song_ordinal}
+}}}
+
+* **Lossless join:** SONG and R3 intersect on {{{song_id}}}. Since {{{song_id → song_name, song_duration}}}, the common attribute determines the complete SONG relation. The decomposition is therefore lossless.
+
+* **Dependency preservation:** FD08 is preserved entirely in SONG and can be checked without performing a join.
+
+
+==== 4. ADMIN(admin_id, admin_type, discount_percentage)
+
+ADMIN is extracted before MODIFICATION because {{{admin_id}}} is needed here and will later be removed from the residual relation when FD11 is decomposed.
+
+{{{
+admin_id → admin_type, discount_percentage
+}}}
+
+Furthermore:
+
+{{{
+modification_id → admin_id
+}}}
+
+and {{{modification_id}}} is a proper subset of K. Therefore the admin attributes are ultimately determined by a proper subset of K.
+
+{{{
+R4 = R3 - {admin_type, discount_percentage}
+
+R4 = {user_id, email, username, password, date_created, shipping_address, telephone_number, admin_id, points_collected, artist_id, release_id, 
+      album_id, song_id, product_id, format, price, product_description, stock, order_id, payment_method, purchase_date, points_earned, points_used,
+      status, modification_id, date_modified, type_of_modification, discount, wishlist_id, quantity, price_at_purchase, added_at, release_ordinal, 
+      type, song_ordinal}
+}}}
+
+* **Lossless join:** ADMIN and R4 intersect on {{{admin_id}}}. Since {{{admin_id → admin_type, discount_percentage}}}, the common attribute determines the whole ADMIN relation, making the decomposition lossless.
+
+* **Dependency preservation:** FD04 is fully contained in ADMIN and therefore remains directly enforceable.
+
+
+==== 5. CONSUMER(user_id, points_collected)
+
+{{{
+user_id → points_collected
+}}}
+
+Since:
+
+{{{
+order_id → user_id
+}}}
+
+and {{{order_id}}} is a proper subset of K, {{{points_collected}}} is indirectly dependent on only part of K.
+
+{{{
+R5 = R4 - {points_collected}
+
+R5 = {user_id, email, username, password, date_created, shipping_address, telephone_number, admin_id, artist_id, release_id, album_id, song_id,
+      product_id, format, price, product_description, stock, order_id, payment_method, purchase_date, points_earned, points_used, status,
+      modification_id, date_modified, type_of_modification, discount, wishlist_id, quantity, price_at_purchase, added_at, release_ordinal, type, 
+      song_ordinal}
+}}}
+
+* **Lossless join:** CONSUMER and R5 intersect on {{{user_id}}}. Since {{{user_id → points_collected}}}, the shared attribute determines all of CONSUMER.
+
+* **Dependency preservation:** FD05 is preserved directly in CONSUMER.
+
+
+==== 6. USER(user_id, email, username, password, date_created, shipping_address, telephone_number)
+
+USER is extracted before ORDER because ORDER will later remove {{{user_id}}} from the residual relation.
+
+{{{
+user_id → email, username, password, date_created, shipping_address, telephone_number
+email → user_id
+username → user_id
+}}}
+
+Because {{{order_id → user_id}}} and {{{order_id}}} is a proper subset of K, the USER attributes are also ultimately determined by a proper subset of K.
+
+{{{
+R6 = R5 - {email, username, password, date_created, shipping_address, telephone_number}
+
+R6 = {user_id, admin_id, artist_id, release_id, album_id, song_id, product_id, format, price, product_description, stock, order_id, payment_method,
+      purchase_date, points_earned, points_used, status, modification_id, date_modified, type_of_modification, discount, wishlist_id, quantity, 
+      price_at_purchase, added_at, release_ordinal, type, song_ordinal}
+}}}
+
+* **Lossless join:** USER and R6 intersect on {{{user_id}}}. Since {{{user_id}}} determines every other attribute of USER, the decomposition is lossless.
+
+* **Dependency preservation:** FD01, FD02 and FD03 are all contained in USER. Therefore both the primary identifier {{{user_id}}} and the alternate candidate keys {{{email}}} and {{{username}}} retain their functional dependencies.
+
+
+==== 7. ORDER_PRODUCTS(order_id, product_id, price_at_purchase, quantity)
+
+{{{
+(order_id, product_id) → price_at_purchase, quantity
+}}}
+
+The determinant {{{(order_id, product_id)}}} is a proper subset of K, so the dependency is partial.
+
+{{{
+R7 = R6 - {price_at_purchase, quantity}
+
+R7 = {user_id, admin_id, artist_id, release_id, album_id, song_id, product_id, format, price, product_description, stock, order_id, payment_method, 
+      purchase_date, points_earned, points_used, status, modification_id, date_modified, type_of_modification, discount, wishlist_id, added_at, 
       release_ordinal, type, song_ordinal}
 }}}
 
-* **Lossless join:** The original relation can be reconstructed through a join using {{{song_id}}}.
-
-* **Dependency preservation:** The dependency {{{song_id → song_name, song_duration}}} is preserved in the new ''SONG'' relation.
-
-==== 5. MODIFICATION(modification_id, admin_id, date_modified, type_of_modification, discount)
-
-{{{
-R5 = R4 - {admin_id, date_modified, type_of_modification, discount}
-
-R5 = {user_id, email, username, password, date_created, shipping_address, telephone_number, admin_type, discount_percentage, points_collected,
-      artist_id, release_id, album_id, song_id, product_id, order_id, payment_method, purchase_date, points_earned, points_used, status,
-      modification_id, wishlist_id, quantity, price_at_purchase, added_at, release_ordinal, type, song_ordinal}
-}}}
-
-* **Lossless join:** The original relation can be reconstructed through a join using {{{modification_id}}}.
-
-* **Dependency preservation:** The dependency {{{modification_id → admin_id, date_modified, type_of_modification, discount}}} is preserved in the new ''MODIFICATION'' relation.
-
-==== 6. ORDER(order_id, user_id, payment_method, purchase_date, points_earned, points_used, status)
-
-{{{
-R6 = R5 - {payment_method, purchase_date, points_earned, points_used, status}
-
-R6 = {user_id, email, username, password, date_created, shipping_address, telephone_number, admin_type, discount_percentage, points_collected,
-      artist_id, release_id, album_id, song_id, product_id, order_id, modification_id, wishlist_id, quantity, price_at_purchase, added_at,
+* **Lossless join:** ORDER_PRODUCTS and R7 intersect on {{{order_id, product_id}}}. This pair determines {{{price_at_purchase}}} and {{{quantity}}}, so the common attributes determine the complete ORDER_PRODUCTS relation. The decomposition is therefore lossless.
+
+* **Dependency preservation:** FD14 remains entirely inside ORDER_PRODUCTS.
+
+
+==== 8. WISHLIST_PRODUCTS(wishlist_id, product_id, added_at)
+
+This relation must be created before WISHLIST removes {{{wishlist_id}}} from the residual relation.
+
+{{{
+(wishlist_id, product_id) → added_at
+}}}
+
+Because {{{order_id → user_id → wishlist_id}}}, and both {{{order_id}}} and {{{product_id}}} belong to a proper subset of K, the determinant can ultimately be obtained from part of the candidate key.
+
+{{{
+R8 = R7 - {added_at}
+
+R8 = {user_id, admin_id, artist_id, release_id, album_id, song_id, product_id, format, price, product_description, stock, order_id, payment_method,
+      purchase_date, points_earned, points_used, status, modification_id, date_modified, type_of_modification, discount, wishlist_id, 
       release_ordinal, type, song_ordinal}
 }}}
 
-* **Lossless join:** The original relation can be reconstructed through a join using {{{order_id}}}.
-
-* **Dependency preservation:** The dependency {{{order_id → user_id, payment_method, purchase_date, points_earned, points_used, status}}} is preserved in the new ''ORDER'' relation.
-
-==== 7. USER(user_id, email, username, password, date_created, shipping_address, telephone_number)
-
-{{{
-R7 = R6 - {email, username, password, date_created, shipping_address, telephone_number}
-
-R7 = {user_id, admin_type, discount_percentage, points_collected, artist_id, release_id, album_id, song_id, product_id, order_id,
-      modification_id, wishlist_id, quantity, price_at_purchase, added_at, release_ordinal, type, song_ordinal}
-}}}
-
-* **Lossless join:** The original relation can be reconstructed through a join using {{{user_id}}}.
-
-* **Dependency preservation:** The dependency {{{user_id → email, username, password, date_created, shipping_address, telephone_number}}} is preserved in the new ''USER'' relation.
-
-==== 8. ORDER_PRODUCTS(order_id, product_id, price_at_purchase, quantity)
-
-{{{
-R8 = R7 - {price_at_purchase, quantity}
-
-R8 = {user_id, admin_type, discount_percentage, points_collected, artist_id, release_id, album_id, song_id, product_id, order_id,
-      modification_id, wishlist_id, added_at, release_ordinal, type, song_ordinal}
-}}}
-
-* **Lossless join:** The original relation can be reconstructed through a join using {{{order_id, product_id}}}.
-
-* **Dependency preservation:** The dependency {{{(order_id, product_id) → price_at_purchase, quantity}}} is preserved in the new ''ORDER_PRODUCTS'' relation.
-
-==== 9. WISHLIST_PRODUCTS(wishlist_id, product_id, added_at)
-
-{{{
-R9 = R8 - {added_at}
-
-R9 = {user_id, admin_type, discount_percentage, points_collected, artist_id, release_id, album_id, song_id, product_id, order_id,
-      modification_id, wishlist_id, release_ordinal, type, song_ordinal}
-}}}
-
-* **Lossless join:** The original relation can be reconstructed through a join using {{{wishlist_id, product_id}}}.
-
-* **Dependency preservation:** The dependency {{{(wishlist_id, product_id) → added_at}}} is preserved in the new ''WISHLIST_PRODUCTS'' relation.
-
-==== 10. RELEASE_ARTISTS(release_id, artist_id, release_ordinal, type)
-
-{{{
-R10 = R9 - {release_ordinal, type}
-
-R10 = {user_id, admin_type, discount_percentage, points_collected, artist_id, release_id, album_id, song_id, product_id, order_id,
-       modification_id, wishlist_id, song_ordinal}
-}}}
-
-* **Lossless join:** The original relation can be reconstructed through a join using {{{release_id, artist_id}}}.
-
-* **Dependency preservation:** The dependency {{{(release_id, artist_id) → release_ordinal, type}}} is preserved in the new ''RELEASE_ARTISTS'' relation.
-
-==== 11. SONG_ARTISTS(song_id, artist_id, song_ordinal)
-
-{{{
-R11 = R10 - {song_ordinal}
-
-R11 = {user_id, admin_type, discount_percentage, points_collected, artist_id, release_id, album_id, song_id, product_id, order_id,
-       modification_id, wishlist_id}
-}}}
-
-* **Lossless join:** The original relation can be reconstructed through a join using {{{song_id, artist_id}}}.
-
-* **Dependency preservation:** The dependency {{{(song_id, artist_id) → song_ordinal}}} is preserved in the new ''SONG_ARTISTS'' relation.
-
-==== 12. ADMIN(user_id, admin_type, discount_percentage)
-
-{{{
-R12 = R11 - {admin_type, discount_percentage}
-
-R12 = {user_id, points_collected, artist_id, release_id, album_id, song_id, product_id, order_id, modification_id, wishlist_id}
-}}}
-
-* **Lossless join:** The original relation can be reconstructed through a join using {{{user_id}}}.
-
-* **Dependency preservation:** The dependency {{{user_id → admin_type, discount_percentage}}} is preserved in the new ''ADMIN'' relation.
-
-==== 13. CONSUMER(user_id, points_collected)
-
-{{{
-R13 = R12 - {points_collected}
-
-R13 = {user_id, artist_id, release_id, album_id, song_id, product_id, order_id, modification_id, wishlist_id}
-}}}
-
-* **Lossless join:** The original relation can be reconstructed through a join using {{{user_id}}}.
-
-* **Dependency preservation:** The dependency {{{user_id → points_collected}}} is preserved in the new ''CONSUMER'' relation.
-
-==== 14. WISHLIST(user_id, wishlist_id)
-
-{{{
-R14 = R13 - {wishlist_id}
-
-R14 = {user_id, artist_id, release_id, album_id, song_id, product_id, order_id, modification_id}
-}}}
-
-* **Lossless join:** The original relation can be reconstructed through a join using {{{user_id}}}.
-
-* **Dependency preservation:** The dependencies {{{user_id → wishlist_id}}} and {{{wishlist_id → user_id}}} are preserved in the new ''WISHLIST'' relation.
-
-==== 15. Removing Remaining Partial Dependencies
-
-Two non-prime attributes remain:
-
-1. {{{order_id → user_id}}}
-2. {{{product_id → release_id}}}
-
-Since {{{order_id}}} and {{{product_id}}} are proper subsets of the candidate key, these are still partial dependencies. However, these dependencies are already represented in ''ORDER'' and ''PRODUCT''.
-
-{{{
-R15 = R14 - {user_id, release_id}
-
-R15 = {artist_id, release_id, album_id, song_id, product_id,
-       order_id, modification_id}
-}}}
-
-* **Lossless join:** The removed {{{user_id}}} can be recovered through the existing ''ORDER'' relation using {{{order_id}}} and the removed {{{release_id}}} can be recovered through the existing ''PRODUCT'' relation using {{{product_id}}}.
-
-* **Dependency preservation:** The dependency {{{order_id → user_id}}} remains preserved in the existing ''ORDER'' relation and the dependency {{{product_id → release_id}}} remains preserved in the existing ''PRODUCT'' relation.
-
-}}}
+* **Lossless join:** WISHLIST_PRODUCTS and R8 intersect on {{{wishlist_id, product_id}}}. These attributes determine {{{added_at}}}, so the common attributes determine the full WISHLIST_PRODUCTS relation.
+
+* **Dependency preservation:** FD15 is preserved entirely inside WISHLIST_PRODUCTS.
+
+
+==== 9. RELEASE_ARTISTS(release_id, artist_id, release_ordinal, type)
+
+This relation is extracted before PRODUCT because PRODUCT will later remove {{{release_id}}} from the residual relation.
+
+{{{
+(release_id, artist_id) → release_ordinal, type
+}}}
+
+Since {{{product_id → release_id}}}, the pair {{{(product_id, artist_id)}}}, which is a proper subset of K, can determine these attributes.
+
+{{{
+R9 = R8 - {release_ordinal, type}
+
+R9 = {user_id, admin_id, artist_id, release_id, album_id, song_id, product_id, format, price, product_description, stock, order_id, payment_method, 
+      purchase_date, points_earned, points_used, status, modification_id, date_modified, type_of_modification, discount, wishlist_id, song_ordinal}
+}}}
+
+* **Lossless join:** RELEASE_ARTISTS and R9 intersect on {{{release_id, artist_id}}}. This composite determinant determines {{{release_ordinal}}} and {{{type}}}, so the decomposition is lossless.
+
+* **Dependency preservation:** FD16 is fully contained in RELEASE_ARTISTS.
+
+
+==== 10. SONG_ARTISTS(song_id, artist_id, song_ordinal)
+
+{{{
+(song_id, artist_id) → song_ordinal
+}}}
+
+The determinant {{{(song_id, artist_id)}}} is a proper subset of K.
+
+{{{
+R10 = R9 - {song_ordinal}
+
+R10 = {user_id, admin_id, artist_id, release_id, album_id, song_id, product_id, format, price, product_description, stock, order_id, payment_method, 
+       purchase_date, points_earned, points_used, status, modification_id, date_modified, type_of_modification, discount, wishlist_id}
+}}}
+
+* **Lossless join:** SONG_ARTISTS and R10 intersect on {{{song_id, artist_id}}}. Since the pair determines {{{song_ordinal}}}, it determines the entire SONG_ARTISTS relation.
+
+* **Dependency preservation:** FD17 remains directly represented inside SONG_ARTISTS.
+
+
+==== 11. WISHLIST(user_id, wishlist_id)
+
+The following two dependencies hold:
+
+{{{
+wishlist_id → user_id
+user_id → wishlist_id
+}}}
+
+WISHLIST is decomposed only after WISHLIST_PRODUCTS because {{{wishlist_id}}} will now be removed from the residual relation.
+
+{{{
+R11 = R10 - {wishlist_id}
+
+R11 = {user_id, admin_id, artist_id, release_id, album_id, song_id, product_id, format, price, product_description, stock, order_id, payment_method,
+       purchase_date, points_earned, points_used, status, modification_id, date_modified, type_of_modification, discount}
+}}}
+
+* **Lossless join:** WISHLIST and R11 intersect on {{{user_id}}}. Since {{{user_id → wishlist_id}}}, {{{user_id}}} determines the complete WISHLIST relation. Therefore the split is lossless.
+
+* **Dependency preservation:** Both FD12 and FD13 remain inside WISHLIST, since both {{{user_id}}} and {{{wishlist_id}}} are contained in the same relation.
+
+
+==== 12. MODIFICATION(modification_id, admin_id, date_modified, type_of_modification, discount)
+
+ADMIN has already been extracted, so {{{admin_id}}} can now safely be removed from the residual relation.
+
+{{{
+modification_id → admin_id, date_modified, type_of_modification, discount
+}}}
+
+Since {{{modification_id}}} is a proper subset of K, this is a partial dependency.
+
+{{{
+R12 = R11 - {admin_id, date_modified, type_of_modification, discount}
+
+R12 = {user_id, artist_id, release_id, album_id, song_id, product_id, format, price, product_description, stock, order_id, payment_method, 
+       purchase_date, points_earned, points_used, status, modification_id}
+}}}
+
+* **Lossless join:** MODIFICATION and R12 intersect on {{{modification_id}}}. Since {{{modification_id}}} determines all remaining MODIFICATION attributes, the original information can be reconstructed without spurious tuples.
+
+* **Dependency preservation:** FD11 is preserved entirely inside MODIFICATION. FD04 has already been preserved separately inside ADMIN.
+
+
+==== 13. ORDER(order_id, user_id, payment_method, purchase_date, points_earned, points_used, status)
+
+USER, CONSUMER and WISHLIST have already been extracted, so {{{user_id}}} can now be removed from the residual relation.
+
+{{{
+order_id → user_id, payment_method, purchase_date, points_earned, points_used, status
+}}}
+
+Since {{{order_id}}} is a proper subset of K, this is a partial dependency.
+
+{{{
+R13 = R12 - {user_id, payment_method, purchase_date, points_earned, points_used, status}
+
+R13 = {artist_id, release_id, album_id, song_id, product_id, format, price, product_description, stock, order_id, modification_id}
+}}}
+
+* **Lossless join:** ORDER and R13 intersect on {{{order_id}}}. Since {{{order_id}}} determines all attributes of ORDER, the decomposition is lossless.
+
+* **Dependency preservation:** FD10 is fully preserved inside ORDER. The dependencies involving {{{user_id}}} have already been preserved in USER, CONSUMER and WISHLIST before {{{user_id}}} was removed from the residual relation.
+
+
+==== 14. PRODUCT(product_id, release_id, format, price, product_description, stock)
+
+PRODUCT is decomposed last among the identifier-bearing relations because {{{release_id}}} was needed earlier for RELEASE and RELEASE_ARTISTS.
+
+{{{
+product_id → release_id, format, price, product_description, stock
+}}}
+
+Since {{{product_id}}} is a proper subset of K, this is a partial dependency.
+
+{{{
+R14 = R13 - {release_id, format, price, product_description, stock}
+
+R14 = {artist_id, album_id, song_id, product_id, order_id, modification_id}
+}}}
+
+* **Lossless join:** PRODUCT and R14 intersect on {{{product_id}}}. Since {{{product_id}}} determines {{{release_id, format, price, product_description, stock}}}, the shared attribute determines the entire PRODUCT relation. Therefore the decomposition is lossless.
+
+* **Dependency preservation:** FD09 is fully contained inside PRODUCT. The dependency involving {{{release_id}}} has already been preserved in RELEASE and RELEASE_ARTISTS before {{{release_id}}} was removed from the residual relation.
+
+
+==== Residual Relation
+
+After all partial dependencies have been removed, the residual relation is:
+
+{{{
+R14 = {artist_id, album_id, song_id, product_id, order_id, modification_id}
+}}}
+
+This residual relation consists only of attributes belonging to the candidate key:
+
+{{{
+K = {order_id, product_id, artist_id, song_id, modification_id, album_id}
+}}}
+
+Therefore there are no non-prime attributes left in R14 that can be partially dependent on a proper subset of the candidate key.
+
+Consequently, the decomposition has removed all partial dependencies from the universal relation and the resulting relations satisfy 2NF.
 
 == 3NF Decomposition
@@ -495 +650 @@
 Listed bellow are the relations obtained after the 2NF decomposition as they are examined for transitive dependencies to determine whether or not a 3NF Decomposition is needed.
 
-==== PRODUCT
-{{{
-Attributes : product_id, release_id, format, price, product_description, stock
-FDs        : FD09 (product_id → release_id, format, price, product_description, stock)
-CKs / PK   : {product_id}
-}}}
-{{{release_id}}} is a non-key attribute and a foreign key to RELEASE, but it does not determine any other attribute within PRODUCT. All non-key attributes depend directly on {{{product_id}}}. **No decomposition needed**.
+==== RELEASE
+{{{
+Attributes : release_id, title, record_label, genre, release_date, cover_photo, duration
+FDs        : FD07 (release_id → title, record_label, genre, release_date, cover_photo, duration)
+CKs / PK   : {release_id}
+}}}
+All non-key attributes depend directly on {{{release_id}}}. There is no non-key attribute that determines another non-key attribute within RELEASE. Therefore, no transitive dependency exists. **No decomposition needed**.
 
 ==== ARTIST
@@ -511 +666 @@
 All non-key attributes depend directly on {{{artist_id}}}. There are no dependencies between non-key attributes. **No decomposition needed**.
 
-==== RELEASE
-{{{
-Attributes : release_id, title, record_label, genre, release_date, cover_photo, duration
-FDs        : FD07 (release_id → title, record_label, genre, release_date, cover_photo, duration)
-CKs / PK   : {release_id}
-}}}
-All non-key attributes depend directly on {{{release_id}}}. There is no non-key attribute that determines another non-key attribute. **No decomposition needed**.
-
 ==== SONG
 {{{
@@ -525 +672 @@
 CKs / PK   : {song_id}
 }}}
-Only two non-key attributes exist, and both depend directly on {{{song_id}}}. No transitive dependency exists. **No decomposition needed**.
-
-==== MODIFICATION
-{{{
-Attributes : modification_id, admin_id, date_modified, type_of_modification, discount
-FDs        : FD11 (modification_id → admin_id, date_modified, type_of_modification, discount)
-CKs / PK   : {modification_id}
-}}}
-{{{admin_id}}} is a non-key attribute and a foreign key to ADMIN. Although {{{admin_id}}} determines attributes in ADMIN, it does not determine any other attribute inside MODIFICATION. Therefore, no transitive dependency exists within this relation. **No decomposition needed**.
-
-==== ORDER
-{{{
-Attributes : order_id, user_id, payment_method, purchase_date, points_earned, points_used, status
-FDs        : FD10 (order_id → user_id, payment_method, purchase_date, points_earned, points_used, status)
-CKs / PK   : {order_id}
-}}}
-{{{user_id}}} is a non-key foreign key to USER, but it does not determine any other attribute within ORDER. All non-key attributes depend directly on {{{order_id}}}. **No decomposition needed**.
+Both non-key attributes depend directly on {{{song_id}}}. Neither non-key attribute determines another attribute within the relation. Therefore, no transitive dependency exists. **No decomposition needed**.
+
+==== ADMIN
+{{{
+Attributes : admin_id, admin_type, discount_percentage
+FDs        : FD04 (admin_id → admin_type, discount_percentage)
+CKs / PK   : {admin_id}
+}}}
+Both non-key attributes depend directly on {{{admin_id}}}. Neither {{{admin_type}}} nor {{{discount_percentage}}} determines another attribute within ADMIN. Therefore, there is no transitive dependency. **No decomposition needed**.
+
+==== CONSUMER
+{{{
+Attributes : user_id, points_collected
+FDs        : FD05 (user_id → points_collected)
+CKs / PK   : {user_id}
+}}}
+There is only one non-key attribute, {{{points_collected}}}, and it depends directly on the candidate key {{{user_id}}}. Therefore, no transitive dependency is possible. **No decomposition needed**.
 
 ==== USER
@@ -552 +699 @@
 PK         : user_id
 }}}
-{{{email}}} and {{{username}}} both determine {{{user_id}}}, but each is itself a complete candidate key. Therefore, neither creates a transitive dependency. Every determinant in the listed functional dependencies is a candidate key. **No decomposition needed**.
+{{{email}}} and {{{username}}} both determine {{{user_id}}}, but each is itself a complete candidate key. Therefore, neither dependency represents a dependency from a non-key attribute to another non-key attribute. Every determinant in the listed functional dependencies is a candidate key and therefore a superkey. **No decomposition needed**.
 
 ==== ORDER_PRODUCTS
@@ -560 +707 @@
 CKs / PK   : {order_id, product_id}
 }}}
-The determinant {{{(order_id, product_id)}}} is the complete composite candidate key. Neither {{{order_id}}} nor {{{product_id}}} determines either non-key attribute independently within this relation. No transitive dependency exists. **No decomposition needed**.
+The determinant {{{(order_id, product_id)}}} is the complete composite candidate key. Both non-key attributes, {{{price_at_purchase}}} and {{{quantity}}}, depend directly on the complete key. Neither {{{order_id}}} nor {{{product_id}}} determines either non-key attribute independently within this relation, and neither non-key attribute determines another non-key attribute. Therefore, no transitive dependency exists. **No decomposition needed**.
 
 ==== WISHLIST_PRODUCTS
@@ -568 +715 @@
 CKs / PK   : {wishlist_id, product_id}
 }}}
-The only non-key attribute, {{{added_at}}}, depends directly on the complete composite candidate key. There are no other non-key attributes that could create a transitive dependency. **No decomposition needed**.
+The only non-key attribute, {{{added_at}}}, depends directly on the complete composite candidate key {{{(wishlist_id, product_id)}}}. There are no other non-key attributes that could form a transitive dependency. **No decomposition needed**.
 
 ==== RELEASE_ARTISTS
@@ -576 +723 @@
 CKs / PK   : {release_id, artist_id}
 }}}
-Both non-key attributes depend directly on the complete composite candidate key {{{(release_id, artist_id)}}}. Neither non-key attribute determines the other. **No decomposition needed**.
+Both non-key attributes depend directly on the complete composite candidate key {{{(release_id, artist_id)}}}. Neither {{{release_ordinal}}} nor {{{release_artist_type}}} determines another attribute within the relation. Therefore, no transitive dependency exists. **No decomposition needed**.
 
 ==== SONG_ARTISTS
@@ -584 +731 @@
 CKs / PK   : {song_id, artist_id}
 }}}
-{{{song_ordinal}}} depends directly on the complete composite candidate key. There are no other non-key attributes and therefore no transitive dependency. **No decomposition needed**.
-==== ADMIN
-{{{
-Attributes : user_id, admin_type, discount_percentage
-FDs        : FD04 (user_id → admin_type, discount_percentage)
-CKs / PK   : {user_id}
-}}}
-Both non-key attributes depend directly on **user_id**. Neither {{{admin_type}}} nor {{{discount_percentage}}} determines another attribute within the relation. **No decomposition needed**.
-==== CONSUMER
-{{{
-Attributes : user_id, points_collected
-FDs        : FD05 (user_id → points_collected)
-CKs / PK   : {user_id}
-}}}
-There is only one non-key attribute, and it depends directly on the candidate key. No transitive dependency is possible. **No decomposition needed**.
+{{{song_ordinal}}} depends directly on the complete composite candidate key {{{(song_id, artist_id)}}}. There are no other non-key attributes and therefore no transitive dependency. **No decomposition needed**.
+
 ==== WISHLIST
 {{{
@@ -607 +741 @@
 PK         : wishlist_id
 }}}
-{{{user_id}}} and {{{wishlist_id}}} determine each other, meaning each attribute is independently a candidate key. Both determinants are therefore superkeys, and no transitive dependency exists. **No decomposition needed**.
-}}}
+{{{user_id}}} and {{{wishlist_id}}} determine each other, meaning that each attribute is independently a candidate key. Consequently, both determinants are superkeys. There is no dependency whose determinant is a non-key attribute, and therefore no transitive dependency exists. **No decomposition needed**.
+
+==== MODIFICATION
+{{{
+Attributes : modification_id, admin_id, date_modified, type_of_modification, discount
+FDs        : FD11 (modification_id → admin_id, date_modified, type_of_modification, discount)
+CKs / PK   : {modification_id}
+}}}
+{{{admin_id}}} is a non-key attribute and a foreign key to ADMIN. Although {{{admin_id}}} determines {{{admin_type}}} and {{{discount_percentage}}} in the separate ADMIN relation, those attributes are not present inside MODIFICATION. Within MODIFICATION, no non-key attribute determines another non-key attribute. All non-key attributes depend directly on {{{modification_id}}}. Therefore, no transitive dependency exists within this relation. **No decomposition needed**.
+
+==== ORDER
+{{{
+Attributes : order_id, user_id, payment_method, purchase_date, points_earned, points_used, status
+FDs        : FD10 (order_id → user_id, payment_method, purchase_date, points_earned, points_used, status)
+CKs / PK   : {order_id}
+}}}
+{{{user_id}}} is a non-key attribute and a foreign key to USER. Although {{{user_id}}} determines attributes in USER, CONSUMER and WISHLIST, those attributes are stored in separate relations and are not present inside ORDER. Within ORDER, every non-key attribute depends directly on {{{order_id}}}, and no non-key attribute determines another non-key attribute. **No decomposition needed**.
+
+==== PRODUCT
+{{{
+Attributes : product_id, release_id, format, price, product_description, stock
+FDs        : FD09 (product_id → release_id, format, price, product_description, stock)
+CKs / PK   : {product_id}
+}}}
+{{{release_id}}} is a non-key attribute and a foreign key to RELEASE. Although {{{release_id}}} determines attributes in RELEASE, those attributes are no longer contained within PRODUCT after the 2NF decomposition. Therefore, {{{release_id}}} does not determine another non-key attribute within PRODUCT. All non-key attributes depend directly on {{{product_id}}}. **No decomposition needed**.
+
+==== R14
+{{{
+Attributes : artist_id, album_id, song_id, product_id, order_id, modification_id
+FDs        : No non-trivial functional dependencies hold within R14 under the current FD set.
+CKs / PK   : {artist_id, album_id, song_id, product_id, order_id, modification_id}
+}}}
+R14 contains only attributes of the candidate key of the original universal relation. There are no non-key attributes and no non-trivial functional dependency in R14 whose determinant is not a superkey. Therefore, no transitive dependency can exist. **No decomposition needed**.
+
+Since none of the relations resulting from the 2NF decomposition contains a transitive dependency from a key through a non-key attribute to another non-key attribute, no additional decomposition is required to achieve Third Normal Form.
+
+Therefore, all relations obtained after the 2NF decomposition already satisfy 3NF.
 
 == BCNF Decomposition
@@ -615 +784 @@
 Listed below are the relations obtained after the 3NF analysis, examined to determine whether every non-trivial functional dependency has a superkey as its determinant and whether further decomposition is required to achieve BCNF.
 
+A relation satisfies BCNF if, for every non-trivial functional dependency {{{X → Y}}}, the determinant {{{X}}} is a superkey of that relation.
+
 The primary keys are bolded.
 
-1. PRODUCT(**product_id**, release_id, format, price, product_description, stock) → **satisfies BCNF**
-2. ARTIST(**artist_id**, artist_name, artist_description, artist_photo) → satisfies BCNF
-3. RELEASE(**release_id**, title, record_label, genre, release_date, cover_photo, duration) → **satisfies BCNF**
-4. SONG(**song_id**, song_name, song_duration) → satisfies BCNF
-5. MODIFICATION(**modification_id**, admin_id, date_modified, type_of_modification, discount) → **satisfies BCNF**
-6. ORDER(**order_id**, user_id, payment_method, purchase_date, points_earned, points_used, status) → **satisfies BCNF**
-7. USER(**user_id**, email, username, password, date_created, shipping_address, telephone_number) → **satisfies BCNF**
-8. ORDER_PRODUCTS(**order_id**, **product_id**, price_at_purchase, quantity) → **satisfies BCNF**
-9. WISHLIST_PRODUCTS(**wishlist_id**, **product_id**, added_at) → **satisfies BCNF**
-10. RELEASE_ARTISTS(**release_id**, **artist_id**, release_ordinal, release_artist_type) → **satisfies BCNF**
-11. SONG_ARTISTS(**song_id**, **artist_id**, song_ordinal) → **satisfies BCNF**
-12. ADMIN(**user_id**, admin_type, discount_percentage) → **satisfies BCNF**
-13. CONSUMER(**user_id**, points_collected) → **satisfies BCNF**
-14. WISHLIST(**wishlist_id**, user_id) → **satisfies BCNF**
-15. R15 = {**artist_id**, **album_id**, **song_id**, **product_id**, **order_id**, **modification_id**} → **satisfies BCNF**
+1. RELEASE(**release_id**, title, record_label, genre, release_date, cover_photo, duration) → **satisfies BCNF**
+
+   The only non-trivial functional dependency is {{{release_id → title, record_label, genre, release_date, cover_photo, duration}}}. Since {{{release_id}}} is the primary key and therefore a superkey, RELEASE satisfies BCNF.
+
+2. ARTIST(**artist_id**, artist_name, artist_description, artist_photo) → **satisfies BCNF**
+
+   The only non-trivial functional dependency is {{{artist_id → artist_name, artist_description, artist_photo}}}. Since {{{artist_id}}} is the primary key and therefore a superkey, ARTIST satisfies BCNF.
+
+3. SONG(**song_id**, song_name, song_duration) → **satisfies BCNF**
+
+   The only non-trivial functional dependency is {{{song_id → song_name, song_duration}}}. Since {{{song_id}}} is the primary key and therefore a superkey, SONG satisfies BCNF.
+
+4. ADMIN(**admin_id**, admin_type, discount_percentage) → **satisfies BCNF**
+
+   The only non-trivial functional dependency is {{{admin_id → admin_type, discount_percentage}}}. Since {{{admin_id}}} is the primary key and therefore a superkey, ADMIN satisfies BCNF.
+
+5. CONSUMER(**user_id**, points_collected) → **satisfies BCNF**
+
+   The only non-trivial functional dependency is {{{user_id → points_collected}}}. Since {{{user_id}}} is the primary key and therefore a superkey, CONSUMER satisfies BCNF.
+
+6. USER(**user_id**, email, username, password, date_created, shipping_address, telephone_number) → **satisfies BCNF**
+
+   The relevant functional dependencies are:
+
+   {{{
+   user_id → email, username, password, date_created, shipping_address, telephone_number
+   email → user_id
+   username → user_id
+   }}}
+
+   {{{user_id}}}, {{{email}}}, and {{{username}}} are all candidate keys of USER. Therefore, every determinant in a non-trivial functional dependency is a superkey, so USER satisfies BCNF.
+
+7. ORDER_PRODUCTS(**order_id**, **product_id**, price_at_purchase, quantity) → **satisfies BCNF**
+
+   The non-trivial functional dependency is {{{(order_id, product_id) → price_at_purchase, quantity}}}. The determinant {{{(order_id, product_id)}}} is the complete composite primary key and therefore a superkey. ORDER_PRODUCTS satisfies BCNF.
+
+8. WISHLIST_PRODUCTS(**wishlist_id**, **product_id**, added_at) → **satisfies BCNF**
+
+   The non-trivial functional dependency is {{{(wishlist_id, product_id) → added_at}}}. The determinant {{{(wishlist_id, product_id)}}} is the complete composite primary key and therefore a superkey. WISHLIST_PRODUCTS satisfies BCNF.
+
+9. RELEASE_ARTISTS(**release_id**, **artist_id**, release_ordinal, release_artist_type) → **satisfies BCNF**
+
+   The non-trivial functional dependency is {{{(release_id, artist_id) → release_ordinal, release_artist_type}}}. The determinant {{{(release_id, artist_id)}}} is the complete composite primary key and therefore a superkey. RELEASE_ARTISTS satisfies BCNF.
+
+10. SONG_ARTISTS(**song_id**, **artist_id**, song_ordinal) → **satisfies BCNF**
+
+   The non-trivial functional dependency is {{{(song_id, artist_id) → song_ordinal}}}. The determinant {{{(song_id, artist_id)}}} is the complete composite primary key and therefore a superkey. SONG_ARTISTS satisfies BCNF.
+
+11. WISHLIST(**wishlist_id**, user_id) → **satisfies BCNF**
+
+   The relevant functional dependencies are:
+
+   {{{
+   wishlist_id → user_id
+   user_id → wishlist_id
+   }}}
+
+   Both {{{wishlist_id}}} and {{{user_id}}} are candidate keys of WISHLIST. Therefore, both determinants are superkeys and WISHLIST satisfies BCNF.
+
+12. MODIFICATION(**modification_id**, admin_id, date_modified, type_of_modification, discount) → **satisfies BCNF**
+
+   The only non-trivial functional dependency within MODIFICATION is {{{modification_id → admin_id, date_modified, type_of_modification, discount}}}. Since {{{modification_id}}} is the primary key and therefore a superkey, MODIFICATION satisfies BCNF.
+
+13. ORDER(**order_id**, user_id, payment_method, purchase_date, points_earned, points_used, status) → **satisfies BCNF**
+
+   The only non-trivial functional dependency within ORDER is {{{order_id → user_id, payment_method, purchase_date, points_earned, points_used, status}}}. Since {{{order_id}}} is the primary key and therefore a superkey, ORDER satisfies BCNF.
+
+14. PRODUCT(**product_id**, release_id, format, price, product_description, stock) → **satisfies BCNF**
+
+   The only non-trivial functional dependency within PRODUCT is {{{product_id → release_id, format, price, product_description, stock}}}. Since {{{product_id}}} is the primary key and therefore a superkey, PRODUCT satisfies BCNF.
+
+15. R14 = {**artist_id**, **album_id**, **song_id**, **product_id**, **order_id**, **modification_id**} → **satisfies BCNF**
+
+   R14 contains only the attributes of the candidate key remaining after the 2NF decomposition. Under the current set of functional dependencies, there are no non-trivial functional dependencies within R14 whose determinant is not a superkey. Therefore, R14 satisfies BCNF.
+
+Since every non-trivial functional dependency in every relation has a superkey as its determinant, all relations obtained after the 3NF analysis already satisfy BCNF.
+
+Therefore, no further decomposition is required.
 }}}