The book says that I cannot make a relational algebra theta join (a join with whatever condition) on two identical relations, so let's suppose I have:
person(ssn, name, surname, age)
disease(ssn_p, gravity, code)
With ssn from person which is the primary key, code from disease which is the primary key, and ssn_p the foreign key referencing person.name.
Can I do a join on two relations that have common fields?
So for example there are two relations (result of a select and a projection):
R1(ssn, name)
R2(ssn_p, name)
Can I do the join between R1 and R2?
You can't do a theta-join when the relations share attributes. The reason is that the theta-join basically juxtaposes attributes. In other words, if a1, a2, ..., an are attributes in R1, and b1, b2, ..., bm are attributes in R2, then the result of a theta-join on R1 and R2 will be a relation R3 with attributes a1, a2, ..., an, b1, b2, ..., bm. If R1 and R2 share any attributes, then R3 will have duplicated attributes. Repeated attributes are not allowed in the relation algebra.
If you would like to do a theta-join when the relations share some attributes, you can do a rename operation on one of the relations, so that all attributes becomes distinct.