Changes between Version 19 and Version 20 of Normalization

Timestamp:

09/15/26 12:20:53 (12 days ago)

Author:

232012

Comment:

--

Normalization

@@ -81 +81 @@
 
 {{{#!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, type, added_at, song_ordinal
-
-**Both LHS and RHS:**\\
-user_id, wishlist_id, release_id
+**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
 }}}
 
@@ -94 +103 @@
 
 {{{#!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:
+
+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}**
 
-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)**
+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.
+
 }}}