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.
