Changes between Version 10 and Version 11 of P5

Timestamp:

04/17/26 23:33:14 (2 weeks ago)

Author:

211099

Comment:

--

P5

@@ -75 +75 @@
 Every 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.
 
+=== 2NF Decomposition
+R has the following 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 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
+
 === 2NF Check
-Every table that has a simple (non-composite) primary key is already in 2NF, so we only need to check the tables that have composite keys.
+After decomposition we verify that every resulting relation 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 depends on {user_id, story_id} together, so it satisfies 2NF.
-
-COLLABORATION {user_id, story_id} → 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 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.
-
-Since there are no partial dependencies in any of the tables, this schema 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.
+Since there are no partial dependencies in any of the resulting tables, this schema satisfies 2NF.
 
 === 3NF Check