Changes between Version 15 and Version 16 of Normalization

Timestamp:

08/31/26 19:16:37 (7 days ago)

Author:

232012

Comment:

--

Normalization

@@ -347 +347 @@
 
 }}}
+
+== 3NF
+
+==== 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**.
+
+==== ARTIST
+{{{
+Attributes : artist_id, artist_name, artist_description, artist_photo
+FDs        : FD06 (artist_id → artist_name, artist_description, artist_photo)
+CKs / PK   : {artist_id}
+}}}
+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
+{{{
+Attributes : song_id, song_name, song_duration
+FDs        : FD08 (song_id → song_name, song_duration)
+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**.
+
+==== USER
+{{{
+Attributes : user_id, email, username, password, date_created, shipping_address, telephone_number
+FDs        : FD01 (user_id → email, username, password, date_created, shipping_address, telephone_number)
+             FD02 (email → user_id)
+             FD03 (username → user_id)
+CKs        : {user_id}, {email}, {username}
+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**.
+
+==== ORDER_PRODUCTS
+{{{
+Attributes : order_id, product_id, price_at_purchase, quantity
+FDs        : FD14 ((order_id, product_id) → price_at_purchase, quantity)
+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**.
+
+==== WISHLIST_PRODUCTS
+{{{
+Attributes : wishlist_id, product_id, added_at
+FDs        : FD15 ((wishlist_id, product_id) → added_at)
+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**.
+
+==== RELEASE_ARTISTS
+{{{
+Attributes : release_id, artist_id, release_ordinal, release_artist_type
+FDs        : FD16 ((release_id, artist_id) → release_ordinal, release_artist_type)
+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**.
+
+==== SONG_ARTISTS
+{{{
+Attributes : song_id, artist_id, song_ordinal
+FDs        : FD17 ((song_id, artist_id) → song_ordinal)
+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**.
+==== WISHLIST
+{{{
+Attributes : user_id, wishlist_id
+FDs        : FD12 (wishlist_id → user_id)
+             FD13 (user_id → wishlist_id)
+CKs        : {user_id}, {wishlist_id}
+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**.
+
+== BCNF
+
+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**