Question

Give the following queries in the relational algebra using the following relational schema: Student (sid, name, age, address, email, cgpa) enrolledIn (sid, modulecode, grade) module (modulecode, modulename, credits, lecturername)

a) What are the names of students enrolled in module code “cs3020”?

b) Give the module code of all the modules that are taught by the teacher named “Altaf”?

c) Who teaches the module with code”cs1500”?

d) Give the name of all the teachers teaching the student with id”12f6778”.

Answer 1

The given relational schemas are :

Student (sid, name, age, address, email, cgpa)

enrolledIn (sid, modulecode, grade)

module (modulecode, modulename, credits, lecturername)

a)

The relational algebra query to find the  names of students enrolled in module code “cs3020” is :

π _{( name )} (  σ _{( modulecode = "cs3020” )} (  Student  ⋈ enrolledIn ) )

b)

The relational algebra query to find the module code of all the modules that are taught by the teacher named “Altaf” is :

π _{( modulecode )} (  σ _{( lecturername = “Altaf” )} ( module ) )

c)

The relational algebra query to find the teachers having module with code ”cs1500” is :

π _{( lecturername )} (  σ _{( modulecode = ”cs1500”)} ( module ) )

d)

The relational algebra query to find  the name of all the teachers teaching the student with id ”12f6778” is :

π _{( lecturername )} ( σ _{( sid = ”12f6778” )} ( enrolledIn ⋈ module ) ) .