Database course Database course 1. Suppose we have a relation SocialNetwork(Pers
ID: 3596803 • Letter: D
Question
Database course Database course 1. Suppose we have a relation SocialNetwork(Person, Friend) That gives immediate friends of a person of which there can be many friends. We want to define a recursive relation Follows whose pairs (p.) are persons such that fwas 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 ffollows person p, but is not an immediate friend ofp c) Write a recursive SQL query that returns the set of pairs (p./f) such that fis a follow-on ofp, 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 friend of p but fhas at most one friendExplanation / Answer
a)
# Relation 'Follows' contains three attributes. Attribute 'Person' is the foreign key of relation 'SocialNetwork'. The primary key of this relation is the combination of 'Person' + 'followingUser' + 'followedBy'
Follows (Person, followingUser, followedBy)
b)
# This query fetch the 'person' and followers combination of all the persons who are not the immediate friend of 'person'
SELECT p.Person, f.followedBy
FROM SocialNetwork AS p
INNER JOIN Follows AS f
ON p.Person = f.Person
WHERE f.followedBy NOT IN (SELECT friend FROM SocialNetwork WHERE f.Person = p.Person)
c)
# This query fetches all the person, friend pairs where friend follows person but is neither a friend or friend of friend.
SELECT p.person, f.followedBy
FROM SocialNetwork AS p
INNER JOIN Follows AS f
ON p.person = f.person
WHERE f.followedBy NOT IN (SELECT friend FROM SocialNetwork WHERE f.person = p.person)
AND f.followedBy NOT IN (SELECT person FROM SocialNetwork WHERE f.person = p.person)
d)
# Query to to return pair (p,f) such that f is a friend of p but f has at most one friend.
SELECT p.person, f.followedBy
FROM SocialNetwork AS p
INNER JOIN Follows AS f
ON p.person = f.person
WHERE f.followedBy IN (SELECT friend FROM SocialNetwork WHERE f.person = p.person)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.