Relational-Algebra
Question 1 |
Give a relational algebra expression using only the minimum number of operators from (∪, −) which is equivalent to R ∩ S.
Out of syllabus (For explanation see below) |
→ No need of using Union operation here. → In question they gave (∪, −) but we don't use both.
→ And also they are saying that only the minimum number of operators from (∪, −) which is equivalent to R ∩ S.
So, the expression is minimal.
Question 2 |
Given two union compatible relations R1(A,B) and R2(C,D), what is the result of the operation R1A = CAB = DR2?
R1 ∪ R2 | |
R1 × R2 | |
R1 - R2 | |
R1 ∩ R2 |
Question 3 |
Which of the following query transformations (i.e. replacing the l.h.s. expression by the r.h.s. expression) is incorrect? R1 and R2 are relations, C1, C2 are selection conditions and A1, A2 are attributes of R1?
σC1(σC1(R1)) → σC2(σC2(R1)) | |
σC1(σA1(R1)) → σA1(σC1(R1)) | |
σC1(R1 ∪ R2) → σC1(R1) ∪ σC1 | |
πA1(σC1(R1)) → σC1(σA1(R1)) |
Question 4 |
Consider the join of a relation R with a relation S. If R has m tuples and S has n tuples then the maximum and minimum sizes of the join respectively are
m + n and 0 | |
mn and 0 | |
m + n and |m – n| | |
mn and m + n |
Suppose there is no common attribute in R and S due to which natural join will act as cross product. So then in cross product total no. of tuples will be mn.
For minimum:
Suppose there is common attribute in R and S, but none of the row of R matches with rows of S then minimum no. of tuples will be 0.
Question 5 |
The relational algebra expression equivalent to the following tuple calculus expression:
{t| t ∈ r ∧(t[A] = 10 ∧ t[B] = 20)} isσ(A=10∨B=20) (r) | |
σ(A=10) (r) ∪ σ(B=20) (r) | |
σ(A=10) (r) ∩ σ(B=20) (r) | |
σ(A=10) (r) - σ(B=20) (r) |
σ(A=10) (r) ∩ σ(B=20) (r)
Question 6 |
Given the relations
employee (name, salary, deptno) and department (deptno, deptname, address)
Which of the following queries cannot be expressed using the basic relational algebra operations (σ, π, ×, ⋈, ∪, ∩, -)?
Department address of every employee | |
Employees whose name is the same as their department name | |
The sum of all employees’ salaries | |
All employees of a given department |
Question 7 |
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 8 |
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
Question 9 |
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 10 |
Let R1(A,B,C) and R2(D,E) be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in R1 referring to R2. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances r1 and r2. Which one of the following relational algebra expressions would necessarily produce an empty relation?
ΠD(r2) - ΠC(r1) | |
ΠC(r1) - ΠD(r2) | |
ΠD(r1⨝C≠Dr2) | |
ΠC(r1⨝C=Dr2) |
→ Based on referral integrity C is subset of values in R2 then,
ΠC(r1) - ΠD(r2) results empty relation.
Question 11 |
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 12 |
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 13 |
What does the above expression generate?
Employee numbers of all employees whose age is not the maximum. | |
Employee numbers of only those employees whose age is the maximum. | |
Employee numbers of all employees whose age is not the minimum. | |
Employee numbers of only those employees whose age is more than the age of exactly one other employee. |
Question 14 |
Consider the relations r1(P, Q, R) and r2(R, S, T) with primary keys P and R respectively. The relation r1 contains 2000 tuples and r2 contains 2500 tuples. The maximum size of the join r1⋈ r2 is :
2000 | |
2500 | |
4500 | |
5000 |
Question 15 |
Consider a selection of the form σA≤100(r), where r is a relation with 1000 tuples. Assume that the attribute values for A among the tuples are uniformly distributed in the interval [0, 500]. Which one of the following options is the best estimate of the number of tuples returned by the given selection query ?
50 | |
100 | |
150 | |
200 |
Values for A among the tuples are uniformly distributed in the interval [0, 500]. This can be split to 5 mutually exclusive and exhaustive intervals of same width of 100 ([0-100], [101-200], [201-300], [301-400], [401-500], 0 makes the first interval larger - this must be type in this question) and we can assume all of them have same number of values due to uniform distribution. So no. of tuples with A value in first interval should be,
Total no. of tuples/5 = 1000/5 = 200
Question 16 |
Consider the following relation schemas:
b-Schema = (b-name, b-city, assets)
a-Schema = (a-num, b-name, bal)
d-Schema = (c-name, a-number)
Let branch, account and depositor be respectively instances of the above schemas. Assume that account and depositor relations are much bigger than the branch relation.
Consider the following query:
Пc-name (σb-city = "Agra" ⋀ bal < 0 (branch ⋈ (account ⋈ depositor)
Which one of the following queries is the most efficient version of the above query?
Пc-name (σbal < 0 (σb-city = “Agra” branch ⋈ account) ⋈ depositor) | |
Пc-name (σb-city = “Agra”branch ⋈ (σbal < 0 account ⋈ depositor)) | |
Пc-name (σb-city = “Agra” branch ⋈ σb-city = “Agra” ⋀ bal < 0 account) ⋈ depositor) | |
Пc-name (σb-city = “Agra” ⋀ bal < 0 account ⋈ depositor)) |
Options (C) and (D) are invalid as there is no b-city column in a-schema.
Question 17 |
Let r be a relation instance with schema R = (A, B, C, D). We define r1 = ΠA,B,C(R) and r2 = ΠA,D(R). Let s = r1*r2 where * denotes natural join. Given that the decomposition of r into r1 and r2 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 r1: ΠA,B,C(R)
Table r2: ΠA,D(R)
S = r1 * r2 (* denotes natural join)
Table S:
Table r ⊂ Table S
⇒ r ⊂ S