Relational-Algebra
Question 1 |
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 2 |
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 3 |
Suppose R1(A, B) and R2(C, D) are two relation schemas. Let r1 and r2 be the corresponding relation instances. B is a foreign key that refers to C in R2. If data in r1 and r2 satisfy referential integrity constraints, which of the following is ALWAYS TRUE?
∏B (r1) - ∏C (r2) = ∅ | |
∏C (r2) - ∏B (r1) = ∅ | |
∏B (r1) = ∏C (r2) | |
∏B (r1) - ∏C (r2) ≠ ∅ |
So we can say that r2(C) is the superset of r1(B).
So (subset - superset) is always empty.
Question 4 |
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 5 |
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 6 |
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 7 |
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 8 |
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 9 |
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 10 |
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 11 |
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 12 |
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 13 |
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 14 |
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 15 |
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 16 |
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 17 |
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.