Skip to content

Data Normalization

Jump to bottom
Nantawan Paramapooti edited this page Nov 10, 2024 · 9 revisions

1-3 NF for Each Relation

*data instances in the following examples are abstract values for demonstration, may not accurately reflects project's actual instances.

Post (post_id, user_id, p_content, timestamp)

post_id user_id p_content timestamp
1 1 "Hello" 2024-11-10
2 2 "Learning NF Forms" 2024-11-11
3 1 "Normalization Rocks" 2024-11-12

1NF: All attributes contain atomic values.

  • Each attribute (post_id, user_id, p_content, timestamp) holds only indivisible values.

2NF: No partial dependency on any subset of a primary key.

  • Since post_id is the primary key, all non-key attributes (user_id, p_content, timestamp) are fully dependent on post_id.

3NF: No transitive dependency on non-key attributes.

  • There are no dependencies between non-key attributes; therefore, no transitive dependency exists.

Follower (user_id, follower_id)

user_id follower_id
1 2
2 3
3 1

1NF: All attributes contain atomic values.

  • Both attributes (user_id, follower_id) hold only indivisible values.

2NF: No partial dependency on any subset of a primary key.

  • The combination of user_id and follower_id forms a composite primary key, and there is no partial dependency.

3NF: No transitive dependency on non-key attributes.

  • There are no additional non-key attributes, so no transitive dependency exists.

Blocker (blocker_id, blocked_id)

blocker_id blocked_id
1 3
2 1
3 2

1NF: All attributes contain atomic values.

  • Each attribute (blocker_id, blocked_id) is atomic.

2NF: No partial dependency on any subset of a primary key.

  • The combination of blocker_id and blocked_id forms a composite primary key, and all attributes are fully dependent on it.

3NF: No transitive dependency on non-key attributes.

  • No non-key attributes are present, so no transitive dependency exists.

User (user_id, username, email, password, name, bio)

user_id username email password name bio
1 user1 user1@email.com pass123 John Doe "Developer"
2 user2 user2@email.com pass456 Jane Smith "Data Scientist"
3 user3 user3@email.com pass789 Bob Brown "AI Enthusiast"

1NF: All attributes contain atomic values.

  • Each attribute (user_id, username, email, password, name, bio) holds atomic values.

2NF: No partial dependency on any subset of a primary key.

  • user_id is the primary key, and all other attributes (username, email, password, name, bio) are fully dependent on user_id.

3NF: No transitive dependency on non-key attributes.

  • There are no transitive dependencies among non-key attributes.

UserChat (user_id, chat_id)

user_id chat_id
1 1
2 1
3 2

1NF: All attributes contain atomic values.

  • Both attributes (user_id, chat_id) hold atomic values.

2NF: No partial dependency on any subset of a primary key.

  • The composite primary key (user_id, chat_id) fully determines all non-key attributes.

3NF: No transitive dependency on non-key attributes.

  • There are no non-key attributes, so no transitive dependency exists.

ChatRoom (chat_id, chat_name)

chat_id chat_name
1 "General"
2 "Project A"
3 "Random Chat"

1NF: All attributes contain atomic values.

  • Each attribute (chat_id, chat_name) is atomic.

2NF: No partial dependency on any subset of a primary key.

  • chat_id is the primary key, and chat_name is fully dependent on chat_id.

3NF: No transitive dependency on non-key attributes.

  • There are no transitive dependencies among non-key attributes.

Message (message_id, user_id, chat_id, ms_content, timestamp)

message_id user_id chat_id ms_content timestamp
1 1 1 "Hello World" 2024-11-10
2 2 1 "Hi Everyone" 2024-11-11
3 3 2 "Project Update" 2024-11-12

1NF: All attributes contain atomic values.

  • Each attribute (message_id, user_id, chat_id, ms_content, timestamp) holds atomic values.

2NF: No partial dependency on any subset of a primary key.

  • message_id is the primary key, and all other attributes are fully dependent on message_id.

3NF: No transitive dependency on non-key attributes.

  • There are no transitive dependencies among non-key attributes.

Comment (comment_id, user_id, post_id, c_content, timestamp)

comment_id user_id post_id c_content timestamp
1 1 1 "Nice Post" 2024-11-10
2 2 1 "Great info!" 2024-11-11
3 3 2 "Very helpful." 2024-11-12

1NF: All attributes contain atomic values.

  • Each attribute (comment_id, user_id, post_id, c_content, timestamp) is atomic.

2NF: No partial dependency on any subset of a primary key.

  • comment_id is the primary key, and all other attributes depend fully on comment_id.

3NF: No transitive dependency on non-key attributes.

  • There are no transitive dependencies among non-key attributes.

Tag (tag_id, tag_content)

tag_id tag_content
1 "coding"
2 "database"
3 "AI"

1NF: All attributes contain atomic values.

  • Each attribute (tag_id, tag_content) is atomic.

2NF: No partial dependency on any subset of a primary key.

  • tag_id is the primary key, and tag_content is fully dependent on tag_id.

3NF: No transitive dependency on non-key attributes.

  • There are no transitive dependencies among non-key attributes.

PostTag (tag_id, post_id)

tag_id post_id
1 1
2 2
3 3

1NF: All attributes contain atomic values.

  • Both attributes (tag_id, post_id) hold atomic values.

2NF: No partial dependency on any subset of a primary key.

  • The composite primary key (tag_id, post_id) fully determines all non-key attributes.

3NF: No transitive dependency on non-key attributes.

  • There are no non-key attributes, so no transitive dependency exists.

Likes (like_id, user_id, post_id)

like_id user_id post_id
1 1 1
2 2 2
3 3 3

1NF: All attributes contain atomic values.

  • Each attribute (like_id, user_id, post_id) is atomic.

2NF: No partial dependency on any subset of a primary key.

  • like_id is the primary key, and all other attributes are fully dependent on it.

3NF: No transitive dependency on non-key attributes.

  • There are no transitive dependencies among non-key attributes.

Shares (share_id, user_id, post_id, share_type)

share_id user_id post_id share_type
1 1 1 "public"
2 2 1 "private"
3 3 2 "public"

1NF: All attributes contain atomic values.

  • Each attribute (share_id, user_id, post_id, share_type) is atomic.

2NF: No partial dependency on any subset of a primary key.

  • share_id is the primary key, and all other attributes are fully dependent on it.

3NF: No transitive dependency on non-key attributes.

  • There are no transitive dependencies among non-key attributes.

Conclusion

All relations are already in its 3NF form from the UML design, no further changes needed.

Clone this wiki locally