Purchase Solution

Queries using Relational Algebra and Relational Calculus

Not what you're looking for?

Ask Custom Question

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.

Purchase this Solution

Solution Summary

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

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 ...

Purchase this Solution


Free BrainMass Quizzes
Graphs and Functions

This quiz helps you easily identify a function and test your understanding of ranges, domains , function inverses and transformations.

Exponential Expressions

In this quiz, you will have a chance to practice basic terminology of exponential expressions and how to evaluate them.

Geometry - Real Life Application Problems

Understanding of how geometry applies to in real-world contexts

Solving quadratic inequalities

This quiz test you on how well you are familiar with solving quadratic inequalities.

Know Your Linear Equations

Each question is a choice-summary multiple choice question that will present you with a linear equation and then make 4 statements about that equation. You must determine which of the 4 statements are true (if any) in regards to the equation.