Share
Explore BrainMass

Queries using Relational Algebra and Relational Calculus

Consider the following University Database schemas:
Department (D-code, D-Name, Chair-SSn)
Course (D-code, C-no, Title, Units)
Prereq (D-code, C-no, P-code, P-no)
Class (Class-no, D-code, C-no, Instructor-SSn)
Faculty (Ssn, F-Name, D-Code, Rank)
Student (Ssn, S-Name, Major, Status)
Enrollment (Class-no, Student-Ssn)
Transcript (Student-Ssn, D-Code, C-no, Grade)

In the above tables, primary keys are underlined. Some explanations of attributes are as follows. Chair-Ssn in Department refers to Ssn in Faculty table. Instructor-SSn refers to Ssn in Faculty table, and Student-Ssn refers to Sssn in Student table. Class-no is a unique ID for each offering of a course. (For example, a class with class-no='12020' may be an offering of INFS 614, in which case, we may have a row like <12020, INFS, 614, 100009399> in the Class table.) <P-code, P-no> in Prereq table refers to <D-code, C-no>in the Course table, i.e., they refer to courses. A example row in Prereq table is <INFS,614, INFS, 501> , which means that INFS614 needs INFS501 as a prerequisite course. Other foreign keys and meanings of the tables should be obvious.

Specify the following queries in relational algebra, relational calculus and implement them in SQL (I do not need help with SQL implementation):
1. List the courses (D-code and C-no), along with the names of the students who are currently taking them.
2. List all the courses (D-code and C-no) that John (i.e., S-Name=``John'') got 'A' grade.
3. List the courses (D-Code and C-No) that do not require any pre-requisites.
4. Give the students (Ssn) who are enrolled in INFS614 (i.e., D-code=``INFS'' and C-no=``614'') and have satisfied all its prerequisites.

Attachments

Solution Preview

Queries expressed using Relational Algebra:

1. List the courses (D-code and C-no), along with the names of the students who are currently taking them.

Answer:
First we take natural join of Class and Enrollment on the only common attribute name Class-no, and project D-code, C-no and Student-Ssn from it. Then we take an equi-join of this result with Student, and project D-code, C-no and S-Name to get the required result.

Class_StuSSN <= D-code,C-no,Student-ssn (Class * Enrollment)
Result <= ?D-code,C-no,S-Name (Class_StuSSN JOINStudent-ssn=Ssn Student)

Another way to do this would be as follows:

Result <= D-code,C-no,S-Name (Class * ((p(Class-no, Ssn) Enrollment) * Student))

2. List all the courses (D-code and C-no) that John (i.e., S-Name=``John'') got 'A' grade.

Answer:
We take equi-join of Transcript and Student and select those tuples from it where student name is 'John' and ...

Solution Summary

Brief supportive explanations are provided with many queries expressed using Relational Algebra.

$2.19