Relational Algebra - Database Systems

If you can't do all of them then please do not attempt the question.

ANSWER TEMPLATE:

Symbol to choose the symbol that you want such as ( ) (or copy from here).

For conditions, to make text appear slightly above (superscript) or below (subscript) your regular text, you can use keyboard shortcuts or use the icons on the HOME menu, under font panel.

1. Select the text or number that you want to make it superscript or subscript.
2. For superscript, press Ctrl, Shift, and the Plus sign (+) at the same time. For subscript, press Ctrl and the Equal sign (=) at the same time.

Q1- Student Enrolment

1. List the CNos of courses in which at least one student is enrolled.

2. List the CNos of courses in which no student is enrolled.

3. List the names of students and the names of courses they enrolled to.

4. List the StudID of students who are enrolled for Database Systems or Analysis of Algorithms.

5. List the StudID of students who are enrolled for both Database Systems and Analysis of Algorithms

6. List the CNos of courses in which all students are enrolled.

7. List the CNos of courses in which all COMP major students are enrolled.

Q1. Consider the following relational database schema Student (Studio, name, address, gender, major) Course (CNO CName) Enrolled (StudID, CNo) Where the primary keys are underlined, foreign keys are bold. Write a Relational Algebra expression for each of the following queries. The queries may have multiple solutions. Hints provided here helps you to find at least 1 or 2 solutions, but it does not mean that any solution not implied by the hint is wrong. In fact, use your creativity and come up with different solutions. You can either nest RA expressions in a bigger expression or you can write your answers in several lines and use intermediate relations to store intermediate results. 1. List the CNos of courses in which at least one student is enrolled. (Hint: if at least 1 student has enrolled in a course, CNo of that course can be find in Enrolled relation. If a course has no enrollment, CNo cannot be find in Enrolled relation. Moreover, do not forget that a Relational Algebra Expression always gives you a set not a multiset (a set does not have any duplicate value. In fact, RA always gives you DISTINCT result)) 2. List the CNos of courses in which no student is enrolled. (Hint: list of courses with at least one enrollment (previous query) minus list of all courses, you can get list of courses with no enrollment. Do not forget that when you use Set Operations (Union, Intersection, Difference), the relations must be compatible (same number of attributes and same type) 3. List the names of students and the names of courses they enrolled to. (Hint: names of students can be found in Student relation, Names of courses are in Courses relation, and enrollment of students in courses is in Enrolled relation. So we need to join all 3 relations to extract desired output.) 4. List the Studid of students who are enrolled for 'Database Systems' or 'Analysis of Algorithms'. (Hint: first we must find what are CNo's of these two courses, then the result joined with Enrolled, we can find what students enrolled in them) 5. List the StudiD of students who are enrolled for both 'Database Systems' and 'Analysis of Algorithms'. (Hint: there can be multiple solutions: one solution is to find list of all students who enrolled in 'Database Systems' intersect with the students enrolled in 'Analysis of Algorithms'. Another solution: find the CNo's of both courses and use division operation to divide Enrolled relation by list that includes CNo's of these two courses) 6. List the CNos of courses in which all students are enrolled. (Hint: When you see the word 'all' that gives a hint that you could use division operation to find the answer to the query. When you use division on two relations, e.g. Alm, n) divided by B(n) (A + B), the answer is Rm), pay attention that attributes of B must be subset of the attributes of A. That is, {n} c{m, n}. If (a,b,c,d) and T(c,d) you can divide 5 by T (S:T) and if K(d,p), you cannot divide 5 by K because the set of attributes of K is not a subset of s, because p is not an attribute of S. However, Wid(K), we can divide S by W, because W includes only 1 attribute, d, which is a subset of set of attributes of 5) 7. List the CNos of courses in which all 'COMP'major students are enrolled. (Hint: does 'all give you any hint?) Q1. Consider the following relational database schema Student (Studio, name, address, gender, major) Course (CNO CName) Enrolled (StudID, CNo) Where the primary keys are underlined, foreign keys are bold. Write a Relational Algebra expression for each of the following queries. The queries may have multiple solutions. Hints provided here helps you to find at least 1 or 2 solutions, but it does not mean that any solution not implied by the hint is wrong. In fact, use your creativity and come up with different solutions. You can either nest RA expressions in a bigger expression or you can write your answers in several lines and use intermediate relations to store intermediate results. 1. List the CNos of courses in which at least one student is enrolled. (Hint: if at least 1 student has enrolled in a course, CNo of that course can be find in Enrolled relation. If a course has no enrollment, CNo cannot be find in Enrolled relation. Moreover, do not forget that a Relational Algebra Expression always gives you a set not a multiset (a set does not have any duplicate value. In fact, RA always gives you DISTINCT result)) 2. List the CNos of courses in which no student is enrolled. (Hint: list of courses with at least one enrollment (previous query) minus list of all courses, you can get list of courses with no enrollment. Do not forget that when you use Set Operations (Union, Intersection, Difference), the relations must be compatible (same number of attributes and same type) 3. List the names of students and the names of courses they enrolled to. (Hint: names of students can be found in Student relation, Names of courses are in Courses relation, and enrollment of students in courses is in Enrolled relation. So we need to join all 3 relations to extract desired output.) 4. List the Studid of students who are enrolled for 'Database Systems' or 'Analysis of Algorithms'. (Hint: first we must find what are CNo's of these two courses, then the result joined with Enrolled, we can find what students enrolled in them) 5. List the StudiD of students who are enrolled for both 'Database Systems' and 'Analysis of Algorithms'. (Hint: there can be multiple solutions: one solution is to find list of all students who enrolled in 'Database Systems' intersect with the students enrolled in 'Analysis of Algorithms'. Another solution: find the CNo's of both courses and use division operation to divide Enrolled relation by list that includes CNo's of these two courses) 6. List the CNos of courses in which all students are enrolled. (Hint: When you see the word 'all' that gives a hint that you could use division operation to find the answer to the query. When you use division on two relations, e.g. Alm, n) divided by B(n) (A + B), the answer is Rm), pay attention that attributes of B must be subset of the attributes of A. That is, {n} c{m, n}. If (a,b,c,d) and T(c,d) you can divide 5 by T (S:T) and if K(d,p), you cannot divide 5 by K because the set of attributes of K is not a subset of s, because p is not an attribute of S. However, Wid(K), we can divide S by W, because W includes only 1 attribute, d, which is a subset of set of attributes of 5) 7. List the CNos of courses in which all 'COMP'major students are enrolled. (Hint: does 'all give you any hint?)