Consider the following schema: Students(banerID: string, lName: string, fName: s

Question

Consider the following schema:

Students(banerID: string, lName: string, fName: string, email: string, address: string, phoneNum: string, GPA: real)
Courses( cNum:string, cname:string, creditHrs: integer, prerequisites: string)
Enroll(bannerID:string, cNum: string, semester: string, section:string, grade:string)
Write the following queries in Relational Algebra.

1. “Find the names of Students.”
2. “Find the Banner IDs of students who enrolled in CS101.”
3. “Find the names and addresses of students who enrolled in CS101.”
4. “Find the names and addresses of students who enrolled in CS101Fall 2010.”
5. “Find the Banner IDs of students who have enrolled in both CS101 and CS102”.
6. “Find the Banner IDs of students who have enrolled in CS101 or CS102”.
7. “Find the Banner IDs of students who have enrolled in CS101 but not CS102”.
8. “Find the Banner IDs of students who have enrolled in at least one course”.
9. “Find the Banner IDs of students who have enrolled in at least two courses”.
10. “Find the Banner IDs of students who have not enrolled in any course yet Fall 2010”.

Explanation / Answer

It's been a while that I haven't used Relational Algebra notation so I hope you don't mind that I write this in SQL-like form. Sorry! You can easily translate/convert it , there's plenty of online material to help with that too. I hope this helps a bit :)

1. “Find the names of Students.”
   select fName, lName from Students;
2. “Find the Banner IDs of students who enrolled in CS101.”
   select bannerID from Enroll where cNum = 'CS101';
3. “Find the names and addresses of students who enrolled in CS101.”
   select fName, lName, address from Students where bannerID IN (select bannerID from Enroll where cNum='CS101')
4. “Find the names and addresses of students who enrolled in CS101Fall 2010.”
   select fName, lName, address from Students where bannerID IN (select bannerID from Enroll where cNum='CS101' AND semester='Fall 2010');
5. “Find the Banner IDs of students who have enrolled in both CS101 and CS102”.
   select bannerID from Enroll where cNum='CS101'
   intersect
   select bannerID from Enroll where cNum='CS102'
6. “Find the Banner IDs of students who have enrolled in CS101 or CS102”.
   select bannerID from Enroll where cNum='CS101'
   union
   select bannerID from Enroll where cNum='CS102'
7. “Find the Banner IDs of students who have enrolled in CS101 but not CS102”.
   select bannerID from Enroll where cNum='CS101'
   difference (except or minus)
   select bannerID from Enroll where cNum='CS102'
8. “Find the Banner IDs of students who have enrolled in at least one course”.
   these would be the students registered with the university minus the students
   select DISTINCT bannerID from Enroll
9. “Find the Banner IDs of students who have enrolled in at least two courses”.
   select distinct bannerID from Enroll group by bannerID having count(cNum) >= 2
10. “Find the Banner IDs of students who have not enrolled in any course yet Fall 2010”.
   select banner ID from Enroll
   difference
   select dstinct bannerID from enroll where semester='Fall 2010'

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.