Changes between Version 25 and Version 26 of Normalization


Ignore:
Timestamp:
08/24/26 16:44:50 (13 days ago)
Author:
211099
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Normalization

    v25 v26  
    66content → story_content, chapter_content, comment_content, notification_content, list_content
    77
    8 created_at → user_created_at, story_created_at, chapter_created_at, list_created_at, notification_created_at, like_created_at, comment_created_at, collab_created_at, suggestion_created_at
     8created_at → user_created_at, story_created_at, chapter_created_at, list_created_at, notification_created_at, comment_created_at, collab_created_at, suggestion_created_at
    99
    1010updated_at → user_updated_at, story_updated_at, chapter_updated_at, list_updated_at, comment_updated_at
    … …  
    1414After these changes, I obtained the following de-normalized relation:
    1515=== Global Relation
     16=== Global Relation
    1617R = {user_id, username, email, user_name, surname, password, user_created_at,
    17 user_updated_at, story_id, mature_content, short_description, image, story_content,
    18 story_created_at, story_updated_at, status, chapter_id, chapter_number, chapter_name,
    19 title, chapter_content, word_count, rating, published_at, view_count, chapter_created_at,
    20 chapter_updated_at, genre_id, genre_name, list_id, list_name, list_content, is_public,
    21 list_created_at, list_updated_at, added_at, notification_id, notification_content, is_read,
    22 type, link, notification_created_at, content_type, suggestion_id, original_text,
    23 suggested_text, accepted, applied_at, suggestion_created_at, suggestion_type, comment_id,
    24 comment_content, comment_created_at, comment_updated_at, roles, permission_level,
    25 collab_created_at, like_created_at}
     18user_updated_at, assigned_at, joined_at, bio, story_id, title, mature_content,
     19short_description, image, story_content, status, story_created_at, story_updated_at,
     20chapter_id, chapter_number, chapter_name, chapter_content, word_count, rating,
     21published_at, view_count, chapter_created_at, chapter_updated_at, genre_id, genre_name,
     22list_id, list_name, list_content, is_public, list_created_at, list_updated_at, added_at,
     23notification_id, notification_content, content_type, is_read, link, notification_created_at,
     24suggestion_id, original_text, suggested_text, suggestion_type, accepted, applied_at,
     25suggestion_created_at, comment_id, comment_content, comment_created_at, comment_updated_at,
     26role, permission_level, collab_created_at, liked_at}
    2627
    2728== Functional Dependencies:
    … …  
    3031- FD2: username → user_id
    3132- FD3: email    → user_id
    32 - FD4: story_id → mature_content, short_description, image, story_content,story_title user_id, story_created_at, story_updated_at
     33- FD4: story_id → title, mature_content, short_description, image, story_content, status, user_id, story_created_at, story_updated_at
    3334- FD5: chapter_id → chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, story_id, chapter_created_at, chapter_updated_at
    3435- FD6: {story_id, chapter_number} → chapter_id
    … …  
    3637- FD8: genre_name → genre_id
    3738- FD9: list_id → list_name, list_content, is_public, user_id, list_created_at, list_updated_at
    38 - FD10: notification_id → notification_content, is_read, recipient_user_id, type, link, notification_created_at
    39 - FD11: suggestion_id → original_text, suggested_text, accepted, suggestion_created_at, applied_at, story_id
    40 - FD12:comment_id → comment_content, user_id, story_id, comment_created_at, comment_updated_at
    41 - FD13: {user_id, story_id} → like_created_at
    42 - FD14: {user_id, story_id} → collab_created_at
    43 - FD15:{list_id, story_id} → added_at   
     39- FD10: notification_id → notification_content, content_type, is_read, user_id, story_id, link, notification_created_at
     40- FD11: suggestion_id → original_text, suggested_text, suggestion_type, accepted, suggestion_created_at, applied_at, story_id
     41- FD12: comment_id → comment_content, user_id, story_id, comment_created_at, comment_updated_at
     42- FD13: {user_id, story_id} → liked_at
     43- FD14: {user_id, story_id} → role, permission_level, collab_created_at
     44- FD15: {list_id, story_id} → added_at
    4445
    4546=== Closures
    46 user_id+ = {user_id, username, email, user_name, surname, password,user_created_at, user_updated_at} → Does NOT contain all attributes of R
    47 
    48 username+ = {username, user_id, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
    49 
    50 email+ = {email, user_id, username, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
    51 
    52 story_id+ = {story_id, mature_content, short_description, image, story_content,story_title, user_id, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
    53 
    54 chapter_id+ = {chapter_id, chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, story_id, chapter_created_at, chapter_updated_at, mature_content, short_description, image, story_content, user_id, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
    55 
    56 {story_id, chapter_number}+ = {story_id, chapter_number, chapter_id, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, chapter_created_at, chapter_updated_at, mature_content, short_description, image, story_content, user_id, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
    57 
    58 genre_id+ = {genre_id, genre_name} → Does NOT contain all attributes of R
    59 
    60 genre_name+ = {genre_name, genre_id} → Does NOT contain all attributes of R
    61 
    62 list_id+ = {list_id, list_name, list_content, is_public, user_id, list_created_at, list_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
    63 
    64 notification_id+ = {notification_id, notification_content, is_read, recipient_user_id, type, link, notification_created_at} → Does NOT contain all attributes of R
    65 
    66 suggestion_id+ = {suggestion_id, original_text, suggested_text, accepted, suggestion_created_at, applied_at, story_id,  mature_content, short_description, image, story_content, user_id, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
    67 
    68 comment_id+ = {comment_id, comment_content, user_id, story_id, comment_created_at, comment_updated_at, mature_content, short_description, image, story_content, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
    69 
    70 {user_id, story_id}+ = {user_id, story_id, like_created_at, collab_created_at, mature_content, short_description, image, story_content, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
    71 
    72 {list_id, story_id}+ = {list_id, story_id, added_at, list_name, list_content, is_public, list_created_at, list_updated_at, mature_content, short_description, image, story_content,user_id, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
    73 
    74 {chapter_id, genre_id, list_id, notification_id, suggestion_id, comment_id, status, content_type, suggestion_type, roles, permission_level}+ = {chapter_id, genre_id, list_id, notification_id, suggestion_id, comment_id, status, content_type, suggestion_type, roles, permission_level, chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, story_id, chapter_created_at, chapter_updated_at, mature_content, short_description, image, story_content, user_id, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at, like_created_at, collab_created_at, genre_name, list_name, list_content, is_public, list_created_at, list_updated_at, added_at, notification_content, is_read, recipient_user_id, type, link, notification_created_at, original_text, suggested_text, accepted, suggestion_created_at, applied_at, comment_content, comment_created_at, comment_updated_at} → contains all attributes of R → This is the Candidate Key (Primary Key) of R, this is only candidate key because it covers all attributes of R,it's minimal and no alternative exists(every attribute group is independent).
     47
     48user_id+ = {user_id, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
     49
     50username+ = {username, user_id, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
     51
     52email+ = {email, user_id, username, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
     53
     54story_id+ = {story_id, title, mature_content, short_description, image, story_content, status, user_id, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
     55
     56chapter_id+ = {chapter_id, chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, story_id, chapter_created_at, chapter_updated_at, mature_content, short_description, image, story_content, status, user_id, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
     57
     58{story_id, chapter_number}+ = {story_id, chapter_number, chapter_id, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, chapter_created_at, chapter_updated_at, mature_content, short_description, image, story_content, status, user_id, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
     59
     60genre_id+ = {genre_id, genre_name} → Does NOT contain all attributes of R
     61
     62genre_name+ = {genre_name, genre_id} → Does NOT contain all attributes of R
     63
     64list_id+ = {list_id, list_name, list_content, is_public, user_id, list_created_at, list_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
     65
     66notification_id+ = {notification_id, notification_content, content_type, is_read, user_id, story_id, link, notification_created_at, username, email, user_name, surname, password, user_created_at, user_updated_at, title, mature_content, short_description, image, story_content, status, story_created_at, story_updated_at} → Does NOT contain all attributes of R
     67
     68suggestion_id+ = {suggestion_id, original_text, suggested_text, suggestion_type, accepted, suggestion_created_at, applied_at, story_id, title, mature_content, short_description, image, story_content, status, user_id, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
     69
     70comment_id+ = {comment_id, comment_content, user_id, story_id, comment_created_at, comment_updated_at, title, mature_content, short_description, image, story_content, status, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
     71
     72{user_id, story_id}+ = {user_id, story_id, liked_at, role, permission_level, collab_created_at, title, mature_content, short_description, image, story_content, status, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
     73
     74{list_id, story_id}+ = {list_id, story_id, added_at, list_name, list_content, is_public, list_created_at, list_updated_at, title, mature_content, short_description, image, story_content, status, user_id, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R
     75
     76{chapter_id, genre_id, list_id, notification_id, suggestion_id, comment_id}+ = {chapter_id, genre_id, list_id, notification_id, suggestion_id, comment_id, chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, story_id, chapter_created_at, chapter_updated_at, mature_content, short_description, image, story_content, status, user_id, story_created_at, story_updated_at, username, email, user_name, surname, password, user_created_at, user_updated_at, genre_name, list_name, list_content, is_public, list_created_at, list_updated_at, notification_content, content_type, is_read, link, notification_created_at, original_text, suggested_text, suggestion_type, accepted, suggestion_created_at, applied_at, comment_content, comment_created_at, comment_updated_at, liked_at, role, permission_level, collab_created_at, added_at} → contains all attributes of R except assigned_at, joined_at, bio (which are partial attributes applying only to the respective USER subtype and are not covered by any FD in this set) → This is the minimal Candidate Key of R. Note: story_id and user_id, although present in several individual FDs, are transitively derivable through chapter_id (chapter_id → story_id → user_id) and are therefore redundant as explicit key components.
    7577
    7678=== 1NF Check
     79
    7780Every column in the database holds just one value, so there are no lists or multi-value attributes in the cells. There are no repeating columns, each table has a primary key, and all attributes are atomic, so my relations satisfy 1NF.
    78 The 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}
    79 since it is the only combination whose closure contains all attributes of R, and no proper subset of it can determine all attributes of R.
     81
     82The candidate key of R is: CK = {chapter_id, genre_id, list_id, notification_id, suggestion_id, comment_id}
     83since it is the only combination whose closure contains all attributes of R (except assigned_at, joined_at, and bio, which are partial attributes of the respective USER subtypes and fall outside any FD in this set), and no proper subset of it can determine all attributes of R.
     84
    8085=== 2NF Check
    8186We analyze the global relation R with candidate key:
    82 CK = {chapter_id, genre_id, list_id, notification_id, suggestion_id, comment_id, status, content_type, suggestion_type, roles, permission_level}
     87
     88CK = {chapter_id, genre_id, list_id, notification_id, suggestion_id, comment_id}
    8389
    8490Since CK is composite, we check R for partial dependencies:
    … …  
    9096list_id → list_name, list_content, is_public, user_id, list_created_at, list_updated_at — partial dependency, violates 2NF
    9197
    92 notification_id → notification_content, is_read, type, link, notification_created_at — partial dependency, violates 2NF
    93 
    94 suggestion_id → original_text, suggested_text, accepted, suggestion_created_at, applied_at, story_id — partial dependency, violates 2NF
     98notification_id → notification_content, content_type, is_read, user_id, story_id, link, notification_created_at — partial dependency, violates 2NF
     99
     100suggestion_id → original_text, suggested_text, suggestion_type, accepted, suggestion_created_at, applied_at, story_id — partial dependency, violates 2NF
    95101
    96102comment_id → comment_content, user_id, story_id, comment_created_at, comment_updated_at — partial dependency, violates 2NF
    … …  
    103109USERS(user_id, username, email, user_name, surname, password, user_created_at, user_updated_at) → simple primary key user_id, so it satisfies 2NF.
    104110
    105 STORY(story_id, mature_content, short_description, image, story_content,story_title, user_id, story_created_at, story_updated_at) → simple primary key story_id, so it satisfies 2NF.
     111ADMIN(user_id, assigned_at) → simple primary key user_id, so it satisfies 2NF.
     112
     113REGULAR_USER(user_id, joined_at) → simple primary key user_id, so it satisfies 2NF.
     114
     115WRITER(user_id, bio) → simple primary key user_id, so it satisfies 2NF.
     116
     117STORY(story_id, title, mature_content, short_description, image, story_content, status, user_id, story_created_at, story_updated_at) → simple primary key story_id, so it satisfies 2NF.
    106118
    107119CHAPTER(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.
    … …  
    111123READING_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.
    112124
    113 NOTIFICATION(notification_id, notification_content, is_read, type, link, notification_created_at) → simple primary key notification_id, so it satisfies 2NF.
    114 
    115 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.
     125NOTIFICATION(notification_id, notification_content, content_type, is_read, link, user_id, story_id, notification_created_at) → simple primary key notification_id, so it satisfies 2NF.
     126
     127AI_SUGGESTION(suggestion_id, original_text, suggested_text, suggestion_type, accepted, suggestion_created_at, applied_at, story_id) → simple primary key suggestion_id, so it satisfies 2NF.
    116128
    117129COMMENT(comment_id, comment_content, user_id, story_id, comment_created_at, comment_updated_at) → simple primary key comment_id, so it satisfies 2NF.
    118130
    119 STATUS(story_id, status) → There are no other attributes, so it satisfies 2NF.
    120 
    121 CONTENT_TYPE(notification_id, content_type) → There are no other attributes, so it satisfies 2NF.
    122 
    123 SUGGESTION_TYPE(suggestion_id, suggestion_type) → There are no other attributes, so it satisfies 2NF.
    124 
    125 LIKES(user_id, story_id, like_created_at) → like_created_at depends on {user_id, story_id} together, so it satisfies 2NF.
    126 
    127 COLLABORATION(user_id, story_id, collab_created_at) → collab_created_at depends on {user_id, story_id} together, so it satisfies 2NF.
    128 
    129 ROLES(user_id, story_id, roles) → There are no other attributes, so it satisfies 2NF.
    130 
    131 PERMISSION_LEVEL(user_id, story_id, permission_level) → There are no other attributes, so it satisfies 2NF.
     131LIKES(user_id, story_id, liked_at) → liked_at depends on {user_id, story_id} together, so it satisfies 2NF.
     132
     133COLLABORATION(user_id, story_id, role, permission_level, collab_created_at) → role, permission_level, and collab_created_at all depend on {user_id, story_id} together, not on a subset, so it satisfies 2NF.
    132134
    133135HAS_GENRE(story_id, genre_id) → There are no other attributes, so it satisfies 2NF.
    … …  
    135137READING_LIST_ITEMS(list_id, story_id, added_at) → added_at depends on {list_id, story_id} together, so it satisfies 2NF.
    136138
    137 NOTIFY(user_id, story_id, notification_id) → There are no other attributes, so it satisfies 2NF.
    138 
    139139NEED_APPROVAL(suggestion_id, story_id, chapter_id) → There are no other attributes, so it satisfies 2NF.
    140140
    141141=== Lossless Join Verification
    142142
    143 Step 1: Decompose R → USERS + R₁ — common attribute: user_id → primary key of USERS
    144 
     143Step 1: Decompose R → USERS + R₁ — common attribute: user_id → primary key of USERS
    145144R₁ = All attributes of R, except: {username, email, user_name, surname, password, user_created_at, user_updated_at}
    146 Step 2: Decompose R₁ → STORY + R₂ — common attribute: story_id → primary key of STORY
    147 
    148 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_title, story_created_at, story_updated_at}
    149 Step 3: Decompose R₂ → CHAPTER + R₃ — common attribute: chapter_id → primary key of CHAPTER
    150 
    151 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}
    152 Step 4: Decompose R₃ → GENRE + R₄ — common attribute: genre_id → primary key of GENRE
    153 
    154 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}
    155 Step 5: Decompose R₄ → READING_LIST + R₅ — common attribute: list_id → primary key of READING_LIST
    156 
    157 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}
    158 Step 6: Decompose R₅ → NOTIFICATION + R₆ — common attribute: notification_id → primary key of NOTIFICATION
    159 
    160 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}
    161 Step 7: Decompose R₆ → AI_SUGGESTION + R₇ — common attribute: suggestion_id → primary key of AI_SUGGESTION
    162 
    163 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}
    164 Step 8: Decompose R₇ → COMMENT + R₈ — common attribute: comment_id → primary key of COMMENT
    165 
    166 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}
    167 Step 9: Decompose R₈ → STATUS + R₉ — common attribute: story_id → primary key of STORY
    168 
    169 R₉ = All attributes of R, except: {... same as R₈ ..., status}
    170 Step 10: Decompose R₉ → CONTENT_TYPE + R₁₀ — common attribute: notification_id → primary key of NOTIFICATION
    171 
    172 R₁₀ = All attributes of R, except: {... same as R₉ ..., content_type}
    173 Step 11: Decompose R₁₀ → SUGGESTION_TYPE + R₁₁ — common attribute: suggestion_id → primary key of AI_SUGGESTION
    174 
    175 R₁₁ = All attributes of R, except: {... same as R₁₀ ..., suggestion_type}
    176 Step 12: Decompose R₁₁ → LIKES + R₁₂ — common attributes: {user_id, story_id} → primary key of LIKES
    177 
    178 R₁₂ = All attributes of R, except: {... same as R₁₁ ..., like_created_at}
    179 Step 13: Decompose R₁₂ → COLLABORATION + R₁₃ — common attributes: {user_id, story_id} → primary key of COLLABORATION
    180 
    181 R₁₃ = All attributes of R, except: {... same as R₁₂ ..., collab_created_at}
    182 Step 14: Decompose R₁₃ → ROLES + R₁₄ — common attributes: {user_id, story_id} → primary key of ROLES
    183 
    184 R₁₄ = All attributes of R, except: {... same as R₁₃ ..., roles}
    185 Step 15: Decompose R₁₄ → PERMISSION_LEVEL + R₁₅ — common attributes: {user_id, story_id} → primary key of PERMISSION_LEVEL
    186 
    187 R₁₅ = All attributes of R, except: {... same as R₁₄ ..., permission_level
    188 Step 16: Decompose R₁₅ → HAS_GENRE + R₁₆ — common attributes: {story_id, genre_id} → primary key of HAS_GENRE
    189 
    190 R₁₆ = All attributes of R, except: {... same as R₁₅ ..., genre_id}
    191 Step 17: Decompose R₁₆ → READING_LIST_ITEMS + R₁₇ — common attributes: {list_id, story_id} → primary key of READING_LIST_ITEMS
    192 
    193 R₁₇ = All attributes of R, except: {... same as R₁₆ ..., added_at
    194 Step 18: Decompose R₁₇ → NOTIFY + R₁₈ — common attributes: {user_id, story_id, notification_id} → primary key of NOTIFY
    195 
    196 R₁₈ = {suggestion_id, story_id, chapter_id} — only the attributes of NEED_APPROVAL remain
    197 Step 19: Decompose R₁₈ → NEED_APPROVAL (final relation) — common attributes: {suggestion_id, story_id, chapter_id} → primary key of NEED_APPROVAL
    198 
    199 R₁₉ = ∅ — all attributes fully decomposed
     145
     146Step 2: Decompose R₁ → ADMIN + R₂ — common attribute: user_id → primary key of ADMIN
     147R₂ = All attributes of R₁, except: {assigned_at}
     148
     149Step 3: Decompose R₂ → REGULAR_USER + R₃ — common attribute: user_id → primary key of REGULAR_USER
     150R₃ = All attributes of R₂, except: {joined_at}
     151
     152Step 4: Decompose R₃ → WRITER + R₄ — common attribute: user_id → primary key of WRITER
     153R₄ = All attributes of R₃, except: {bio}
     154
     155Step 5: Decompose R₄ → STORY + R₅ — common attribute: story_id → primary key of STORY
     156R₅ = All attributes of R₄, except: {title, mature_content, short_description, image, story_content, status, story_created_at, story_updated_at}
     157
     158Step 6: Decompose R₅ → CHAPTER + R₆ — common attribute: chapter_id → primary key of CHAPTER
     159R₆ = All attributes of R₅, except: {chapter_number, chapter_name, chapter_content, word_count, rating, published_at, view_count, chapter_created_at, chapter_updated_at}
     160(note: title is shared with STORY and is already removed in Step 5; it appears once in R as a single attribute name, resolved together with the STORY decomposition)
     161
     162Step 7: Decompose R₆ → GENRE + R₇ — common attribute: genre_id → primary key of GENRE
     163R₇ = All attributes of R₆, except: {genre_name}
     164
     165Step 8: Decompose R₇ → READING_LIST + R₈ — common attribute: list_id → primary key of READING_LIST
     166R₈ = All attributes of R₇, except: {list_name, list_content, is_public, list_created_at, list_updated_at}
     167
     168Step 9: Decompose R₈ → NOTIFICATION + R₉ — common attribute: notification_id → primary key of NOTIFICATION
     169R₉ = All attributes of R₈, except: {notification_content, content_type, is_read, link, notification_created_at}
     170
     171Step 10: Decompose R₉ → AI_SUGGESTION + R₁₀ — common attribute: suggestion_id → primary key of AI_SUGGESTION
     172R₁₀ = All attributes of R₉, except: {original_text, suggested_text, suggestion_type, accepted, suggestion_created_at, applied_at}
     173
     174Step 11: Decompose R₁₀ → COMMENT + R₁₁ — common attribute: comment_id → primary key of COMMENT
     175R₁₁ = All attributes of R₁₀, except: {comment_content, comment_created_at, comment_updated_at}
     176
     177Step 12: Decompose R₁₁ → LIKES + R₁₂ — common attributes: {user_id, story_id} → primary key of LIKES
     178R₁₂ = All attributes of R₁₁, except: {liked_at}
     179
     180Step 13: Decompose R₁₂ → COLLABORATION + R₁₃ — common attributes: {user_id, story_id} → primary key of COLLABORATION
     181R₁₃ = All attributes of R₁₂, except: {role, permission_level, collab_created_at}
     182
     183Step 14: Decompose R₁₃ → HAS_GENRE + R₁₄ — common attributes: {story_id, genre_id} → primary key of HAS_GENRE
     184R₁₄ = All attributes of R₁₃, except: {} (HAS_GENRE has no non-key attributes; story_id and genre_id remain in R₁₄ as they are also part of other relations' keys)
     185
     186Step 15: Decompose R₁₄ → READING_LIST_ITEMS + R₁₅ — common attributes: {list_id, story_id} → primary key of READING_LIST_ITEMS
     187R₁₅ = All attributes of R₁₄, except: {added_at}
     188
     189Step 16: Decompose R₁₅ → NEED_APPROVAL (final relation) — common attributes: {suggestion_id, story_id, chapter_id} → primary key of NEED_APPROVAL
     190R₁₆ = ∅ — all attributes fully decomposed
    200191
    201192At every step the common attributes form a primary key in the extracted relation, so the entire decomposition is lossless and no information is lost.
    … …  
    204195
    205196=== 3NF Check
     197
    206198We analyze each relation obtained from the 2NF decomposition for transitive dependencies.
    207199
    208200USERS(user_id, username, email, ...) — FD2: username → user_id and FD3: email → user_id exist, but username and email are both candidate keys, not non-prime attributes. No transitive dependency.
    209201
     202ADMIN(user_id, assigned_at) — single non-prime attribute, trivially depends only on the key. No transitive dependency.
     203
     204REGULAR_USER(user_id, joined_at) — single non-prime attribute, trivially depends only on the key. No transitive dependency.
     205
     206WRITER(user_id, bio) — single non-prime attribute, trivially depends only on the key. No transitive dependency.
     207
     208
    210209STORY(story_id, ..., user_id, ...) — user_id is a non-prime attribute, but within this relation user_id does not determine any other attribute. No transitive dependency.
    211210
    … …  
    216215READING_LIST(list_id, ..., user_id, ...) — user_id is a non-prime attribute, but within this relation user_id does not determine any other attribute. No transitive dependency.
    217216
    218 NOTIFICATION, AI_SUGGESTION, COMMENT — simple primary keys, no non-prime attribute determines another. No transitive dependencies.
    219 
    220 All remaining tables — no non-prime attributes at all, or single non-prime attribute depending on the full composite key. No transitive dependencies.
    221 
     217NOTIFICATION(notification_id, ..., user_id, story_id, ...) — user_id and story_id are non-prime attributes, but neither determines any other attribute within this relation, and neither determines the other. No transitive dependency.
     218
     219AI_SUGGESTION(suggestion_id, ..., suggestion_type, ..., story_id) — story_id is a non-prime attribute that does not determine any other attribute within this relation. No transitive dependency.
     220
     221COMMENT — simple primary key, no non-prime attribute determines another. No transitive dependency.
     222
     223COLLABORATION(user_id, story_id, role, permission_level, collab_created_at) — composite key; role, permission_level, and collab_created_at are non-prime attributes but none of them determines another. No transitive dependency.
     224
     225All remaining tables (LIKES, HAS_GENRE, READING_LIST_ITEMS, NEED_APPROVAL) — no non-prime attributes at all, or a single non-prime attribute depending on the full composite key. No transitive dependencies.
    222226Since no transitive dependencies were found in any relation, all relations already satisfy 3NF.
    223227
    224228=== 3NF Decomposition
     229
    225230Since no transitive dependencies were identified in the 3NF Check, no decomposition is required.
    226231
    227232=== BCNF Check
     233
    228234We analyze each relation for BCNF violations:
     235
    229236USERS(user_id, username, email, user_name, surname, password, user_created_at, user_updated_at) — FD2: username → user_id and FD3: email → user_id exist, but username and email are both superkeys (their closure contains all attributes of USERS). No BCNF violation.
    230237
     238ADMIN(user_id, assigned_at) — user_id is the only determinant and is the candidate key. No BCNF violation.
     239
     240REGULAR_USER(user_id, joined_at) — user_id is the only determinant and is the candidate key. No BCNF violation.
     241
     242WRITER(user_id, bio) — user_id is the only determinant and is the candidate key. No BCNF violation.
     243
    231244STORY(story_id, ...) — only FD4 applies, story_id is the sole candidate key and the only determinant. No BCNF violation.
    232245
    … …  
    243256COMMENT(comment_id, ...) — only FD12 applies, comment_id is the only determinant and is the candidate key. No BCNF violation.
    244257
     258COLLABORATION(user_id, story_id, role, permission_level, collab_created_at) — FD14: {user_id, story_id} → role, permission_level, collab_created_at; {user_id, story_id} is the sole candidate key and the only determinant. No BCNF violation.
     259
    245260All remaining tables — no non-trivial functional dependencies beyond the primary key. No BCNF violations.
    246261
    … …  
    248263
    249264=== BCNF Decomposition
    250 Since no BCNF violations were identified in the BCNF Check, no decomposition is required
     265Since no BCNF violations were identified in the BCNF Check, no decomposition is required.
    251266
    252267=== Conclusion
    253 
    254 After normalization we get same relations from phase 2. So this mean that the database was well modeled.
     268After normalization we obtain 16 relations, matching the final DDL. Normalization independently reproduced the corrected model, confirming it is well-designed and already in BCNF.