Changes between Version 23 and Version 24 of P5

Timestamp:

04/28/26 13:15:34 (4 days ago)

Author:

211099

Comment:

--

P5

@@ -81 +81 @@
 We analyze the global relation R with candidate key:
 CK = {chapter_id, genre_id, list_id, notification_id, suggestion_id, comment_id, status, content_type, suggestion_type, roles, permission_level}
+
 Since CK is composite, we check R for partial dependencies:
+
 chapter_id → chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, story_id, chapter_created_at, chapter_updated_at — partial dependency, violates 2NF
+
 genre_id → genre_name — partial dependency, violates 2NF
+
 list_id → list_name, list_content, is_public, user_id, list_created_at, list_updated_at — partial dependency, violates 2NF
+
 notification_id → notification_content, is_read, type, link, notification_created_at — partial dependency, violates 2NF
+
 suggestion_id → original_text, suggested_text, accepted, suggestion_created_at, applied_at, story_id — partial dependency, violates 2NF
+
 comment_id → comment_content, user_id, story_id, comment_created_at, comment_updated_at — partial dependency, violates 2NF
+
 R is in 1NF but NOT in 2NF. We decompose R by extracting each group of partially dependent attributes into its own relation, keeping the determinant as the primary key. The story and user attributes are resolved transitively through the decomposition of chapter_id → story_id and story_id → user_id.
+
 === 2NF Decomposition
 After decomposition we verify that every resulting relation satisfies 2NF:
+
 USERS(user_id, username, email, user_name, surname, password, user_created_at, user_updated_at) → simple primary key user_id, so it satisfies 2NF.
+
 STORY(story_id, mature_content, short_description, image, story_content, user_id, story_created_at, story_updated_at) → simple primary key story_id, so it satisfies 2NF.
+
 CHAPTER(chapter_id, chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, story_id, chapter_created_at, chapter_updated_at) → composite candidate key {story_id, chapter_number} exists, but all other attributes depend on the full key, not a subset. No partial dependencies, so it satisfies 2NF.
+
 GENRE(genre_id, genre_name) → simple primary key genre_id, so it satisfies 2NF.
+
 READING_LIST(list_id, list_name, list_content, is_public, user_id, list_created_at, list_updated_at) → simple primary key list_id, so it satisfies 2NF.
+
 NOTIFICATION(notification_id, notification_content, is_read, type, link, notification_created_at) → simple primary key notification_id, so it satisfies 2NF.
+
 AI_SUGGESTION(suggestion_id, original_text, suggested_text, accepted, suggestion_created_at, applied_at, story_id) → simple primary key suggestion_id, so it satisfies 2NF.
+
 COMMENT(comment_id, comment_content, user_id, story_id, comment_created_at, comment_updated_at) → simple primary key comment_id, so it satisfies 2NF.
+
 STATUS(story_id, status) → There are no other attributes, so it satisfies 2NF.
+
 CONTENT_TYPE(notification_id, content_type) → There are no other attributes, so it satisfies 2NF.
+
 SUGGESTION_TYPE(suggestion_id, suggestion_type) → There are no other attributes, so it satisfies 2NF.
+
 LIKES(user_id, story_id, like_created_at) → like_created_at depends on {user_id, story_id} together, so it satisfies 2NF.
+
 COLLABORATION(user_id, story_id, collab_created_at) → collab_created_at depends on {user_id, story_id} together, so it satisfies 2NF.
+
 ROLES(user_id, story_id, roles) → There are no other attributes, so it satisfies 2NF.
+
 PERMISSION_LEVEL(user_id, story_id, permission_level) → There are no other attributes, so it satisfies 2NF.
+
 HAS_GENRE(story_id, genre_id) → There are no other attributes, so it satisfies 2NF.
+
 READING_LIST_ITEMS(list_id, story_id, added_at) → added_at depends on {list_id, story_id} together, so it satisfies 2NF.
+
 NOTIFY(user_id, story_id, notification_id) → There are no other attributes, so it satisfies 2NF.
+
 NEED_APPROVAL(suggestion_id, story_id, chapter_id) → There are no other attributes, so it satisfies 2NF.
+
 === Lossless Join Verification
+
 Step 1: Decompose R → USERS + R₁ — common attribute: user_id → primary key of USERS 
+
 R₁ = All attributes of R, except: {username, email, user_name, surname, password, user_created_at, user_updated_at}
 Step 2: Decompose R₁ → STORY + R₂ — common attribute: story_id → primary key of STORY 
+
 R₂ = All attributes of R, except: {username, email, user_name, surname, password, user_created_at, user_updated_at, mature_content, short_description, image, story_content, story_created_at, story_updated_at}
 Step 3: Decompose R₂ → CHAPTER + R₃ — common attribute: chapter_id → primary key of CHAPTER 
+
 R₃ = All attributes of R, except: {username, email, user_name, surname, password, user_created_at, user_updated_at, mature_content, short_description, image, story_content, story_created_at, story_updated_at, chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, chapter_created_at, chapter_updated_at}
 Step 4: Decompose R₃ → GENRE + R₄ — common attribute: genre_id → primary key of GENRE 
