RelationalAlgebra
Question 1 
How many tuples will be returned by the following relational algebra query?
∏_{x}(P.Y=R.Y ∧ R.V=V2)(P × R))  ∏_{x}(Q.Y=R.Y ∧ Q.T>2)(Q × R))
0  
1  
2  
3 
∏_{x}(σ_{(P.Y = R.Y ∧ R.V = V2)}(P × R))
σ_{(Q.Y = R.Y ∧ Q.T>2)}(Q × R)
∏_{x}(σ_{(Q.Y = R.Y ∧ Q.T>2)}(Q × R))
∏_{x}(σ_{(P.Y = R.Y ∧ R.V = V2)}(P × R))  ∏_{x}(σ_{(Q.Y = R.Y ∧ Q.T>2)}(Q × R))
Question 2 
Q: r⋈(σ<_{B<5} (s))
Let LOJ denote the natural left outerjoin operation. Assume that r and s contain no null values.
Which one of the following is NOT equivalent to Q?
σ_{B<5} (r ⨝ s)  
σ_{B<5} (r LOJ s)
 
r LOJ (σ_{B<5}(s))
 
σ_{B<5}(r) LOJ s 
Consider the following tables without NULL values.
Q: r⨝(σ_{B}<5(S))
The result of σ_{B<5}(S) is
The result of σ_{B<5}(S) is
Option (A):
The result of r⨝S is
The result of σ_{B<5}(r⨝S) is
Option (B):
The result of r LOJ S is
The result of σ_{B<5}(r LOJ S) is
Option (C):
The result of σ_{B<5}(S) is
Now, the result of r LOJ(σ_{B<5}(S))
Option (D):
The result of σ_{B<5}(r) is
Now, the result of σ_{B<5}(r) LOJ S is
Therefore, from the output of above four options, the results of options, the results of options (A), (B) and (D) are equivalent to Q.
Question 3 
(I) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∀v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}
(II) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}
(III) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] = v[DeptId]))}
Which of the above queries are safe?
(I) and (II) only  
(I) and (III) only  
(II) and (III) only  
(I), (II) and (III) 
(I) Gives EmpNames who do not belong to any Department. So, it is going to be a finite number of tuples as a result.
(II) Gives EmpNames who do not belong to some Department. This is also going to have finite number of tuples.
(III) Gives EmpNames who do not belong to same Department. This one will also give finite number of tuples.
All the expressions I, II and III are giving finite number of tuples. So, all are safe.
Question 4 
Consider a database that has the relation schema CR(StudentName, CourseName). An instance of the schema CR is as given below.
The following query is made on the database.
T1 ← π_{CourseName}(σ_{StudentName='SA'}(CR)) T2 ← CR ÷ T1
The number of rows in T2 is ____________.
4  
5  
6  
7 
The σ_{StudentName = 'SA'}(CR) will produce the following
⇾ The result of T1 ← π_{CourseName}(σ_{StudentName='SA'}(CR)) is
(2) T2 ← CR÷T1
⇾ We see that SA is enrolled for CA, CB and CC.
⇾ T2 will give the StudentNames those who have enrolled for all the CA, C, CC courses. So, the following Students are enrolled for the given 3 courses.
⇾ So, the output of T2 will have 4 rows.
Question 5 
Consider two relations R_{1}(A,B) with the tuples (1,5), (3,7) and R_{2}(A,C) = (1,7), (4,9). Assume that R(A,B,C) is the full natural outer join of R_{1} and R_{2}. Consider the following tuples of the form (A,B,C): a = (1.5,null), b = (1,null,7), c = (3,null,9), d = (4,7,null), e = (1,5,7), f = (3,7,null), g = (4,null,9). Which one of the following statements is correct?
R contains a,b,e,f,g but not c, d.
 
R contains all of a,b,c,d,e,f,g  
R contains e,f,g but not a,b  
R contains e but not f,g 
⋆ So, from the above resultant table, R contains e, f, g only but not a, b.
Question 6 
Suppose R_{1}(A, B) and R_{2}(C, D) are two relation schemas. Let r_{1} and r_{2} be the corresponding relation instances. B is a foreign key that refers to C in R_{2}. If data in r_{1} and r_{2} satisfy referential integrity constraints, which of the following is ALWAYS TRUE?
∏_{B} (r_{1})  ∏_{C} (r_{2}) = ∅  
∏_{C} (r_{2})  ∏_{B} (r_{1}) = ∅  
∏_{B} (r_{1}) = ∏_{C} (r_{2})  
∏_{B} (r_{1})  ∏_{C} (r_{2}) ≠ ∅ 
So we can say that r_{2}(C) is the superset of r_{1}(B).
So (subset  superset) is always empty.
Question 7 
Consider the following relations A, B, C.
How many tuples does the result of the following relational algebra expression contain? Assume that the schema of AUB is the same as that of A.
(AUB)⋈_{A.Id>40∨C.Id<15} C
7  
4  
5  
9 
Performs the cross product and selects the tuples whose A∙Id is either greater than 40 or C∙Id is less than 15. It yields:
Question 8 
Which of the following functional dependencies hold for relations R(A, B, C) and S(B, D, E):
B > A A > C
The relation R contains 200 tuples and the rel ation S contains 100 tuples. What is the maximum number of tuples possible in the natural join of R and S (R natural join S)
100  
200  
300  
2000 
R(A, B, C) – 200 tuples
S(B, D, E) – 100 tuples
FD’s:
B → A
A → C
― ‘B’ is primary key for R and foreign key of S from the given FDs.
― Maximum tuples in natural join of R and S is min(200, 100) = 100.
Question 9 
Let R and S be two relations with the following schema
 R(P,Q,R1,R2,R3)
