= 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: user_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, release_artist_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, album_id

**RHS only:**\\
email, username, 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, release_artist_type, added_at, song_ordinal

**Both LHS and RHS:**\\
user_id, wishlist_id, release_id
}}}

== Candidate Keys and Primary Key

{{{#!div style="text-align: justify; width: 100%;"
When identifying a candidate key, we first consider the attributes that appear only on the left-hand side (LHS) of the functional dependencies:

**{artist_id, song_id, product_id, order_id,modification_id, album_id}**

These attributes cannot be derived from any other attributes using the given functional dependencies, so they must be included in a candidate key.

Therefore, we initially define:

**K = {order_id, product_id, artist_id, song_id, modification_id, album_id}**

The closure K+ contains all attributes of the universal relation R. Therefore, K is a superkey.  Each attribute in K is necessary because removing any one of them prevents at least one set of attributes of R from being derived. Therefore, K is minimal and the selected primary key for the initial de-normalized relation is:

**(order_id, product_id, artist_id, song_id, modification_id, album_id)**
}}}


== Normal Form of R Before Decomposition

{{{#!div style="text-align: justify; width: 100%;"

{{{R ∈ 1NF}}}: The universal relation R is in 1NF because all attributes contain atomic values and there are no repeating groups.

{{{R ∉ 2NF}}}: The candidate key of R is:

               **K = {order_id, product_id, artist_id, song_id, modification_id, album_id}**

               Since K is a composite key, we must check for partial functional dependencies in order to determine whether R satisfies 2NF.

**Example 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.

}}}

== 2NF decomposition
…

== 3NF decomposition
…

== BCNF if possible
…

== Final result and discussion