2

I've been trying to translate an SQL query (list names of friends of friends of person with ID no. 1000) into relational algebra, but with not much luck. The query is as follows:

CREATE TEMPORARY TABLE Temp1 AS (
SELECT * FROM ( 
SELECT ID2 AS ID FROM isFriend WHERE ID1 = 1000 
UNION
SELECT ID1 AS ID FROM isFriend WHERE ID2 = 1000
) temp 
);

CREATE TEMPORARY TABLE Temp2 AS (SELECT * FROM MyTable);

SELECT Name FROM People WHERE ID IN (
SELECT ID2 AS ID FROM isFriend WHERE ID1 IN (SELECT * FROM Temp1)
UNION
SELECT ID1 AS ID FROM isFriend WHERE ID2 IN (SELECT * FROM Temp2)
);

The first thing I did was to create the temporary table by using the rename operation:

ρ(Temp1,∏ID1(σID2=1000(isFriend)) U (∏ID2(σID1=1000(isFriend)))

I'm not quite sure what to do after that for "SELECT Name..." other than

∏Name(σID.....)

How would I go about referencing the renamed temporary table "Temp1" in the Select operation, and how would I translate the "IN" keyword (WHERE ID1 IN ... )?

I'm kind of new to the whole relational algebra thing, so I wouldn't be surprised if I totally overlooked something really obvious (to those who have experience with RA).

Any help would be appreciated, thanks!

Cloud Strife
  • 99
  • 2
  • 2
  • 7
  • Possible duplicate of [Relational Algebra equivalent of SQL "NOT IN"](https://stackoverflow.com/questions/12541731/relational-algebra-equivalent-of-sql-not-in) – Paul Sweatte Jul 07 '17 at 18:52

0 Answers0