Changes between Version 11 and Version 12 of P5

Timestamp:

04/17/26 23:39:54 (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:
+=== 2NF Check
+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 for partial dependencies:
+Since CK is composite, we check R for partial dependencies — cases where a proper subset of CK determines a non-prime attribute:
 
 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
@@ -87 +87 @@
 comment_id → comment_content, user_id, story_id, comment_created_at, comment_updated_at — partial dependency, violates 2NF
 
-=== 2NF Check
+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.
@@ -112 +112 @@
 
 === 3NF Check
-We checked all FDs where the left side is a primary key, so we can skip those. We only need to check:
-FD6: {story_id, chapter_number} → chapter_id → here we can see that there is a UNIQUE constraint on {story_id, chapter_number}, which tells us that it is also a superkey. 
-After this we can safely say that the schema satisfies 3NF.
+We analyze each relation obtained from the 2NF decomposition for transitive dependencies.
+
+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.
 
 === BCNF Check