+
 R₄ = All attributes of R, except: {username, email, user_name, surname, password, user_created_at, user_updated_at, mature_content, short_description, image, story_content, story_created_at, story_updated_at, chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, chapter_created_at, chapter_updated_at, genre_name}
 Step 5: Decompose R₄ → READING_LIST + R₅ — common attribute: list_id → primary key of READING_LIST 
+
 R₅ = All attributes of R, except: {username, email, user_name, surname, password, user_created_at, user_updated_at, mature_content, short_description, image, story_content, story_created_at, story_updated_at, chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, chapter_created_at, chapter_updated_at, genre_name, list_name, list_content, is_public, list_created_at, list_updated_at}
 Step 6: Decompose R₅ → NOTIFICATION + R₆ — common attribute: notification_id → primary key of NOTIFICATION 
+
 R₆ = All attributes of R, except: {username, email, user_name, surname, password, user_created_at, user_updated_at, mature_content, short_description, image, story_content, story_created_at, story_updated_at, chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, chapter_created_at, chapter_updated_at, genre_name, list_name, list_content, is_public, list_created_at, list_updated_at, notification_content, is_read, type, link, notification_created_at}
 Step 7: Decompose R₆ → AI_SUGGESTION + R₇ — common attribute: suggestion_id → primary key of AI_SUGGESTION 
+
 R₇ = All attributes of R, except: {username, email, user_name, surname, password, user_created_at, user_updated_at, mature_content, short_description, image, story_content, story_created_at, story_updated_at, chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, chapter_created_at, chapter_updated_at, genre_name, list_name, list_content, is_public, list_created_at, list_updated_at, notification_content, is_read, type, link, notification_created_at, original_text, suggested_text, accepted, suggestion_created_at, applied_at}
 Step 8: Decompose R₇ → COMMENT + R₈ — common attribute: comment_id → primary key of COMMENT 
+
 R₈ = All attributes of R, except: {username, email, user_name, surname, password, user_created_at, user_updated_at, mature_content, short_description, image, story_content, story_created_at, story_updated_at, chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, chapter_created_at, chapter_updated_at, genre_name, list_name, list_content, is_public, list_created_at, list_updated_at, notification_content, is_read, type, link, notification_created_at, original_text, suggested_text, accepted, suggestion_created_at, applied_at, comment_content, comment_created_at, comment_updated_at}
 Step 9: Decompose R₈ → STATUS + R₉ — common attribute: story_id → primary key of STORY 
+
 R₉ = All attributes of R, except: {... same as R₈ ..., status}
 Step 10: Decompose R₉ → CONTENT_TYPE + R₁₀ — common attribute: notification_id → primary key of NOTIFICATION 
+
 R₁₀ = All attributes of R, except: {... same as R₉ ..., content_type}
 Step 11: Decompose R₁₀ → SUGGESTION_TYPE + R₁₁ — common attribute: suggestion_id → primary key of AI_SUGGESTION 
+
 R₁₁ = All attributes of R, except: {... same as R₁₀ ..., suggestion_type}
 Step 12: Decompose R₁₁ → LIKES + R₁₂ — common attributes: {user_id, story_id} → primary key of LIKES 
+
 R₁₂ = All attributes of R, except: {... same as R₁₁ ..., like_created_at}
 Step 13: Decompose R₁₂ → COLLABORATION + R₁₃ — common attributes: {user_id, story_id} → primary key of COLLABORATION 
+
 R₁₃ = All attributes of R, except: {... same as R₁₂ ..., collab_created_at}
 Step 14: Decompose R₁₃ → ROLES + R₁₄ — common attributes: {user_id, story_id} → primary key of ROLES 
+
 R₁₄ = All attributes of R, except: {... same as R₁₃ ..., roles}
 Step 15: Decompose R₁₄ → PERMISSION_LEVEL + R₁₅ — common attributes: {user_id, story_id} → primary key of PERMISSION_LEVEL 
+
 R₁₅ = All attributes of R, except: {... same as R₁₄ ..., permission_level
 Step 16: Decompose R₁₅ → HAS_GENRE + R₁₆ — common attributes: {story_id, genre_id} → primary key of HAS_GENRE 
+
 R₁₆ = All attributes of R, except: {... same as R₁₅ ..., genre_id} 
 Step 17: Decompose R₁₆ → READING_LIST_ITEMS + R₁₇ — common attributes: {list_id, story_id} → primary key of READING_LIST_ITEMS 
+
 R₁₇ = All attributes of R, except: {... same as R₁₆ ..., added_at
 Step 18: Decompose R₁₇ → NOTIFY + R₁₈ — common attributes: {user_id, story_id, notification_id} → primary key of NOTIFY 
+
 R₁₈ = {suggestion_id, story_id, chapter_id} — only the attributes of NEED_APPROVAL remain
 Step 19: Decompose R₁₈ → NEED_APPROVAL (final relation) — common attributes: {suggestion_id, story_id, chapter_id} → primary key of NEED_APPROVAL 
+
 R₁₉ = ∅ — all attributes fully decomposed 
+
 At every step the common attributes form a primary key in the extracted relation, so the entire decomposition is lossless and no information is lost.
+
 Since there are no partial dependencies in any of the resulting tables, this schema satisfies 2NF.