Question

Consider the following relational schema:

student(studID, studname, major, advisor)

department(deptname, major)

club(studID,clubname)

professor(profID, profname, building, deptname)

NOTE: KEY ATTRIBUTES ARE IN BOLD

where advisor takes values in the domain of professor names (profname) and

the underline attributes form the primary key of the corresponding relations.

Questions:

1. State any assumptions you might make.
2. Write the relational algebra for the following queries:

2.a. Find all students and their advisors.

2.b. Find all the students who are in any one of the clubs that Jamie Smith is in.

2.c. Find all of the advisors, their buildings and departments that advise students that

are in the same clubs that Jamie Smith participates into.

2.d. Find all professors names and their departments that have offices in the

buildings identified in query 2c.

2.e. Find all student names and their major(s) that participate in Computer Science

Association Club.

Answer 1

Assumptions: Assuming that the student.advisor and professor.profname are the same.

2.a. Find all students and their advisors.

select studname,advisor from student;

2.b. Find all the students who are in any one of the clubs that Jamie Smith is in.

select s.studname
from student s, club c
where s.studID = c.studID
and c.clubname in (select c.clubname
from student s, club c
where s.studID = c.studID
and studname = 'Jamie Smith');

2.c. Find all of the advisors, their buildings and departments that advise
students that are in the same clubs that Jamie Smith participates into.

select s.advisor, p.building, d.deptname
from student s, club c, department d , professor p
where s.studID = c.studID
and s.advisor = p.profname
and p.deptname = d.deptname
and s.major = d.major
and c.clubname in (select c.clubname
from student s, club c
where s.studID = c.studID
and studname = 'Jamie Smith');

2.d. Find all professors names and their departments that have offices in the
buildings identified in query 2c.

select p.profname, p.deptname
from professor p where profID in
(select p.profID
from student s, club c, department d , professor p
where s.studID = c.studID
and s.advisor = p.profname
and p.deptname = d.deptname
and s.major = d.major
and c.clubname in (select c.clubname
from student s, club c
where s.studID = c.studID
and studname = 'Jamie Smith'));

2.e. Find all student names and their major(s) that participate in Computer Science

Association Club.

select s.studname, s.major
from student s, club c
where s.studID = c.studID
and c.clubname = 'Computer Science Association Club';