S(P,Q,S1,S2)
Where {P, Q} is the key for both schemas. Which of the following queries are equivalent?
 I. Π_{P}(R ⨝ S)
II. Π_{P}(R) ⨝ Π_{P}(S)
III. Π_{P}(Π_{P,Q}(R) ∩ Π_{P,Q}(S))
IV. Π_{P}(Π_{P,Q}(R)  (Π_{P,Q}(R)  Π_{P,Q}(S)))
Only I and II  
Only I and III  
Only I, II and III  
Only I, III and IV 
We have two common columns in 'R' and 'S' which are 'P' and 'Q'.
(I) Both P and Q are used while doing the join, i.e., both P and Q are used to filter.
(II) Q is not used here for filtering. Natural join is done on all P's from R and all P's from S. So different from option (I).
(III) Through venn diagram it can be proved that A∩B = A  (AB).
So through above formula we can say that (III) and (IV) are equivalent.
So, finally (I), (III) and (IV) are equivalent.
Question 10 
Information about a collection of students is given by the relation studinfo(studId, name, sex). The relation enroll(studId, courseId) gives which student has enrolled for (or taken) that course(s). Assume that every course is taken by at least one male and at least one female student. What does the following relational algebra expression represent?
Π_{courseId}((Π_{studId}(σ_{sex="female"}(studInfo))×Π_{courseId}(enroll))enroll)Courses in which all the female students are enrolled.  
Courses in which a proper subset of female students are enrolled.  
Courses in which only male students are enrolled.
 
None of the above 
Option B: Yes, True. It selects the proper subset of female students which are enrolled because in the expression we are performing the Cartesian product.
Option C: False. It doesn’t shows (or) display the males students who are enrolled.
Question 11 
Let r be a relation instance with schema R = (A, B, C, D). We define r_{1} = Π_{A,B,C}(R) and r_{2} = Π_{A,D}(R). Let s = r_{1}*r_{2} where * denotes natural join. Given that the decomposition of r into r_{1} and r_{2} is lossy, which one of the following is TRUE?
s ⊂ r  
r ∪ s = r  
r ⊂ s  
r * s = s

Table r: R(A, B, C, D)
Table r_{1}: Π_{A,B,C}(R)
Table r_{2}: Π_{A,D}(R)
S = r_{1} * r_{2} (* denotes natural join)
Table S:
Table r ⊂ Table S
⇒ r ⊂ S
Question 12 
Let R_{1}(A,B,C) and R_{2}(D,E) be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in R_{1} referring to R_{2}. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances r_{1} and r_{2}. Which one of the following relational algebra expressions would necessarily produce an empty relation?
Π_{D}(r_{2})  Π_{C}(r_{1})  
Π_{C}(r_{1})  Π_{D}(r_{2})  
Π_{D}(r_{1}⨝_{C≠D}r_{2})  
Π_{C}(r_{1}⨝_{C=D}r_{2}) 
→ Based on referral integrity C is subset of values in R_{2} then,
Π_{C}(r_{1})  Π_{D}(r_{2}) results empty relation.
Question 13 
Consider the following relation schema pertaining to a students database:
Student (rollno, name, address) Enroll (rollno, courseno, coursename)
Where the primary keys are shown underlined. The number of tuples in the Student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student * Enroll), where '*' denotes natural join ?
8, 8  
120, 8  
960, 8  
960, 120 
→ In the question only enroll Id's are same with the student table.
→ The no. of minimum and maximum tuples is same i.e., 8, 8.
Question 14 
Consider the relation Student (name, sex, marks), where the primary key is shown underlined, pertaining to students in a class that has at least one boy and one girl. What does the following relational algebra expression produce? (Note: p is the rename operator).
The condition in join is "(sex = female ^ x = male ^ marks ≤ m)"
names of girl students with the highest marks
 
names of girl students with more marks than some boy student  
names of girl students with marks not less than some boy students
 
names of girl students with more marks than all the boy students 
Question 15 
With regard to the expressive power of the formal relational query languages, which of the following statements is true?
Relational algebra is more powerful than relational calculus.  
Relational algebra has the same power as relational calculus.  
Relational algebra has the same power as safe relational calculus.  
None of the above. 
A query can be formulated in safe Relational Calculus if and only if it can be formulated in Relational Algebra.
Question 16 
Suppose the adjacency relation of vertices in a graph is represented in a table Adj(X,Y). Which of the following queries cannot be expressed by a relational algebra expression of constant length?
List of all vertices adjacent to a given vertex  
List all vertices which have self loops  
List all vertices which belong to cycles of less than three vertices  
List all vertices reachable from a given vertex 
(b) Finding a self loop is also simple (Oop(X,X))
(c) If a → b, b → c then c!=a, finding this is also simple.
(d) List all the elements reachable from a given vertex is too difficult in Relational Algebra.
Question 17 
Let r and s be two relations over the relation schemes R and S respectively, and let A be an attribute in R. Then the relational algebra expression σ_{A=a}(r⋈s) is always equal to
σ_{A=a} (r)  
r  
σ_{A=a} (r)⨝s  
None of the above 
(b) Display table
(c) A=a for all Tables r and s