= Normalization and design improvements In my initial DDL design, several entities shared the same attribute names, so I renamed them to make the design clearer and eliminate any ambiguous information. The changes I made follow the format (relation_name_specification): content → story_content, chapter_content, comment_content, notification_content, list_content 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 updated_at → user_updated_at, story_updated_at, chapter_updated_at, list_updated_at, comment_updated_at name → user_name, genre_name, list_name After these changes, I obtained the following de-normalized relation: === Global Relation R = {user_id, username, email, user_name, surname, password, user_created_at, user_updated_at, story_id, mature_content, short_description, image, story_content, story_created_at, story_updated_at, status, chapter_id, chapter_number, chapter_name, title, chapter_content, word_count, rating, published_at, view_count, chapter_created_at, chapter_updated_at, genre_id, genre_name, list_id, list_name, list_content, is_public, list_created_at, list_updated_at, added_at, notification_id, notification_content, is_read, type, link, notification_created_at, content_type, suggestion_id, original_text, suggested_text, accepted, applied_at, suggestion_created_at, suggestion_type, comment_id, comment_content, comment_created_at, comment_updated_at, roles, permission_level, collab_created_at, like_created_at} == Functional Dependencies: - FD1: user_id → username, email, user_name, surname, password, user_created_at, user_updated_at - FD2: username → user_id - FD3: email → user_id - FD4: story_id → mature_content, short_description, image, story_content, user_id, story_created_at, story_updated_at - 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 - FD6: {story_id, chapter_number} → chapter_id - FD7: genre_id → genre_name - FD8: genre_name → genre_id - FD9: list_id → list_name, list_content, is_public, user_id, list_created_at, list_updated_at - FD10: notification_id → notification_content, is_read, recipient_user_id, type, link, notification_created_at - FD11: suggestion_id → original_text, suggested_text, accepted, suggestion_created_at, applied_at, story_id - FD12:comment_id → comment_content, user_id, story_id, comment_created_at, comment_updated_at - FD13: {user_id, story_id} → like_created_at - FD14: {user_id, story_id} → collab_created_at - FD15:{list_id, story_id} → added_at === Closures user_id+ = {user_id, username, email, user_name, surname, password,user_created_at, user_updated_at} → Does NOT contain all attributes of R username+ = {username, user_id, email, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R email+ = {email, user_id, username, user_name, surname, password, user_created_at, user_updated_at} → Does NOT contain all attributes of R story_id+ = {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 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 {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 genre_id+ = {genre_id, genre_name} → Does NOT contain all attributes of R genre_name+ = {genre_name, genre_id} → Does NOT contain all attributes of R 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 notification_id+ = {notification_id, notification_content, is_read, recipient_user_id, type, link, notification_created_at} → Does NOT contain all attributes of R 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 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 {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 {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 {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 === 1NF Check 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. 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} 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. === 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 R 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 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. 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. 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: CHAPTER ⋈ STORY: common attribute story_id → story_id is the primary key of STORY STORY ⋈ USERS: common attribute user_id → user_id is the primary key of USERS READING_LIST ⋈ USERS: common attribute user_id → user_id is the primary key of USERS AI_SUGGESTION ⋈ STORY: common attribute story_id → story_id is the primary key of STORY COMMENT ⋈ STORY: common attribute story_id → story_id is the primary key of STORY COMMENT ⋈ USERS: common attribute user_id → user_id is the primary key of USERS STATUS ⋈ STORY: common attribute story_id → story_id is the primary key of STORY CONTENT_TYPE ⋈ NOTIFICATION: common attribute notification_id → notification_id is the primary key of NOTIFICATION SUGGESTION_TYPE ⋈ AI_SUGGESTION: common attribute suggestion_id → suggestion_id is the primary key of AI_SUGGESTION LIKES ⋈ USERS: common attribute user_id → user_id is the primary key of USERS LIKES ⋈ STORY: common attribute story_id → story_id is the primary key of STORY COLLABORATION ⋈ USERS: common attribute user_id → user_id is the primary key of USERS COLLABORATION ⋈ STORY: common attribute story_id → story_id is the primary key of STORY ROLES ⋈ USERS: common attribute user_id → user_id is the primary key of USERS ROLES ⋈ STORY: common attribute story_id → story_id is the primary key of STORY PERMISSION_LEVEL ⋈ USERS: common attribute user_id → user_id is the primary key of USERS PERMISSION_LEVEL ⋈ STORY: common attribute story_id → story_id is the primary key of STORY HAS_GENRE ⋈ STORY: common attribute story_id → story_id is the primary key of STORY HAS_GENRE ⋈ GENRE: common attribute genre_id → genre_id is the primary key of GENRE READING_LIST_ITEMS ⋈ READING_LIST: common attribute list_id → list_id is the primary key of READING_LIST READING_LIST_ITEMS ⋈ STORY: common attribute story_id → story_id is the primary key of STORY NOTIFY ⋈ USERS: common attribute user_id → user_id is the primary key of USERS NOTIFY ⋈ STORY: common attribute story_id → story_id is the primary key of STORY NOTIFY ⋈ NOTIFICATION: common attribute notification_id → notification_id is the primary key of NOTIFICATION NEED_APPROVAL ⋈ AI_SUGGESTION: common attribute suggestion_id → suggestion_id is the primary key of AI_SUGGESTION NEED_APPROVAL ⋈ STORY: common attribute story_id → story_id is the primary key of STORY NEED_APPROVAL ⋈ CHAPTER: common attribute chapter_id → chapter_id is the primary key of CHAPTER 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. Since there are no partial dependencies in any of the resulting tables, this schema satisfies 2NF. === 3NF Check 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 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 === Conclusion After normalization we get same relations from phase 2. So this mean that the database was well modeled.