Nontrivial joining a relation with itself [closed]

I am a philosopher and am doing my first steps in relational algebra. So here is a puzzle:

Let say that we have a relation R with attributes 'R.a' and 'R.b'. Is it possible to make a renaming and natural join operations on R and receive a triple (a, b, a) as a result?

If I make the following steps:

1. rename R to S.
2. rename all attributes in S to 'S.a', 'S.b'.
3. rename 'S.b' on 'R.b' and
4. make a natural join of R and S.

Will it work? How do I write such a request properly?

• So essentially you want to make a self-join on column `b`? – ypercubeᵀᴹ Jul 31 '18 at 19:37
• There's not just 1 RA, they differ in what operators are available, what operator input & output is & what a relation is. Some so-called algebras are languages not algebras. So please define your algebra/language & preferably give a reference, eg textbook name, edition & section. Also reflect your research at answering this & for homework show what you can do. Nested RA calls form a programming language. So please give what you can of a minimal reproducible example.--Which is a lot, google 'run relational algebra online'. – philipxy Dec 27 '20 at 6:05

What you describe is simply a projection of `R` that includes `a` twice. There is no need for "renaming and natural join operations on R".

1. Rename R to S
2. Rename S.a to S.c
3. Natural join T = R |><| S
4. Rename T.c to T.a

ρc/a((ρa/c(ρR/S)) |><| R)

SQL code look like this:

``````SELECT R.a, R.b, S.c AS a
FROM R
NATURAL JOIN ( SELECT b, a AS c
FROM R
) AS S
;
``````