Relational-Algebra

Question 1

Give a relational algebra expression using only the minimum number of operators from (∪, −) which is equivalent to R ∩ S.

A
Out of syllabus (For explanation see below)
Question 1 Explanation: 
R - (R - S)
→ 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?

A
R1 ∪ R2
B
R1 × R2
C
R1 - R2
D
R1 ∩ R2
Question 2 Explanation: 
The join here will be selecting only those tuples where A=C and B=D, meaning it is the intersection.
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?

A
σC1C1(R1)) → σC2C2(R1))
B
σC1A1(R1)) → σA1C1(R1))
C
σC1(R1 ∪ R2) → σC1(R1) ∪ σC1
D
πA1C1(R1)) → σC1A1(R1))
Question 3 Explanation: 
If the selection condition is on attribute A2, then we cannot replace it by RHS as there will not be any attribute A2 due to projection of A1 only.
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

A
m + n and 0
B
mn and 0
C
m + n and |m – n|
D
mn and m + n
Question 4 Explanation: 
For maximum:
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
σ(A=10∨B=20) (r)
B
σ(A=10) (r) ∪ σ(B=20) (r)
C
σ(A=10) (r) ∩ σ(B=20) (r)
D
σ(A=10) (r) - σ(B=20) (r)
Question 5 Explanation: 
The given relational algebra expression represents tuples having A=10 and B=20 which is equivalent to
σ(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 (σ, π, ×, ⋈, ∪, ∩, -)?

A
Department address of every employee
B
Employees whose name is the same as their department name
C
The sum of all employees’ salaries
D
All employees of a given department
Question 6 Explanation: 
The sum of all employee’s salaries can’t be represented by using the given six basic algebra operation. If we want to represent sum of salaries then we need to use aggregation operator.
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?

A
List of all vertices adjacent to a given vertex
B
List all vertices which have self loops
C
List all vertices which belong to cycles of less than three vertices
D
List all vertices reachable from a given vertex
Question 7 Explanation: 
(a) Finding a adjacency vertex for the given vertex is too simple i.e., Adj(X,Y).
(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=a (r)
B
r
C
σA=a (r)⨝s
D
None of the above
Question 8 Explanation: 
(a) A=a for all r
(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?

A
Relational algebra is more powerful than relational calculus.
B
Relational algebra has the same power as relational calculus.
C
Relational algebra has the same power as safe relational calculus.
D
None of the above.
Question 9 Explanation: 
Relational algebra has the same power as safe relational calculus.
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?

A
ΠD(r2) - ΠC(r1)
B
ΠC(r1) - ΠD(r2)
C
ΠD(r1C≠Dr2)
D
ΠC(r1C=Dr2)
Question 10 Explanation: 
C be a foreign key in R1 referring to R2.
→ 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 ?

A
8, 8
B
120, 8
C
960, 8
D
960, 120
Question 11 Explanation: 
Natural join is a JOIN operation that creates an implicit join cause for you based on common columns in the two tables being joined.
→ 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)"

A
names of girl students with the highest marks
B
names of girl students with more marks than some boy student
C
names of girl students with marks not less than some boy students
D
names of girl students with more marks than all the boy students
Question 12 Explanation: 
In the operator '-' between the two subexpression gives the names of all female students whose marks are greater than the all the male students of the class.
Question 13
The following relation records the age of 500 employees of a company, where empNo (indicating the employee number) is the key: empAge (empNo, age) Consider the following relational algebra expression:

What does the above expression generate?
A
Employee numbers of all employees whose age is not the maximum.
B
Employee numbers of only those employees whose age is the maximum.
C
Employee numbers of all employees whose age is not the minimum.
D
Employee numbers of only those employees whose age is more than the age of exactly one other employee.
Question 13 Explanation: 
The given relational algebra expression will result in employees whose age is not minimum. The given conditional join, joins the relations if the age is greater than any of the ages mentioned in the database.
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 :

A
2000
B
2500
C
4500
D
5000
Question 14 Explanation: 
r1⋈ r2 is a join operation done on the common attribute R. So this can have 2000 tuples.
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 ?

A
50
B
100
C
150
D
200
Question 15 Explanation: 
σA≤100(r), r has 1000 tuples.
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-nameb-city = "Agra" ⋀ bal < 0 (branch ⋈ (account ⋈ depositor)  

Which one of the following queries is the most efficient version of the above query?

A
Пc-namebal < 0b-city = “Agra” branch ⋈ account) ⋈ depositor)
B
Пc-nameb-city = “Agra”branch ⋈ (σbal < 0 account ⋈ depositor))
C
Пc-nameb-city = “Agra” branch ⋈ σb-city = “Agra” ⋀ bal < 0 account) ⋈ depositor)
D
Пc-nameb-city = “Agra” ⋀ bal < 0 account ⋈ depositor))
Question 16 Explanation: 
Answer should be (A) and not (B), because we are doing a join between two massive tables whereas in (A) we are doing join between relatively smaller table and larger one and the output that this inner table gives (which is smaller in comparison to join that we are doing in (B)) is used for join with depositor table with the selection condition.
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?

A
s ⊂ r
B
r ∪ s = r
C
r ⊂ s
D
r * s = s
Question 17 Explanation: 
Let us consider an example:
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
There are 17 questions to complete.

Access quiz wise question and answers by becoming as a solutions adda PRO SUBSCRIBER with Ad-Free content

Register Now

If you have registered and made your payment please contact solutionsadda.in@gmail.com to get access