Question

Consider the SOCIAL NETWORK relational database schema description provided below which is used to manage a social network, where:

- Persons are the users -

They can have friends (a friend relation is symmetric meaning that if person1 is friend of person2, the person2 is automatically friend of person1)

- They can post multiple posts on his wall or in other friend’s wall

- They can like posts with different types of likes: like, love, fun, wow, sad or angry. Only one like per post and user. (no need to be friend to like a post). Note: LikeType is a numeric ID and the LikeName is “like” or “wow” or “fun”...

- Then can comment posts, they can comment more than one time in the same post. (no need to be friend to comment a post)

- They can send multiple private messages to other people, that will be marked as read when the receiver opens the message.

- Note, datetime is stored in milliseconds, so it is not possible to post 2 or more posts , make 2 or more comments or send 2 or more private messages in the same exact time by the same user.

The relations are: ● PERSON (NickName, FullName, BirthDate) ● POST (PostID, PersonNick, WallPersonNick, Datetime, Content) ● FRIENDS (PersonNick1, PersonNick2) ● LIKE (PostID, PersonNick, Datetime, Type) ● LIKETYPE(LikeType, LikeName) ● COMMENT (CommentID, PostID, PersonNick, Datetime, Text) ● PRIVATE_MESSAGE (MessageID, PersonNick1, PersonNick2, Datetime, Text, Read)

Write the following queries in relational algebra using the schema provided above

(e) Retrieve the names of all the Persons who have at least a post without likes and without

comments.

(f) Find all persons that liked any post created by “Mike_34” but none of the posts created

by anyone named “Margaret Lind”.

(g) Find the nicknames that have all the different types of likes on their posts

Answer 1

(e)

$\pi$_FullName ( PERSON $\Join$ _{NickName = PersonNick} POST $\Join$ _{PostID != PostID} LIKE $\Join$ _{PostID != PostID} COMMENT)

(f)

$\pi$_FullName ( _{POST.PersonNick = 'Mike_34' and POST.PersonNick != 'Margaret Lind'} (PERSON $\Join$ _{NickName = PersonNick} LIKE $\Join$ _{PostID = PostID}POST )

(g)

$\pi$_NickName ( _{LikeName = 'like' and LikeName = 'love' and LikeName =' fun' and LikeName ='wow' and LikeName ='sad' and LikeName = 'angry' (}PERSON $\Join$ _{NickName = PersonNick} POST $\Join$ _{PostID != PostID} LIKE $\Join$ _{Type = LikeType} LIKETYPE)

Do ask if any doubt. Pleas eupvote.