Question

Consider the following collection of relation schemes:

instructor (ID, name, dept_name, salary)

     teaches (ID, course id, sec id, semester, year)

Using the above relations, write an equivalent relational algebra expression for the following:

∏_name (s_{instructor.ID =teaches.ID} (s_{dept_name =“Physics”} (instructor × teaches)))

Answer 1

Let us first decode the given relational algebra expression

∏name (σinstructor.ID =teaches.ID (σdept_name =“Physics” (instructor × teaches))

• (instructor × teaches)- This will join two relations instructor and teaches relations
• σdept_name =“Physics” (instructor × teaches) – select from join based on condition dept_name=”Physics”
• σinstructor.ID =teaches.ID (σdept_name =“Physics” (instructor × teaches))- will select only those ID which are common in both the relations.
• ∏name (σinstructor.ID =teaches.ID (σdept_name =“Physics” (instructor × teaches))

Give name of those Instructor who teaches “Physics”

Equivalent for the above can be

∏name(instructor)(σdept_name=“Physics” ( σ instructor.ID=teaches.ID (instructor x teaches)))

• (instructor x teaches)- )- This will join two relations instructor and teaches relations.
• σ instructor.ID=teaches.ID (instructor x teaches)- Select only those from join result which are common in instructor and teaches relations. Note this will contain those instructors also which have dept_name other than “Physics”
• σdept_name=“Physics” ( σ instructor.ID=teaches.ID (instructor x teaches)))

Select instructors which have dept_name as physics

• ∏name(instructor)(σdept_name=“Physics” ( σ instructor.ID=teaches.ID (instructor x teaches)))

Project or display name from instructor whose dept_name = “Physics”

We can simply use this also to display name from instructor whose dept_name = “Physics”

∏name(instructor)(σdept_name=“Physics”)