Suppose we have a relation SocialNetwork(Person, Friend) which gives immediate f

Question

Suppose we have a relation SocialNetwork(Person, Friend) which gives immediate friends of a person. A person may have many friends. This SocialNetwork relation is similar to Twitter in that friendship relationship is not necessarily bi-directional. This implies that if a tuple of SN says that A is a friend of B, then it does not imply that B is a friend of A. If B is a friend of A, which is possible, then it will be identified by another tuple. We want to define a recursive relation Follows whose pairs (p,f) are persons such that f was either a friend of p, or a friend of a friend, or so on.

a) Write a definition of Follows as a SQL recursion.

b) Write a SQL query that returns the set of pairs (p,f) such that friend f follows person p, but is not an immediate friend of p.

c) Write a recursive SQL query that returns the set of pairs (p,f) such that f is a follow-on of p, but is neither a friend nor a friend of a friend.

d) Write a recursive SQL query that returns the set of pairs (p,f) such that f is a follow-on of p but f has at most one friend.

Explanation / Answer

Answer a)

1. FollowOn(f, p)<-SequelOf(f, p)

FollowOn(f, p)<-Follow(f, a) AND SequelOf(a, p)

Here it Follow contain 3 attributes namely : SocialNetwork, FollowingUser, FollowedBy

the primary key here is Person which acts as a foreign key in the SocialNetwork(friend,person)

Answer b)

Select person count(*)

from SocialNetwork left outer join friend

on person.membername = friend.membername;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.