Data Normalization

To be updated

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.