= Normalization == De-normalized Database Form First, we combine every attribute in the entire model into one single relation - **Universal Relation R**. {{{ R( 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, title, record_label, genre, release_date, cover_photo, album_id, duration, 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 ) }}} == Functional Dependencies {{{ FD01: user_id → email, username, password, date_created, shipping_address, telephone_number FD02: email → user_id FD03: username → user_id FD04: admin_id → admin_type, discount_percentage FD05: user_id → points_collected FD06: artist_id → artist_name, artist_description, artist_photo FD07: release_id → title, record_label, genre, release_date, cover_photo, duration FD08: song_id → song_name, song_duration FD09: product_id → release_id, format, price, product_description, stock FD10: order_id → user_id, payment_method, purchase_date, points_earned, points_used, status FD11: modification_id → admin_id, date_modified, type_of_modification, discount FD12: wishlist_id → user_id FD13: user_id → wishlist_id FD14: (order_id, product_id) → price_at_purchase, quantity FD15: (wishlist_id, product_id) → added_at FD16: (release_id, artist_id) → release_ordinal, type FD17: (song_id, artist_id) → song_ordinal }}} {{{#!div style="text-align: justify; width: 100%;" **LHS only:** artist_id, song_id, product_id, order_id, modification_id **RHS only:** password, date_created, shipping_address, telephone_number, admin_type, discount_percentage, points_collected, artist_name, artist_description, artist_photo, title, record_label, genre, release_date, cover_photo, duration, song_name, song_duration, format, price, product_description, stock, payment_method, purchase_date, points_earned, points_used, status, date_modified, type_of_modification, discount, price_at_purchase, quantity, release_ordinal, type, added_at, song_ordinal **Both LHS and RHS:** user_id, email, username, admin_id, wishlist_id, release_id **Neither LHS nor RHS:** album_id }}} == Candidate Keys and Primary Key {{{#!div style="text-align: justify; width: 100%;" When identifying a candidate key, we first consider the attributes that cannot be derived from other attributes using the given functional dependencies. The attributes that must be included are: **{artist_id, song_id, product_id, order_id, modification_id, album_id}** `album_id` is included because it does not appear on the right-hand side of any functional dependency, meaning that it cannot be derived from any other attribute. Therefore, we define: **K = {order_id, product_id, artist_id, song_id, modification_id, album_id}** To verify that K is a superkey, we calculate its closure. We start with: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id}** Using: `order_id → user_id, payment_method, purchase_date, points_earned, points_used, status` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status}** Using: `user_id → email, username, password, date_created, shipping_address, telephone_number` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status, email, username, password, date_created, shipping_address, telephone_number}** Using: `user_id → points_collected` and: `user_id → wishlist_id` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status, email, username, password, date_created, shipping_address, telephone_number, points_collected, wishlist_id}** Using: `product_id → release_id, format, price, product_description, stock` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status, email, username, password, date_created, shipping_address, telephone_number, points_collected, wishlist_id, release_id, format, price, product_description, stock}** Using: `release_id → title, record_label, genre, release_date, cover_photo, duration` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status, email, username, password, date_created, shipping_address, telephone_number, points_collected, wishlist_id, release_id, format, price, product_description, stock, title, record_label, genre, release_date, cover_photo, duration}** Using: `artist_id → artist_name, artist_description, artist_photo` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status, email, username, password, date_created, shipping_address, telephone_number, points_collected, wishlist_id, release_id, format, price, product_description, stock, title, record_label, genre, release_date, cover_photo, duration, artist_name, artist_description, artist_photo}** Using: `song_id → song_name, song_duration` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status, email, username, password, date_created, shipping_address, telephone_number, points_collected, wishlist_id, release_id, format, price, product_description, stock, title, record_label, genre, release_date, cover_photo, duration, artist_name, artist_description, artist_photo, song_name, song_duration}** Using: `modification_id → admin_id, date_modified, type_of_modification, discount` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status, email, username, password, date_created, shipping_address, telephone_number, points_collected, wishlist_id, release_id, format, price, product_description, stock, title, record_label, genre, release_date, cover_photo, duration, artist_name, artist_description, artist_photo, song_name, song_duration, admin_id, date_modified, type_of_modification, discount}** Using the corrected dependency: `admin_id → admin_type, discount_percentage` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status, email, username, password, date_created, shipping_address, telephone_number, points_collected, wishlist_id, release_id, format, price, product_description, stock, title, record_label, genre, release_date, cover_photo, duration, artist_name, artist_description, artist_photo, song_name, song_duration, admin_id, date_modified, type_of_modification, discount, admin_type, discount_percentage}** Since both `order_id` and `product_id` are now present, using: `(order_id, product_id) → price_at_purchase, quantity` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status, email, username, password, date_created, shipping_address, telephone_number, points_collected, wishlist_id, release_id, format, price, product_description, stock, title, record_label, genre, release_date, cover_photo, duration, artist_name, artist_description, artist_photo, song_name, song_duration, admin_id, date_modified, type_of_modification, discount, admin_type, discount_percentage, price_at_purchase, quantity}** Using: `(wishlist_id, product_id) → added_at` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status, email, username, password, date_created, shipping_address, telephone_number, points_collected, wishlist_id, release_id, format, price, product_description, stock, title, record_label, genre, release_date, cover_photo, duration, artist_name, artist_description, artist_photo, song_name, song_duration, admin_id, date_modified, type_of_modification, discount, admin_type, discount_percentage, price_at_purchase, quantity, added_at}** Using: `(release_id, artist_id) → release_ordinal, type` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status, email, username, password, date_created, shipping_address, telephone_number, points_collected, wishlist_id, release_id, format, price, product_description, stock, title, record_label, genre, release_date, cover_photo, duration, artist_name, artist_description, artist_photo, song_name, song_duration, admin_id, date_modified, type_of_modification, discount, admin_type, discount_percentage, price_at_purchase, quantity, added_at, release_ordinal, type}** Using: `(song_id, artist_id) → song_ordinal` we obtain: **K+ = {order_id, product_id, artist_id, song_id, modification_id, album_id, user_id, payment_method, purchase_date, points_earned, points_used, status, email, username, password, date_created, shipping_address, telephone_number, points_collected, wishlist_id, release_id, format, price, product_description, stock, title, record_label, genre, release_date, cover_photo, duration, artist_name, artist_description, artist_photo, song_name, song_duration, admin_id, date_modified, type_of_modification, discount, admin_type, discount_percentage, price_at_purchase, quantity, added_at, release_ordinal, type, song_ordinal}** Therefore: **K+ = R**, so K is a superkey. Therefore, under the current set of functional dependencies: **K = {order_id, product_id, artist_id, song_id, modification_id, album_id}** is a candidate key of the universal relation R. }}} == 1NF Decomposition {{{#!div style="text-align: justify; width: 100%;" The universal relation R satisfies First Normal Form (1NF) because every attribute contains a single, atomic value for each tuple. None of the attributes contains a set, list, array, or multiple values stored inside a single field. For example, although a release may be associated with multiple artists, this does not result in several artist values being stored in one attribute. Instead, each association between a release and an artist is represented by a separate tuple through the attributes `release_id` and `artist_id`. The same applies to the relationships between songs and artists, albums and songs, orders and products, wishlists and products, and modifications and products. Similarly, attributes such as `email`, `username`, `price`, `quantity`, `genre`, `status`, `release_ordinal`, and `song_ordinal` each contain one indivisible value within a tuple. Therefore, R contains no repeating groups and no non-atomic attributes meaning: {{{R ∈ 1NF}}} }}} == 2NF Decomposition {{{#!div style="text-align: justify; width: 100%;" {{{R ∉ 2NF}}}: Since candidate key of R: **K = {order_id, product_id, artist_id, song_id, modification_id, album_id}** is a composite key, we must check for partial functional dependencies in order to determine whether R satisfies 2NF. **Examples of a 2NF violation:** * {{{product_id → release_id, format, price, product_description, stock}}} * {{{artist_id → artist_name, artist_description, artist_photo}}} * {{{song_id → song_name, song_duration}}} 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, 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_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:** 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:** 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 {{{#!div style="text-align: justify; width: 100%;" 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. ==== 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 {{{ 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**. ==== SONG {{{ Attributes : song_id, song_name, song_duration FDs : FD08 (song_id → song_name, song_duration) CKs / PK : {song_id} }}} 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 {{{ 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 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 {{{ 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. 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 {{{ 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 {{{(wishlist_id, product_id)}}}. There are no other non-key attributes that could form 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 {{{release_ordinal}}} nor {{{release_artist_type}}} determines another attribute within the relation. Therefore, no transitive dependency exists. **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 {{{(song_id, artist_id)}}}. There are no other non-key attributes and therefore no transitive dependency. **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 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 {{{#!div style="text-align: justify; width: 100%;" 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. 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. }}} == Final Normalized Design {{{#!div style="text-align: justify; width: 100%;" After completing the normalization process from 1NF through 2NF and 3NF to BCNF, the following relations represent the final normalized relational design. All resulting relations satisfy BCNF, and no further decomposition is required. {{{ USERS( user_id PK, email UNIQUE, username UNIQUE, password, date_created, shipping_address, telephone_number ) ADMINS( user_id PK, FK → USERS, admin_type, discount_percentage ) CONSUMERS( user_id PK, FK → USERS, points_collected ) ARTISTS( artist_id PK, artist_name, artist_description, artist_photo ) RELEASES( release_id PK, title, record_label, genre, release_date, cover_photo ) ALBUMS( release_id PK, FK → RELEASES ) SINGLE_RELEASES( release_id PK, FK → RELEASES, duration ) SONGS( song_id PK, song_name, song_duration ) PRODUCTS( product_id PK, release_id FK → RELEASES, format, price, product_description, stock ) ORDERS( order_id PK, user_id FK → USERS, payment_method, purchase_date, points_earned, points_used, status ) MODIFICATIONS( modification_id PK, admin_id FK → ADMINS(user_id), date_modified, type_of_modification, discount ) WISHLISTS( wishlist_id PK, user_id FK → USERS, UNIQUE ) ORDER_PRODUCTS( order_id PK, FK → ORDERS, product_id PK, FK → PRODUCTS, price_at_purchase, quantity ) WISHLIST_PRODUCTS( wishlist_id PK, FK → WISHLISTS, product_id PK, FK → PRODUCTS, added_at ) ALBUM_SONGS( album_id PK, FK → ALBUMS(release_id), song_id PK, FK → SONGS ) RELEASE_ARTISTS( release_id PK, FK → RELEASES, artist_id PK, FK → ARTISTS, release_ordinal, release_artist_type ) SONG_ARTISTS( song_id PK, FK → SONGS, artist_id PK, FK → ARTISTS, song_ordinal ) MODIFICATION_PRODUCTS( modification_id PK, FK → MODIFICATIONS, product_id PK, FK → PRODUCTS ) }}} }}} == Conclusion {{{#!div style="text-align: justify; width: 100%;" The final normalized design is very similar to the relational design created in Phase 2. Most of the relations obtained during normalization, such as ''USERS'', ''ARTISTS'', ''SONGS'', ''PRODUCTS'', ''ORDERS'', ''MODIFICATIONS'', and ''WISHLISTS'', already exist in the Phase 2 design. The same is true for the subtype relations ''ADMINS'' and ''CONSUMERS'' and the main many-to-many relations. Some relations, such as ''ALBUM_SONGS'' and ''MODIFICATION_PRODUCTS'', were not directly produced by the functional dependencies because they only contain their key attributes. However, they are kept because they represent important many-to-many relationships in the database. There is also a difference in the handling of releases. During normalization, {{{duration}}} was considered dependent on {{{release_id}}}, while the Phase 2 design stores it only in ''SINGLE_RELEASES''. The Phase 2 structure with ''RELEASES'', ''ALBUMS'', and ''SINGLE_RELEASES'' is kept because it better represents the meaning of the data. Additionally, there is no point in storing the overall length of the album release if it can be calculated by adding up the length of each of its songs. The residual relation R15 is also not included as a physical table. It was useful for the theoretical normalization process, but its six attributes do not represent one meaningful relationship in the actual database. Overall, the normalization process confirms that the Phase 2 design is already well normalized. Therefore, the Phase 2 relational design will be kept for the following phases, and no major restructuring of the database is required. }}}