Are you able to help me solve this in Relational Algebra? I have got this DB (Thesis Defense)

  • Instructors(Teacher_ID, Title, Niche)
  • Projects(Project_ID, Title, Niche)
  • Thesis(Thesis_ID, Project_ID, Jury_ID, Decision)
  • Juries(Jury_ID, Supervisor_ID, First_Member_ID, Second_Member_ID)

I wish to discover the algebraic query to obtain the instructors (ID, Title) that supervises thesis within their niche

Used to do it in SQL already, this is the way it will seem like:

Select Teacher_ID, Name  
From Teachers T  
Where Teacher_ID IN
      (Select Supervisor_ID  
       From Juries  
       Where Jury_ID IN
             (Select Jury_ID  
              From Thesis  
              Where Project_ID IN 
                    (Select Project_ID  
                     From Projects P  
                     Where T.Specialty = P.Specialty)))  

Interesting help!

Are you currently trying to get this done?

select Te.Teacher_ID,Te.Name from thesis t
join project P on (T.Speciality =P.Speciality)
join Juries J on ( J.jury_ID = T.Jury_id)
join Teacher Te on (Te.Teacher_ID = J.Supervisor_ID)

I believe I have found the response to my question, because of Mulki's help:

R1 <- Thesis ⋈ (Project_ID) Projects
R2 <- Teachers ⋈ (Teacher_ID=Supervisor_ID) Juries
R3 <- π Teacher_ID, Name (R2 ⋈ (Specialty ^ Jury_ID) R1)

First in R1 we obtain what Niche the Thesis is within (This is when Mulki's answer helps)
Then in R2 we obtain the Instructors which are Administrators
Finally we join R2 and R1 around the condition they have exactly the same Jury_ID and Niche
and project their IDs &lifier Names only