= 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, 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
=== Global Relation
R = {user_id, username, email, user_name, surname, password, user_created_at, 
user_updated_at, assigned_at, joined_at, bio, story_id, title, mature_content, 
short_description, image, story_content, status, story_created_at, story_updated_at, 
chapter_id, chapter_number, chapter_name, 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, content_type, is_read, link, notification_created_at, 
suggestion_id, original_text, suggested_text, suggestion_type, accepted, applied_at, 
suggestion_created_at, comment_id, comment_content, comment_created_at, comment_updated_at, 
role, permission_level, collab_created_at, liked_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 → title, mature_content, short_description, image, story_content, status, 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, content_type, is_read, user_id, story_id, link, notification_created_at
- FD11: suggestion_id → original_text, suggested_text, suggestion_type, 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} → liked_at
- FD14: {user_id, story_id} → role, permission_level, 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, 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

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, 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

{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

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, 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

suggestion_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

comment_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

{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

{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

{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.

=== 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}
since 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.

=== 2NF Check
We analyze the global relation R with candidate key:

CK = {chapter_id, genre_id, list_id, notification_id, suggestion_id, comment_id}

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, content_type, is_read, user_id, story_id, link, notification_created_at — partial dependency, violates 2NF

suggestion_id → original_text, suggested_text, suggestion_type, 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.

ADMIN(user_id, assigned_at) → simple primary key user_id, so it satisfies 2NF.

REGULAR_USER(user_id, joined_at) → simple primary key user_id, so it satisfies 2NF.

WRITER(user_id, bio) → simple primary key user_id, so it satisfies 2NF.

STORY(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.

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, content_type, is_read, link, user_id, story_id, notification_created_at) → simple primary key notification_id, so it satisfies 2NF.

AI_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.

COMMENT(comment_id, comment_content, user_id, story_id, comment_created_at, comment_updated_at) → simple primary key comment_id, so it satisfies 2NF.

LIKES(user_id, story_id, liked_at) → liked_at depends on {user_id, story_id} together, so it satisfies 2NF.

COLLABORATION(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.

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.

NEED_APPROVAL(suggestion_id, story_id, chapter_id) → There are no other attributes, so it satisfies 2NF.

=== Lossless Join Verification

Step 1: Decompose R → USERS + R₁ — common attribute: user_id → primary key of USERS
R₁ = All attributes of R, except: {username, email, user_name, surname, password, user_created_at, user_updated_at}

Step 2: Decompose R₁ → ADMIN + R₂ — common attribute: user_id → primary key of ADMIN
R₂ = All attributes of R₁, except: {assigned_at}

Step 3: Decompose R₂ → REGULAR_USER + R₃ — common attribute: user_id → primary key of REGULAR_USER
R₃ = All attributes of R₂, except: {joined_at}

Step 4: Decompose R₃ → WRITER + R₄ — common attribute: user_id → primary key of WRITER
R₄ = All attributes of R₃, except: {bio}

Step 5: Decompose R₄ → STORY + R₅ — common attribute: story_id → primary key of STORY
R₅ = All attributes of R₄, except: {title, mature_content, short_description, image, story_content, status, story_created_at, story_updated_at}

Step 6: Decompose R₅ → CHAPTER + R₆ — common attribute: chapter_id → primary key of CHAPTER
R₆ = All attributes of R₅, except: {chapter_number, chapter_name, chapter_content, word_count, rating, published_at, view_count, chapter_created_at, chapter_updated_at}
(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)

Step 7: Decompose R₆ → GENRE + R₇ — common attribute: genre_id → primary key of GENRE
R₇ = All attributes of R₆, except: {genre_name}

Step 8: Decompose R₇ → READING_LIST + R₈ — common attribute: list_id → primary key of READING_LIST
R₈ = All attributes of R₇, except: {list_name, list_content, is_public, list_created_at, list_updated_at}

Step 9: Decompose R₈ → NOTIFICATION + R₉ — common attribute: notification_id → primary key of NOTIFICATION
R₉ = All attributes of R₈, except: {notification_content, content_type, is_read, link, notification_created_at}

Step 10: Decompose R₉ → AI_SUGGESTION + R₁₀ — common attribute: suggestion_id → primary key of AI_SUGGESTION
R₁₀ = All attributes of R₉, except: {original_text, suggested_text, suggestion_type, accepted, suggestion_created_at, applied_at}

Step 11: Decompose R₁₀ → COMMENT + R₁₁ — common attribute: comment_id → primary key of COMMENT
R₁₁ = All attributes of R₁₀, except: {comment_content, comment_created_at, comment_updated_at}

Step 12: Decompose R₁₁ → LIKES + R₁₂ — common attributes: {user_id, story_id} → primary key of LIKES
R₁₂ = All attributes of R₁₁, except: {liked_at}

Step 13: Decompose R₁₂ → COLLABORATION + R₁₃ — common attributes: {user_id, story_id} → primary key of COLLABORATION
R₁₃ = All attributes of R₁₂, except: {role, permission_level, collab_created_at}

Step 14: Decompose R₁₃ → HAS_GENRE + R₁₄ — common attributes: {story_id, genre_id} → primary key of HAS_GENRE
R₁₄ = 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)

Step 15: Decompose R₁₄ → READING_LIST_ITEMS + R₁₅ — common attributes: {list_id, story_id} → primary key of READING_LIST_ITEMS
R₁₅ = All attributes of R₁₄, except: {added_at}

Step 16: Decompose R₁₅ → NEED_APPROVAL (final relation) — common attributes: {suggestion_id, story_id, chapter_id} → primary key of NEED_APPROVAL
R₁₆ = ∅ — all attributes fully decomposed

At every step the common attributes form a primary key in the extracted relation, so the entire decomposition is lossless and no information is lost.

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.

ADMIN(user_id, assigned_at) — single non-prime attribute, trivially depends only on the key. No transitive dependency.

REGULAR_USER(user_id, joined_at) — single non-prime attribute, trivially depends only on the key. No transitive dependency.

WRITER(user_id, bio) — single non-prime attribute, trivially depends only on the key. 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(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.

AI_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.

COMMENT — simple primary key, no non-prime attribute determines another. No transitive dependency.

COLLABORATION(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.

All 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.
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.

ADMIN(user_id, assigned_at) — user_id is the only determinant and is the candidate key. No BCNF violation.

REGULAR_USER(user_id, joined_at) — user_id is the only determinant and is the candidate key. No BCNF violation.

WRITER(user_id, bio) — user_id is the only determinant and is the candidate key. 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.

COLLABORATION(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.

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 obtain 16 relations, matching the final DDL. Normalization independently reproduced the corrected model, confirming it is well-designed and already in BCNF.