Changes between Version 22 and Version 23 of P5


Ignore:
Timestamp:
04/28/26 13:10:08 (4 days ago)
Author:
211099
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P5

    v22 v23  
    7878The candidate key of R is: CK = {chapter_id, genre_id, list_id, notification_id, suggestion_id, comment_id, status, content_type, suggestion_type, roles, permission_level}
    7979since it is the only combination whose closure contains all attributes of R, and no proper subset of it can determine all attributes of R.
    80 
    8180=== 2NF Check
    8281We analyze the global relation R with candidate key:
    8382CK = {chapter_id, genre_id, list_id, notification_id, suggestion_id, comment_id, status, content_type, suggestion_type, roles, permission_level}
    8483Since CK is composite, we check R for partial dependencies:
    85 
    8684chapter_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
    87 
    8885genre_id → genre_name — partial dependency, violates 2NF
    89 
    9086list_id → list_name, list_content, is_public, user_id, list_created_at, list_updated_at — partial dependency, violates 2NF
    91 
    9287notification_id → notification_content, is_read, type, link, notification_created_at — partial dependency, violates 2NF
    93 
    9488suggestion_id → original_text, suggested_text, accepted, suggestion_created_at, applied_at, story_id — partial dependency, violates 2NF
    95 
    9689comment_id → comment_content, user_id, story_id, comment_created_at, comment_updated_at — partial dependency, violates 2NF
    97 
    9890R 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.
    99 
    10091=== 2NF Decomposition
    10192After decomposition we verify that every resulting relation satisfies 2NF:
    102 
    10393USERS(user_id, username, email, user_name, surname, password, user_created_at, user_updated_at) → simple primary key user_id, so it satisfies 2NF.
    104 
    10594STORY(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.
    106 
    10795CHAPTER(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.
    108 
    10996GENRE(genre_id, genre_name) → simple primary key genre_id, so it satisfies 2NF.
    110 
    11197READING_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.
    112 
    11398NOTIFICATION(notification_id, notification_content, is_read, type, link, notification_created_at) → simple primary key notification_id, so it satisfies 2NF.
    114 
    11599AI_SUGGESTION(suggestion_id, original_text, suggested_text, accepted, suggestion_created_at, applied_at, story_id) → simple primary key suggestion_id, so it satisfies 2NF.
    116 
    117100COMMENT(comment_id, comment_content, user_id, story_id, comment_created_at, comment_updated_at) → simple primary key comment_id, so it satisfies 2NF.
    118 
    119101STATUS(story_id, status) → There are no other attributes, so it satisfies 2NF.
    120 
    121102CONTENT_TYPE(notification_id, content_type) → There are no other attributes, so it satisfies 2NF.
    122 
    123103SUGGESTION_TYPE(suggestion_id, suggestion_type) → There are no other attributes, so it satisfies 2NF.
    124 
    125104LIKES(user_id, story_id, like_created_at) → like_created_at depends on {user_id, story_id} together, so it satisfies 2NF.
    126 
    127105COLLABORATION(user_id, story_id, collab_created_at) → collab_created_at depends on {user_id, story_id} together, so it satisfies 2NF.
    128 
    129106ROLES(user_id, story_id, roles) → There are no other attributes, so it satisfies 2NF.
    130 
    131107PERMISSION_LEVEL(user_id, story_id, permission_level) → There are no other attributes, so it satisfies 2NF.
    132 
    133108HAS_GENRE(story_id, genre_id) → There are no other attributes, so it satisfies 2NF.
    134 
    135109READING_LIST_ITEMS(list_id, story_id, added_at) → added_at depends on {list_id, story_id} together, so it satisfies 2NF.
    136 
    137110NOTIFY(user_id, story_id, notification_id) → There are no other attributes, so it satisfies 2NF.
    138 
    139111NEED_APPROVAL(suggestion_id, story_id, chapter_id) → There are no other attributes, so it satisfies 2NF.
    140 
    141 For a decomposition to be lossless, the common attributes between two joined relations must be a superkey in at least one of them. We verify this for all connected relations:
    142 
    143 CHAPTER ⋈ STORY: common attribute story_id → story_id is the primary key of STORY
    144 
    145 STORY ⋈ USERS: common attribute user_id → user_id is the primary key of USERS
    146 
    147 READING_LIST ⋈ USERS: common attribute user_id → user_id is the primary key of USERS
    148 
    149 AI_SUGGESTION ⋈ STORY: common attribute story_id → story_id is the primary key of STORY
    150 
    151 COMMENT ⋈ STORY: common attribute story_id → story_id is the primary key of STORY
    152 
    153 COMMENT ⋈ USERS: common attribute user_id → user_id is the primary key of USERS
    154 
    155 STATUS ⋈ STORY: common attribute story_id → story_id is the primary key of STORY
    156 
    157 CONTENT_TYPE ⋈ NOTIFICATION: common attribute notification_id → notification_id is the primary key of NOTIFICATION
    158 
    159 SUGGESTION_TYPE ⋈ AI_SUGGESTION: common attribute suggestion_id → suggestion_id is the primary key of AI_SUGGESTION
    160 
    161 LIKES ⋈ USERS: common attribute user_id → user_id is the primary key of USERS
    162 
    163 LIKES ⋈ STORY: common attribute story_id → story_id is the primary key of STORY
    164 
    165 COLLABORATION ⋈ USERS: common attribute user_id → user_id is the primary key of USERS
    166 
    167 COLLABORATION ⋈ STORY: common attribute story_id → story_id is the primary key of STORY
    168 
    169 ROLES ⋈ USERS: common attribute user_id → user_id is the primary key of USERS
    170 
    171 ROLES ⋈ STORY: common attribute story_id → story_id is the primary key of STORY
    172 
    173 PERMISSION_LEVEL ⋈ USERS: common attribute user_id → user_id is the primary key of USERS
    174 
    175 PERMISSION_LEVEL ⋈ STORY: common attribute story_id → story_id is the primary key of STORY
    176 
    177 HAS_GENRE ⋈ STORY: common attribute story_id → story_id is the primary key of STORY
    178 
    179 HAS_GENRE ⋈ GENRE: common attribute genre_id → genre_id is the primary key of GENRE
    180 
    181 READING_LIST_ITEMS ⋈ READING_LIST: common attribute list_id → list_id is the primary key of READING_LIST
    182 
    183 READING_LIST_ITEMS ⋈ STORY: common attribute story_id → story_id is the primary key of STORY
    184 
    185 NOTIFY ⋈ USERS: common attribute user_id → user_id is the primary key of USERS
    186 
    187 NOTIFY ⋈ STORY: common attribute story_id → story_id is the primary key of STORY
    188 
    189 NOTIFY ⋈ NOTIFICATION: common attribute notification_id → notification_id is the primary key of NOTIFICATION
    190 
    191 NEED_APPROVAL ⋈ AI_SUGGESTION: common attribute suggestion_id → suggestion_id is the primary key of AI_SUGGESTION
    192 
    193 NEED_APPROVAL ⋈ STORY: common attribute story_id → story_id is the primary key of STORY
    194 
    195 NEED_APPROVAL ⋈ CHAPTER: common attribute chapter_id → chapter_id is the primary key of CHAPTER
    196 
    197 In every case the common attributes form a primary key in at least one of the joined relations, so all joins are lossless and no information is lost during decomposition.
    198 
     112=== Lossless Join Verification
     113Step 1: Decompose R → USERS + R₁ — common attribute: user_id → primary key of USERS
     114R₁ = All attributes of R, except: {username, email, user_name, surname, password, user_created_at, user_updated_at}
     115Step 2: Decompose R₁ → STORY + R₂ — common attribute: story_id → primary key of STORY
     116R₂ = 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}
     117Step 3: Decompose R₂ → CHAPTER + R₃ — common attribute: chapter_id → primary key of CHAPTER
     118R₃ = 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}
     119Step 4: Decompose R₃ → GENRE + R₄ — common attribute: genre_id → primary key of GENRE
     120R₄ = 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}
     121Step 5: Decompose R₄ → READING_LIST + R₅ — common attribute: list_id → primary key of READING_LIST
     122R₅ = 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}
     123Step 6: Decompose R₅ → NOTIFICATION + R₆ — common attribute: notification_id → primary key of NOTIFICATION
     124R₆ = 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}
     125Step 7: Decompose R₆ → AI_SUGGESTION + R₇ — common attribute: suggestion_id → primary key of AI_SUGGESTION
     126R₇ = 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}
     127Step 8: Decompose R₇ → COMMENT + R₈ — common attribute: comment_id → primary key of COMMENT
     128R₈ = 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}
     129Step 9: Decompose R₈ → STATUS + R₉ — common attribute: story_id → primary key of STORY
     130R₉ = All attributes of R, except: {... same as R₈ ..., status}
     131Step 10: Decompose R₉ → CONTENT_TYPE + R₁₀ — common attribute: notification_id → primary key of NOTIFICATION
     132R₁₀ = All attributes of R, except: {... same as R₉ ..., content_type}
     133Step 11: Decompose R₁₀ → SUGGESTION_TYPE + R₁₁ — common attribute: suggestion_id → primary key of AI_SUGGESTION
     134R₁₁ = All attributes of R, except: {... same as R₁₀ ..., suggestion_type}
     135Step 12: Decompose R₁₁ → LIKES + R₁₂ — common attributes: {user_id, story_id} → primary key of LIKES
     136R₁₂ = All attributes of R, except: {... same as R₁₁ ..., like_created_at}
     137Step 13: Decompose R₁₂ → COLLABORATION + R₁₃ — common attributes: {user_id, story_id} → primary key of COLLABORATION
     138R₁₃ = All attributes of R, except: {... same as R₁₂ ..., collab_created_at}
     139Step 14: Decompose R₁₃ → ROLES + R₁₄ — common attributes: {user_id, story_id} → primary key of ROLES
     140R₁₄ = All attributes of R, except: {... same as R₁₃ ..., roles}
     141Step 15: Decompose R₁₄ → PERMISSION_LEVEL + R₁₅ — common attributes: {user_id, story_id} → primary key of PERMISSION_LEVEL
     142R₁₅ = All attributes of R, except: {... same as R₁₄ ..., permission_level
     143Step 16: Decompose R₁₅ → HAS_GENRE + R₁₆ — common attributes: {story_id, genre_id} → primary key of HAS_GENRE
     144R₁₆ = All attributes of R, except: {... same as R₁₅ ..., genre_id}
     145Step 17: Decompose R₁₆ → READING_LIST_ITEMS + R₁₇ — common attributes: {list_id, story_id} → primary key of READING_LIST_ITEMS
     146R₁₇ = All attributes of R, except: {... same as R₁₆ ..., added_at
     147Step 18: Decompose R₁₇ → NOTIFY + R₁₈ — common attributes: {user_id, story_id, notification_id} → primary key of NOTIFY
     148R₁₈ = {suggestion_id, story_id, chapter_id} — only the attributes of NEED_APPROVAL remain
     149Step 19: Decompose R₁₈ → NEED_APPROVAL (final relation) — common attributes: {suggestion_id, story_id, chapter_id} → primary key of NEED_APPROVAL
     150R₁₉ = ∅ — all attributes fully decomposed
     151At every step the common attributes form a primary key in the extracted relation, so the entire decomposition is lossless and no information is lost.
    199152Since there are no partial dependencies in any of the resulting tables, this schema satisfies 2NF.
    200153