Context Navigation

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

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

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:

Using:

user_id → points_collected

and:

user_id → wishlist_id

we obtain:

Using:

product_id → release_id, format, price, product_description, stock

we obtain:

Using:

release_id → title, record_label, genre, release_date, cover_photo, duration

we obtain:

Using:

artist_id → artist_name, artist_description, artist_photo

we obtain:

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:

Using the corrected dependency:

admin_id → admin_type, discount_percentage

we obtain:

Since both order_id and product_id are now present, using:

(order_id, product_id) → price_at_purchase, quantity

we obtain:

Using:

(wishlist_id, product_id) → added_at

we obtain:

Using:

(release_id, artist_id) → release_ordinal, type

we obtain:

Using:

(song_id, artist_id) → song_ordinal

we obtain:

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

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

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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.

Last modified 10 days ago Last modified on 09/17/26 22:15:52

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text