Changes between Version 17 and Version 18 of Normalization

Timestamp:

09/01/26 02:10:46 (7 days ago)

Author:

232012

Comment:

--

Normalization

@@ -491 +491 @@
 15. R15 = {**artist_id**, **album_id**, **song_id**, **product_id**, **order_id**, **modification_id**} → **satisfies BCNF**
 }}}
+
+== 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.
+}}}