Is my Relational Algebra correct?

Question

I have a database assignment which I have to create some relational algebra for two problems. I feel fairly all right with the majority of it, but I just get confused when trying to project attributes out of a table which is joined to another table.

for example is this correct?

Q1) List the details of incidences with no calls made, so that the receptionist knows which incidents still need to be called in.

RESULT <-- PROJECT<STUDENT.FirstName, STUDENT.LastName, STAFF.FirstName,
STAFF.INCIDENT.LastName, INCIDENT.DateTimeReported, 
INCIDENT.NatureOfIllness(SELECTINCIDENT.DecisionMade = 
  ''(Staff RIGHT JOIN<STAFF.StaffID = INCIDENT.StaffID>
(INCIDENT LEFT JOIN<INCIDENT.StudentID = STUDENT.StudentID>(STUDENT))))

The SQL which I am trying to interpret into relational algebra is:

SELECT 
  s.FirstName, s.LastName, st.FirstName, st.LastName
  , i.DateTimeReported, i.NatureOfIllness
FROM Student s 
RIGHT JOIN Incident i ON s.StudentID = i.StudentID  
LEFT JOIN Staff st ON st.StaffID = i.StaffID
WHERE i.DecisionMade = ''

Any points of advice would be much appreciated.

please reformat the algebra and sql so that it is readable (no scrollbars) — Steven A. Lowe, Jan 06 '09 at 21:27
As this is homework you would be better off asking for hints on where to find the answer rather than asking for the answer its self. — BCS, Jan 06 '09 at 21:53
He's okay- he posted his own response first, and is asking for help checking it. Not at all the same thing as asking us to do it for him. — Joel Coehoorn, Jan 06 '09 at 22:13
Relational algebra, ... one of the worst theories I've ever studied. Basically, it was all SQL with a different and more cryptic notation! — Mehrdad Afshari, Jan 06 '09 at 22:41

score 2 · Answer 1 · answered Jan 06 '09 at 22:13

It's usually (some exceptions apply, of course) easier to read and understand the sql if you write it all with LEFT JOINs:

SELECT s.FirstName, s.LastName, st.FirstName, st.LastName, i.DateTimeReported, i.NatureOfIllness
FROM Incident i
LEFT JOIN Student s ON s.StudentID = i.StudentID
LEFT JOIN Staff st ON st.StaffID = i.StaffID
WHERE i.DecisionMade = ''

score 1 · Accepted Answer · answered Jan 06 '09 at 21:54

Your version seems correct, except for some typos like STAFF.INCIDENT.LastName. Here's my version:

RESULT <--- 
   PROJECT <STUDENT.FirstName, STUDENT.LastName, 
                     STAFF.FirstName, STAFF.LastName,
                     INCIDENT.DateTimeReported, INCIDENT.NatureOfIllness>
      (SELECT <INCIDENT.DecisionMade = ''> 
         ((STUDENT RIGHT JOIN <STUDENT.StudentID = INCIDENT.StudentID> INCIDENT)
             LEFT JOIN <INCIDENT.StaffID = STAFF.StaffID> STAFF)

Is my Relational Algebra correct?

2 Answers2