Relational algebra - sql (common operators)

Question

I have these relational schemes:

Participate (FestivalName, Artist)
Attend(ID, FestivalName)
Prefer (ID, FestivalName)
Like (ID, Artist) 

I would like to list the people which only attend festivals that are not of their preference and that at the same time, in one of those festivals, at least two artists that they like participated (so, 2 or more). Finally, show the ID and the festival.

I would like to solve this by using common operators:

selection operator, projection operator, union operator, difference operator and cartesian product

This is an example:

ATTEND TABLE
147|HannaBalusa |
147|FestivalTOP |
147|BestFestival|

PREFER TABLE
147|FestivalTOP|

LIKE TABLE
147|PaulMackarney|
147|BobDeylan    |

PARTICIPATE TABLE
HannaBalusa |PaulMackarney |
HannaBalusa |BobMurley     |
FestivalTOP |BobDeylan     |
BestFestival|PaulMackarney |
BestFestival|BobDeylan     |

So, i should obtain this output:

147|BestFestival

I can solve this situation by using SQL but i'm having trouble using relational algebra.

Can help me?

Answer 1

An idiomatic SQL query that does what you want might look like this, but it uses more SQL capabilities than you listed.

SELECT A.ID, A.FestivalName
FROM Attend A
JOIN Participate P 
ON A.FestivalName = P.FestivalName --Adds rows with artists for each attended festival
JOIN Like L 
ON L.ID = A.ID AND L.Artist = P.Artist --Leaves only rows with `Like`d artists
WHERE NOT EXISTS (
  SELECT 1 
  FROM Prefer Pr 
  WHERE Pr.ID = A.ID AND Pr.FestivalName = A.FestivalName
) --Removes people who have ever attended a prefered festival
GROUP BY A.ID, A.FestivalName --Allows to count liked artists per festival 
HAVING COUNT(L.Artist) >= 2 --Leaves only festivals with 2 or more liked artists 

To do it with your described operations, it might look like that

SELECT DISTINCT S1.ID, S1.FestivalName
FROM (
  SELECT A.ID, A.FestivalName, P.Artist
  FROM Attend A
  CROSS JOIN Participate P  
  CROSS JOIN Like L 
  WHERE A.FestivalName = P.FestivalName --Rows with artists for each attended festival 
  AND L.ID = A.ID AND L.Artist = P.Artist --Leaves only rows with `Like`d artists
) S1
CROSS JOIN ( -- Copy of the first subquery
  SELECT A.ID, A.FestivalName, P.Artist
  FROM Attend A
  CROSS JOIN Participate P  
  CROSS JOIN Like L 
  WHERE A.FestivalName = P.FestivalName
  AND L.ID = A.ID AND L.Artist = P.Artist 
) S2
WHERE S1.ID = S2.ID 
AND S1.FestivalName = S2.FestivalName 
AND S1.Artist != S2.Artist --Removes festivals with only 1 liked artist

MINUS -- Remove all rows with people who ever attended prefered festivals

SELECT ID, FestivalName 
FROM ( --People who attended prefered festivals
  SELECT DISTINCT A.ID
  FROM Attend A
  CROSS JOIN Prefer P
  WHERE A.ID = P.ID AND A.FestivalName = P.FestivalName
) 
CROSS JOIN ( -- All existent festivals
  SELECT FestivalName
  FROM Attend
)