Changes between Version 15 and Version 16 of P5

Timestamp:

04/21/26 00:25:36 (12 days ago)

Author:

211099

Comment:

--

P5

@@ -92 +92 @@
 
 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.
 
@@ -116 +135 @@
 
 USERS(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.
+
 STORY(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.
+
 CHAPTER(chapter_id, ..., story_id, ...) — story_id is a non-prime attribute, but within this relation story_id does not determine any other attribute. No transitive dependency.
+
 GENRE(genre_id, genre_name) — both attributes are candidate keys. No transitive dependency.
+
 READING_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.
+
 NOTIFICATION, AI_SUGGESTION, COMMENT — simple primary keys, no non-prime attribute determines another. No transitive dependencies.
+
 All remaining tables — no non-prime attributes at all, or single non-prime attribute depending on the full composite key. No transitive dependencies.
 
 Since no transitive dependencies were found in any relation, all relations already satisfy 3NF.
+
 === 3NF Decomposition
 Since no transitive dependencies were identified in the 3NF Check, no decomposition is required.
@@ -130 +156 @@
 We analyze each relation for BCNF violations:
 USERS(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.
+
 STORY(story_id, ...) — only FD4 applies, story_id is the sole candidate key and the only determinant. No BCNF violation.
+
 CHAPTER(chapter_id, ...) — FD5: chapter_id → all others, FD6: {story_id, chapter_number} → chapter_id. Both chapter_id and {story_id, chapter_number} are candidate keys, so both are superkeys. No BCNF violation.
+
 GENRE(genre_id, genre_name) — FD7: genre_id → genre_name and FD8: genre_name → genre_id. Both are candidate keys, so both are superkeys. No BCNF violation.
+
 READING_LIST(list_id, ...) — only FD9 applies, list_id is the only determinant and is the candidate key. No BCNF violation.
+
 NOTIFICATION(notification_id, ...) — only FD10 applies, notification_id is the only determinant and is the candidate key. No BCNF violation.
+
 AI_SUGGESTION(suggestion_id, ...) — only FD11 applies, suggestion_id is the only determinant and is the candidate key. No BCNF violation.
+
 COMMENT(comment_id, ...) — only FD12 applies, comment_id is the only determinant and is the candidate key. No BCNF violation.
+
 All remaining tables — no non-trivial functional dependencies beyond the primary key. No BCNF violations.
+
 Since every determinant in every relation is a superkey, all relations satisfy BCNF.
+
 === BCNF Decomposition
 Since no BCNF violations were identified in the BCNF Check, no decomposition is required