Question

The following relations are part of a school database:

STUDENT(STUD#, STUD_NAME, MAJOR, YEAR, GPA)

TEACHER(FACULTY#, DEPT, TEACHERNAME)

ENROLLMENT(STUD#, COURSE#, GRADE)

RESPONSIBILITY(FACULTY#, COURSE#)

Using PROJECT, SELECT and JOIN, write the sequence of operations to answer each of the following questions:

What are the names of teachers who are responsible for courses in which students whose name is 'JONES' are enrolled?

Use relational Algebra and relational calculus for this question.

Answer 1

select teachername from teacher ------ we only need teacher name

where teacher.facultyno = responsibility.facultyno --------------- join between teacher and responsibility as facultyno is the common column (foreign key) to get the teacher details

and responsibility.courseno = enrollment.courseno ---------- join between responsibility and enrollment on courseno to get the coursedetails

and enrollment.studentno = student.studentno ------------- join between enrollment and student on studentno to get the student names

and student.stud_name = 'JONES'; ------------- as per question , the student name would be 'JONES'

If we try to frame the sql with the help of relational algebra then,

The Relational Algebra is:

Πteachername (Πfacultyno (Πcourseno ((Πstudentno σstud_name = 'JONES'   STUDENT) X ENROLLMENT ) X RESPONSIBILITY ) X TEACHER)

here X symbolises cartesian product.

-----------------------------------------------------------------

If we want to frmae the above SQL with the help of Tuple Relational Calculas , it would be:

{T | ∃T1 ∈ TEACHER(∃Z ∈ RESPONSIBILITY (∃X ∈ STUDENT(X.stud_name =’JONES’∧ ∃Y ∈ ENROLLMENT (Y. studentno = X. studentno ∧ Y. courseno = Z. courseno ∧ Z. facultyno = T 1. facultyno )) ∧ T. teachername = T1. teachername)}

In both the answers, the joinnings are done based upon the SQL written at the starting.