Relational Algebra union, join and intersect

Question

I'm studying computer science and am brushing up on database systems. I'm having difficulties grasping certain parts.

Say I have the following relations:

Lecturers(LecturerID, Name, DeptID)
Course(DeptID, CrsCode, CrsName, Description)

I note that they both share a common attribute, DeptID, therefore they are union-compatible.

How would I go about listing all courses that are taught by lecturers belonging to computer science dept (CS) or electronic engineering dept (eEng)?

My answer would be using intersection with selection. Would the following be correct or near the mark?

πDeptID,CrsName(Course) intersection πDeptID,Name(σDeptID = CS or DeptID = eEng(Lecturers))

I'm sure join could be used here, but I'm unsure how to use the predicate with it.

Thanks for your help. Once I understand what to use in a few situations I'm sure the rest will be easier.

Thanks for any help.

Answer 1

I would use a simple INNER JOIN for this.

SELECT DEPTID, CRSNAME
FROM COURSE A
INNER JOIN LECTURERS B on A.DEPTID=B.DEPTID
WHERE B.DEPTID='eENG' or B.DEPTID='CS'