How do I represent my joins in Relational Algebra?

Question

I am new to relational algebra and for my assignment I have to create two. I have written out the problem I have faced in SQL but I am unsure of how to represent such joins in relational algebra. Any help/pointers would be greatly appreciated.

SELECT ps.FirstName AS StudentFirstName, ps.LastName AS StudentLastName, pst.FirstName AS StaffFirstName , pst.LastName AS StaffLastName, pg.FirstName AS GuardianFirstName, pg.LastName AS GuadianLastName, i.DateTimeReported, i.NatureOfIllness
FROM Incident i
JOIN Student s USING (StudentID)
JOIN Person ps ON (s.StudentID = ps.PersonID)
JOIN Staff st USING (StaffID)
JOIN Person pst ON (st.StaffID = pst.PersonID)
JOIN Guardian g USING (GuardianID)
JOIN Person pg ON (g.GuardianID = pg.PersonID)
WHERE i.DecisionMade IS NULL;

Answer 1

I think you are going about the problem the wrong way. In the real world you'd never want to create a situation where you had a 6 way join.

What it seems like you have here are incidents and people. The people have roles. There should be maybe three tables, incidents, roles, and people. The way you're joining against Person twice, is going to be a mess.

I think you should sit down and read about database normalization.

http://en.wikipedia.org/wiki/Database_normalization

Answer 2

Those left joins you're doing are referred to in relational algebra as theta-joins, sometimes more specifically specifically as equijoins. You'll want to use the symbol that looks like a bow tie and write "StudentID = PersonID" underneath it (for the second join in your example). I can't do the fancy symbols, but http://en.wikipedia.org/wiki/Relational_algebra#.CE.B8-join_and_equijoin has some examples.

Also, there's nothing wrong with 6-way joins and they'll indeed happen in the real world.