Relational Algebra Questions and Answers in DBMS

Master the fundamentals of relational algebra with our detailed step-by-step solutions to common DBMS questions. This guide is perfect for students preparing for GATE, UGC NET, and technical interviews.

• Understand core concepts like selection, projection, and join operations.
• Learn through real-world examples and common queries.
• Enhance your database skills with these solved questions.

 

Relational Algebra Questions in DBMS

Q.1 Which of the following relational algebraic operation is not a commutative operation?

• (a) Union
• (b) Intersection
• (c) Selection
• (d) Projection

Solution:

To determine which relational algebra operation is not commutative, let us analyze each option in detail:

1. Union (∪)

• Definition: Union combines tuples from two relations, eliminating duplicates.
• Commutativity Property:

  R1 ∪ R2 = R2 ∪ R1

  The order of relations does not matter; the result is the same.

• Conclusion: Union is commutative.

2. Intersection (∩)

• Definition: Intersection retrieves tuples that are common in both relations.
• Commutativity Property:

  R1 ∩ R2 = R2 ∩ R1

  The order of relations does not matter; the result is the same.

• Conclusion: Intersection is commutative.

3. Selection (σ)

• Definition: Selection retrieves tuples that satisfy a given predicate from a relation.
• Commutativity Property:

  σP1(σP2(R)) = σP2(σP1(R))

  The order of applying predicates does not matter; the result is the same.

• Conclusion: Selection is commutative.

4. Projection (π)

• Definition: Projection retrieves specific columns (attributes) from a relation.
• Commutativity Property:If two projections πA and πB are applied, the order matters:

  πA(πB(R)) ≠ πB(πA(R))

  If A and B are different subsets of attributes, projecting A first may discard attributes required by B, and vice versa.

• Conclusion: Projection is not commutative.

Answer:

The relational algebraic operation that is not commutative is (d) Projection.

Q.2 Consider a banking database with the following table with three attributes loans (br_name, loan_no, amount). Find the appropriate query for the given statement below:

“Find the loan number for each loan of an amount greater than 20000”

• (a) {t | t ∈ loans ∧ t[amount] > 20000} where ‘t’ is a tuple
• (b) {t | ∃ s ∈ loans (t[loan_no] = s[loan_no] ∧ s[amount] > 20000)}
• (c) {t | ∀ s ∈ loans (t[loan_no] = s[loan_no] ∧ s[amount] > 20000)}
• (d) None of the above

Solution with Step-by-Step Breakdown:

To solve this question, we need to determine the correct relational algebra query that retrieves the loan numbers for loans where the amount is greater than 20,000. Let us analyze each option in detail:

Step 1: Understand Option (a)

• Query: {t | t ∈ loans ∧ t[amount] > 20000} where ‘t’ is a tuple.
• What this query does:
  • Selects all tuples t from the loans relation where the `amount` attribute satisfies the condition `amount > 20000`.
  • This is equivalent to a selection operation in relational algebra: σamount > 20000(loans).
• Issue:
  • The result will include all attributes of the tuples (`br_name`, `loan_no`, and `amount`), not just the loan_no attribute as required by the problem.
  • The query does not explicitly restrict the output to only the loan_no.
• Conclusion: Option (a) is incorrect because it retrieves full tuples instead of only the loan numbers.

Step 2: Analyze Option (b)

• Query: {t | ∃ s ∈ loans (t[loan_no] = s[loan_no] ∧ s[amount] > 20000)}.
• What this query does:
  • Uses an existential quantifier (∃) to state that:
    • There exists a tuple s in the `loans` table where:
    • The loan number of s matches the loan number of t, and
    • The amount in s is greater than 20,000.
  • This ensures that the result will only contain loan numbers for loans where the `amount > 20000`.
• Why this works:
  • This query accurately retrieves only the loan_no values as required by the problem statement.
• Conclusion: Option (b) is correct.

Step 3: Evaluate Option (c)

• Query: {t | ∀ s ∈ loans (t[loan_no] = s[loan_no] ∧ s[amount] > 20000)}.
• What this query does:
  • Uses a universal quantifier (∀) to state that:
    • For all tuples s in the `loans` table, the loan number of t matches the loan number of s, and
    • The amount in s is greater than 20,000.
• Issue:
  • This query implies that the condition s[amount] > 20000 must hold true for all tuples in the `loans` table.
  • This is unnecessarily restrictive and does not align with the problem requirement.
• Conclusion: Option (c) is incorrect because it does not properly model the problem’s requirements.

Step 4: Review Option (d)

• Query: None of the above.
• Analysis:
  • Since Option (b) is correct, this option cannot be the answer.
• Conclusion: Option (d) is incorrect.

Step 5: Final Answer

The correct query is (b) {t | ∃ s ∈ loans (t[loan_no] = s[loan_no] ∧ s[amount] > 20000)}.

Reason: Option (b) accurately retrieves only the loan numbers for loans where the amount is greater than 20,000, fulfilling the requirements of the problem statement.

Q.3 Which of the following is wrong?

• (a) π_{L1 ∪ L2} (E₁ ⨝_θ E₂) = (π_L1 (E₁)) ⨝_θ (π_L1 (E₂))
• (b) σ_P(E₁ – E₂) = σ_P (E₁) – E₂
• (c) σ_{θ1 ∧ θ2} (E) = σ_θ1 (σ_θ2 (E))
• (d) E₁ ⨝_θ E₂ = E₂ ⨝_θ1 E₁

Question

Select the relational expression which could possibly return the following result:

a	c
1	2
2	3

• (a) πa,c (σa=c R)
• (b) πa<c (πa,c R)
• (c) πa<2 R
• (d) σa<c (πa,c R)

Solution

Let’s analyze each option step by step:

Option (a): πa,c (σa=c R)

Analysis:

• The selection operation σa=c R retrieves tuples where a is equal to c.
• In the given result, a is not equal to c for any tuple (e.g., 1 ≠ 2 and 2 ≠ 3).
• Therefore, this operation cannot produce the given result.

Conclusion: This option is incorrect.

Option (b): πa<c (πa,c R)

Analysis:

• The inner projection πa,c R retrieves the attributes a and c from relation R.
• The outer projection πa<c attempts to filter rows where a < c. However, projection (π) is not meant to filter rows based on conditions. Instead, it selects specific columns.
• This makes the expression invalid relational algebra syntax.

Conclusion: This option is invalid.

Option (c): πa<2 R

Analysis:

• The condition a<2 is applied to filter rows, but projection (π) cannot directly handle conditions like this.
• Projection is used to select columns, not to filter rows based on conditions.
• This makes the expression invalid relational algebra syntax.

Conclusion: This option is invalid.

Option (d): σa<c (πa,c R)

Analysis:

• The inner projection πa,c R selects the attributes a and c from relation R.
• The selection σa<c filters rows where a<c.
• For the given result:
  • Row 1: a=1, c=2 satisfies a<c.
  • Row 2: a=2, c=3 satisfies a<c.
• This operation matches the given result.

Conclusion: This option is correct.

Answer

The correct relational expression is (d) σa<c (πa,c R).

Relational Algebra: Select the Relational Expression

Question

Select the relational expression which could possibly return the following result:

a	c
1	2
2	3

• (a) πa,c (σa=c R)
• (b) πa<c (πa,c R)
• (c) πa<2 R
• (d) σa<c (πa,c R)

Solution

Let’s analyze each option step by step:

Option (a): πa,c (σa=c R)

Analysis:

• The selection operation σa=c R retrieves tuples where a is equal to c.
• In the given result, a is not equal to c for any tuple (e.g., 1 ≠ 2 and 2 ≠ 3).
• Therefore, this operation cannot produce the given result.

Conclusion: This option is incorrect.

Option (b): πa<c (πa,c R)

Analysis:

• The inner projection πa,c R retrieves the attributes a and c from relation R.
• The outer projection πa<c attempts to filter rows where a < c. However, projection (π) is not meant to filter rows based on conditions. Instead, it selects specific columns.
• This makes the expression invalid relational algebra syntax.

Conclusion: This option is invalid.

Option (c): πa<2 R

Analysis:

• The condition a<2 is applied to filter rows, but projection (π) cannot directly handle conditions like this.
• Projection is used to select columns, not to filter rows based on conditions.
• This makes the expression invalid relational algebra syntax.

Conclusion: This option is invalid.

Option (d): σa<c (πa,c R)

Analysis:

• The inner projection πa,c R selects the attributes a and c from relation R.
• The selection σa<c filters rows where a<c.
• For the given result:
  • Row 1: a=1, c=2 satisfies a<c.
  • Row 2: a=2, c=3 satisfies a<c.
• This operation matches the given result.

Conclusion: This option is correct.

Answer

The correct relational expression is (d) σa<c (πa,c R).

---

Relational Algebra: Valid Equivalence

Question

If P and Q are predicates and e is the relational algebra expression, then which of the following equivalence is valid?

• (a) σP(σQ(e)) = σQ(σP(e))
• (b) σP(σQ(e)) = σP∧Q(e)
• (c) σQ(σP(e)) = σP∧Q(e)
• (d) All of the above

Solution

Option (a): σP(σQ(e)) = σQ(σP(e))

Analysis:

• Selection (σ) is commutative, meaning the order of applying predicates P and Q does not matter.
• Thus, σP(σQ(e)) is equivalent to σQ(σP(e)).

Conclusion: This equivalence is valid.

Option (b): σP(σQ(e)) = σP∧Q(e)

Analysis:

• The predicates P and Q can be combined into a single predicate P∧Q when applying selection.
• This equivalence simplifies the selection operation by combining predicates.

Conclusion: This equivalence is valid.

Option (c): σQ(σP(e)) = σP∧Q(e)

Analysis:

• Similar to option (b), the predicates P and Q can be combined into a single predicate P∧Q when applying selection.
• The order of predicates does not matter due to the commutativity of selection.

Conclusion: This equivalence is valid.

Option (d): All of the above

Analysis:

• As all the above options (a), (b), and (c) are valid equivalences, this option is correct.

Conclusion: This option is correct.

Answer

The correct answer is (d) All of the above.

Q.13 The relational algebra expression equivalent to the following tuple calculation expression:

{t | t ∈ r ∧ (t[A] = 10 ∧ t[B] = 20)}

• (a) σ_{A=10 ∨ B=20} (r)
• (b) σ_A=10 (r) ∪ σ_B=20 (r)
• (c) σ_A=10 (r) ∩ σ_B=20 (r)
• (d) σ_A=10 – σ_B=20 (r)

Solution

Expression Analysis

The tuple calculation expression specifies:

• Tuples t that belong to relation r.
• These tuples satisfy both conditions simultaneously: t[A] = 10 and t[B] = 20.

To determine the equivalent relational algebra expression, let us analyze each option:

Option (a): σ_{A=10 ∨ B=20} (r)

• This option retrieves tuples where either A=10 or B=20.
• It does not enforce that both conditions are satisfied simultaneously.
• Conclusion: This option is incorrect.

Option (b): σ_A=10 (r) ∪ σ_B=20 (r)

• This option retrieves tuples where A=10 or B=20 by taking the union of two selections.
• It does not enforce that both conditions are satisfied simultaneously.
• Conclusion: This option is incorrect.

Option (c): σ_A=10 (r) ∩ σ_B=20 (r)

• This option retrieves tuples where both A=10 and B=20 are satisfied by taking the intersection of two selections.
• This matches the tuple calculation expression.
• Conclusion: This option is correct.

Option (d): σ_A=10 – σ_B=20 (r)

• This option retrieves tuples where A=10 but not B=20.
• It does not satisfy the requirement that both conditions must hold simultaneously.
• Conclusion: This option is incorrect.

Answer

The correct relational algebra expression is:

(c) σ_A=10 (r) ∩ σ_B=20 (r)

Q.14 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 the employees of a given department

Solution

Key Concepts

The basic relational algebra operations are:
Selection (σ), Projection (π), Cartesian Product (×), Join (⨝), Union (∪), Intersection (∩), and Difference (-).
These operations do not support aggregation (e.g., summing values or counting rows), which requires extensions to relational algebra.

Analysis of Each Query

(a) Department address of every employee

• To find the department address for every employee, we can perform a join between the Employee and Department relations on the common attribute deptno:
• Relational algebra expression:
  πaddress(Employee ⨝ Department)
• This query can be expressed using basic relational algebra operations.
• Conclusion: This query can be expressed.

(b) Employees whose name is the same as their department name

• To retrieve employees where name matches deptname, we can perform a selection on the joined relations:
• Relational algebra expression:
  σname=deptname(Employee ⨝ Department)
• This query can be expressed using basic relational algebra operations.
• Conclusion: This query can be expressed.

(c) The sum of all employees’ salaries

• Summing values (like total salary) requires an aggregate function, which is not part of the basic relational algebra operations.
• Basic relational algebra does not support operations like SUM, AVG, MIN, MAX, COUNT, etc.
• Conclusion: This query cannot be expressed.

(d) All the employees of a given department

• To retrieve employees of a specific department, we can perform a selection on the deptno attribute:
• Relational algebra expression:
  σdeptno=x(Employee), where x is the department number.
• This query can be expressed using basic relational algebra operations.
• Conclusion: This query can be expressed.

Answer

The query that cannot be expressed using basic relational algebra operations is:

(c) The sum of all employees’ salaries

Q.16 What does the following query do?

{S.rollNo, S.name | student(S) ∧ S.sex = ‘F’ ∧ (∃d) (department(d) ∧ d.name = ‘Maths’ ∧ d.deptId = S.deptNo)}

• (a) Name of all students in Maths department
• (b) Obtain the rollNo and name of all girl students in the Maths department
• (c) Obtain the name of all male students in the Maths department
• (d) None of the above

Solution:

To understand this query, let us break it into parts and analyze step by step:

1. Analyze the First Condition:

The query specifies: student(S) ∧ S.sex = 'F'

• This means we are looking for all tuples S where the sex attribute is 'F' (female).
• Thus, the query is restricted to female students only.

2. Analyze the Existential Condition:

The next part of the query includes an existential quantifier:

(∃d) (department(d) ∧ d.name = 'Maths' ∧ d.deptId = S.deptNo)

• This means there exists a department d where:
  • The department name is 'Maths'.
  • The deptId of the department matches the deptNo of the student S.
• In simple terms, this restricts the query to female students who belong to the Maths department.

3. Analyze the Result:

The query specifies the result as:

{S.rollNo, S.name}

• This indicates that the output will include only the roll number (rollNo) and name (name) of the students who satisfy the above conditions.

4. Final Query Behavior:

Combining all parts, the query retrieves the rollNo and name of all female students who are enrolled in the Maths department.

Answer:

The correct answer is:

• (b) Obtain the rollNo and name of all girl students in the Maths department

Q.17

Let R₁(A, B, C) and R₂(D, E) be two relation schemas, where:

• A is the primary key in R₁.
• D is the primary key in R₂.
• C is a foreign key in R₁ referring to D in R₂.

Suppose there is no violation of the above referential integrity constraint in the corresponding relational instances r₁ and r₂. Which one of the following relational algebra expressions would necessarily produce an empty relation?

• (a) π_D(r₂) – π_C(r₂)
• (b) π_C(r₁) – π_D(r₂)
• (c) π_D(r₁ ⨝_C≠D r₂)
• (d) π_C(r₁ ⨝_C≠D r₂)

Solution:

To determine which expression will necessarily produce an empty relation, let us analyze each option in detail:

1. Referential Integrity Constraint:

Since C in R₁ is a foreign key referring to D in R₂, the following is guaranteed:

• Every value of C in r₁ must exist as a value of D in r₂.
• Thus, πC(r1) ⊆ πD(r2).

2. Option Analysis:

(a) π_D(r₂) – π_C(r₂)

• This expression computes the difference between all D values in r₂ and all C values in r₂.
• Since C is not an attribute of R₂, this expression is invalid.

(b) π_C(r₁) – π_D(r₂)

• This computes the set of C values in r₁ that are not present as D values in r₂.
• Since referential integrity ensures πC(r1) ⊆ πD(r2), the difference will always be empty.
• Conclusion: This expression will necessarily produce an empty relation.

(c) π_D(r₁ ⨝_C≠D r₂)

• This expression performs a join between r₁ and r₂ where C in r₁ is not equal to D in r₂, and projects the D values.
• Since C in r₁ must always match D in r₂ due to referential integrity, there will be no tuples where C ≠ D.
• Conclusion: This expression will necessarily produce an empty relation.

(d) π_C(r₁ ⨝_C≠D r₂)

• This expression performs a join between r₁ and r₂ where C in r₁ is not equal to D in r₂, and projects the C values.
• Since there are no tuples where C ≠ D (as explained in option (c)), this expression will also produce an empty relation.
• Conclusion: This expression will necessarily produce an empty relation.

3. Final Answer:

Both options (b), (c), and (d) will necessarily produce an empty relation. However, (b) directly represents the referential integrity property.

Correct Answer:

• (b) π_C(r₁) – π_D(r₂)

Q.18

The following query in Tuple Relational Calculus (TRC) selects:

{S.name | student(S) ∧ (∃e)(∃t) (enrollment(e) ∧ teaching(t) ∧ e.courseId = t.courseId ∧ e.rollNo = S.rollNo ∧ t.empId = S.advisor)}

• (a) Students who have taken all courses taught by their advisor.
• (b) Students who have taken courses, not taught by their advisor.
• (c) Students who have taken at least one course taught by their advisor.
• (d) None of the above.

Solution:

This TRC query is designed to retrieve student names (S.name) based on certain conditions. Let’s analyze the conditions step by step:

Step 1: Analyze the structure of the query

The main query is:

{S.name | student(S) ∧ (∃e)(∃t) (...)}

• student(S): This ensures S is a valid student tuple.
• (∃e)(∃t): There exist tuples e in the enrollment relation and t in the teaching relation.
• Conditions inside the existential quantifiers:
  • enrollment(e): Ensures e is a valid tuple in the enrollment relation.
  • teaching(t): Ensures t is a valid tuple in the teaching relation.
  • e.courseId = t.courseId: Links the enrolled course with the taught course.
  • e.rollNo = S.rollNo: Ensures the student S is enrolled in the course.
  • t.empId = S.advisor: Ensures the course is taught by the student’s advisor.

Step 2: Meaning of the conditions

The query retrieves student names where:

• The student S is enrolled in a course.
• The course is taught by their advisor (t.empId = S.advisor).
• Thus, the query selects students who have taken at least one course taught by their advisor.

Step 3: Analyze each option

• (a) Students who have taken all courses taught by their advisor:
  • This is incorrect because the query does not check whether students have taken all courses taught by their advisor. It only checks for at least one course.
• (b) Students who have taken courses, not taught by their advisor:
  • This is incorrect because the query specifically ensures that the course is taught by the student’s advisor (t.empId = S.advisor).
• (c) Students who have taken at least one course taught by their advisor:
  • This is correct because the query verifies that there exists a course taught by the advisor and that the student is enrolled in it.
• (d) None of the above:
  • This is incorrect because option (c) is valid.

Final Answer:

(c) Students who have taken at least one course taught by their advisor.

Q.20

Consider the sequence of operations given below on the relation Employee
(EmpNo, Name, Address, Bdate, Gender, Salary, SuperNo, DNo):
(EmpNo is key)

1. DEP5_EMPS ← σDNo=5(Employee)
2. RESULT1 ← πEmpNo(DEP5_EMPS)
3. RESULT2 (EmpNo) ← πSuperNo(DEP5_EMPS)
4. RESULT ← RESULT1 ∪ RESULT2

What will the above sequence of operations produce?

• (a) EmpNo of the DNo 5 who work either as an employee or a supervisor.
• (b) EmpNo of the employees who work in DNo 5 along with the employees of DNo 5 who work as supervisors.
• (c) EmpNo of the DNo 5 employees who work as supervisors.
• (d) EmpNo of the employees who either work in DNo 5 or supervise an employee who works in DNo 5.

Solution:

Step-by-Step Explanation

Step 1: DEP5_EMPS ← σ_DNo=5(Employee)

• This step filters all employees who work in department 5 (DNo=5).
• The resulting relation contains tuples of employees working in department 5.

Step 2: RESULT1 ← π_EmpNo(DEP5_EMPS)

• This step projects the EmpNo of employees who work in department 5.
• RESULT1: List of employee numbers (EmpNo) of employees in department 5.

Step 3: RESULT2 ← π_SuperNo(DEP5_EMPS)

• This step projects the SuperNo (supervisor numbers) from DEP5_EMPS.
• RESULT2: List of EmpNo for supervisors of employees in department 5.

Step 4: RESULT ← RESULT1 ∪ RESULT2

• This step combines RESULT1 and RESULT2 using the union operator (∪).
• RESULT: A list of EmpNo for:
  • Employees who work in department 5.
  • Supervisors of employees who work in department 5.

Analysis of Options:

• (a) EmpNo of the DNo 5 who work either as an employee or a supervisor.Incorrect: This option implies that supervisors are also in department 5, which is not necessarily true.
• (b) EmpNo of the employees who work in DNo 5 along with the employees of DNo 5 who work as supervisors.Incorrect: Supervisors in RESULT2 may not belong to department 5.
• (c) EmpNo of the DNo 5 employees who work as supervisors.Incorrect: This excludes supervisors from other departments who supervise employees in department 5.
• (d) EmpNo of the employees who either work in DNo 5 or supervise an employee who works in DNo 5.Correct: This matches the result of RESULT1 ∪ RESULT2.

Final Answer:

(d) EmpNo of the employees who either work in DNo 5 or supervise an employee who works in DNo 5.

Q.21

Consider the relations Branch(BranchNo, Street, City) and Property(PropertyNo, Address, City).
Which of the following relational algebra expressions would list all cities where there is both a branch office and at least one property?

• (a) πCity(Branch) ∪ πCity(Property)
• (b) πCity(Branch) - πCity(Property)
• (c) πCity(Branch) ÷ πCity(Property)
• (d) πCity(Branch) - (πCity(Branch) - πCity(Property))

Solution:

Step-by-Step Explanation:

Option (a): πCity(Branch) ∪ πCity(Property)

• This expression lists all cities that have either a branch office or at least one property.
• It does not filter cities that have both a branch office and a property.
• Conclusion: This option is incorrect.

Option (b): πCity(Branch) - πCity(Property)

• This expression lists cities that have a branch office but no property.
• It excludes cities that have both a branch office and a property.
• Conclusion: This option is incorrect.

Option (c): πCity(Branch) ÷ πCity(Property)

• This expression uses division to find cities where every branch corresponds to a property.
• However, the question does not require every branch in a city to have a corresponding property, only that both exist in the city.
• Conclusion: This option is incorrect.

Option (d): πCity(Branch) - (πCity(Branch) - πCity(Property))

• The inner expression πCity(Branch) - πCity(Property) lists cities that have a branch office but no property.
• Subtracting this from πCity(Branch) leaves only cities that have both a branch office and a property.
• Conclusion: This option is correct.

Final Answer:

(d) π_City(Branch) – (π_City(Branch) – π_City(Property))

Q.22

Assume table R has two attributes A and B. Similarly, table S has two attributes A and B.
Which of the following relational algebra expressions are not equivalent to R ∩ S?

• (a) R ⨝ S
• (b) (R ∪ S) - ((R - S) ∪ (S - R))
• (c) S - (S - R)
• (d) R - (S - R)

Solution:

Step-by-Step Explanation:

Option (a): R ⨝ S

• The natural join (⨝) between R and S retrieves tuples that have the same values for attributes A and B in both relations.
• This operation is equivalent to the intersection of R and S.
• Conclusion: This option is equivalent to R ∩ S.

Option (b): (R ∪ S) - ((R - S) ∪ (S - R))

• This expression computes the symmetric difference ((R - S) ∪ (S - R)), which retrieves tuples present in either R or S, but not both.
• Subtracting the symmetric difference from (R ∪ S) retrieves tuples common to both R and S, which is equivalent to R ∩ S.
• Conclusion: This option is equivalent to R ∩ S.

Option (c): S - (S - R)

• This expression computes the difference S - (S - R), which retrieves tuples in S that are also in R.
• This effectively returns the intersection of R and S.
• Conclusion: This option is equivalent to R ∩ S.

Option (d): R - (S - R)

• The expression S - R retrieves tuples in S that are not in R.
• Subtracting this from R retrieves tuples in R that are not excluded by (S - R).
• This results in tuples that are not guaranteed to be in R ∩ S and instead includes additional tuples.
• Conclusion: This option is not equivalent to R ∩ S.

Final Answer:

The relational algebra expression that is not equivalent to R ∩ S is:

(d) R – (S – R)

Q.23

Find the number of tuples returned by the following query? (p is used to rename):

πAD(R × S) - ρA←B(πBD(R ⨝B=C S))

• (a) 4
• (b) 5
• (c) 6
• (d) 7

Solution:

Step 1: Understand the Query

• The first part of the query, πAD(R × S), retrieves all combinations of attributes A and D from the Cartesian product of relations R and S.
• The second part, ρA←B(πBD(R ⨝B=C S)), performs the following steps:
  • Compute the natural join (⨝) of R and S on the condition B=C.
  • Project attributes B and D from the join result (πBD).
  • Rename attribute B to A (ρA←B).
• The final subtraction, πAD(R × S) - ρA←B(πBD(R ⨝B=C S)), removes tuples in the renamed projection from the Cartesian product projection.

Step 2: Analyze the Results

• Projection on πAD(R × S): The number of tuples in R × S is the product of the tuple counts in R and S. However, πAD reduces this to unique combinations of A and D.
• Join R ⨝B=C S: The join produces tuples where B in R matches C in S.
• Projection πBD: This extracts unique B and D values from the join.
• Rename ρA←B: Renames B to A in the result.
• Set difference: Subtract the tuples in ρA←B(πBD(R ⨝B=C S)) from πAD(R × S).

Step 3: Compute the Result

• The number of tuples in πAD(R × S) depends on the unique combinations of A and D.
• The number of tuples in ρA←B(πBD(R ⨝B=C S)) is determined by the join conditions and the projections.
• Subtracting these counts results in the final number of tuples.

Step 4: Answer

The correct answer is:

(c) 6

Q.25

Let R and S be two relations with the following schema:

• R(P, Q, R₁, R₂, R₃)
• S(P, Q, S₁, S₂)

Where {P, Q} is the key for both schemas. Which of the following queries are equivalent?

1. I. πP(R ⨝ S)
2. II. πP(R) ∩ πP(S)
3. III. πP(πP,Q(R) ∩ πP,Q(S))
4. IV. πP(πP,Q(R) - (πP,Q(R) - πP,Q(S)))

Options:

• (a) Only I and II
• (b) Only I and III
• (c) Only I, II and III
• (d) Only I, III and IV

Solution:

Analysis of Each Query:

I. πP(R ⨝ S)

• The natural join (⨝) combines tuples from R and S based on the equality of attributes P and Q.
• Then, the projection (πP) retrieves only the P-values from the joined result.
• Thus, this query gives the set of all P-values common to R and S.

Conclusion: Equivalent to πP(R) ∩ πP(S).

II. πP(R) ∩ πP(S)

• This directly computes the intersection of the P-values from R and S.
• Since {P, Q} is the key, matching P implies matching tuples in {P, Q}.

Conclusion: Equivalent to Query I.

III. πP(πP,Q(R) ∩ πP,Q(S))

• The inner operation πP,Q(R) ∩ πP,Q(S) computes the intersection of tuples in {P, Q} from R and S.
• The outer projection πP retrieves only the P-values from this intersection.

Conclusion: Equivalent to Query I.

IV. πP(πP,Q(R) - (πP,Q(R) - πP,Q(S)))

• The inner difference πP,Q(R) - πP,Q(S) computes tuples in {P, Q} from R that are not in S.
• The outer difference retrieves tuples common to both R and S in {P, Q}.
• The final projection πP retrieves only the P-values.

Conclusion: Equivalent to Query I.

Final Answer:

All four queries compute the same result: the set of P-values common to both R and S.

Correct Answer: (c) Only I, II and III.

Q.26

Consider the following relations:

R₁(A, B, C) and R₂(A, D, E)

R₁ has 1000 records and R₂ has 2000 records. The non-Null attribute ‘A’ in R₂ is referencing attribute ‘A’ in R₁. Let X be the minimum number of records in R₁ ⨝ R₂ and Y be the maximum number of records in R₁ ⨝ R₂. The sum of (X + Y) is __________.

Q.27

Consider a join (relation algebra) between relation r(R) with ‘n’ blocks with each block containing R_n records and s(S) with ‘m’ blocks with each block containing R_m records using block nested loop. If at any time only 2 blocks are present in main memory, which of the following is true?

• (a) If r(R) is the outer loop, then access cost is n + n × m.
• (b) If r(R) is the outer loop, then access cost is m + R_m × n.
• (c) If s(S) is the outer loop, then access cost is n + R_n × m.
• (d) None of the above.

Q.30

Consider a selection query: σ_A≤15(R), where R is a relation with 400 tuples. Assume that the attribute values (integers) for A among the tuples are uniformly distributed in the interval [1, 25]. How many tuples would the given selection query return?

Q.32

Consider two relations R(A₁, A₂, A₃, …, A_m) and S(A₁, A₂, A₃, …, A_n) where m > n. Find the number of attributes that appear in R ÷ S.

• (a) ⌊n / m⌋
• (b) ⌊m / n⌋
• (c) m – n
• (d) m + n

Q.34

Consider the relation P(A, B, C) Here C is the Key, Q(C, D, E) Here C is the Key and R(E, F) Here E is the Key , having tuples 200, 300, and 100 respectively. Estimate the number of tuples in relation P ⨝ Q ⨝ R.

Q.35

Consider two relations enrolled and course as shown below:

Enrolled	Course
Sid, Cid, Fees	Cid, Cname, Dept
S1, C1, 10	C1, ALGO, CS
S1, C2, 20	C2, DS, CS
S2, C3, 30	C3, TOC, CS
S3, C4, 40	C4, THERMO, ME

π_Sid,Cid(Enrolled) ÷ π_Cid(σ_{Dept=’EE’}(Course)).

in Enrolled relation (Sid,Cid) is the key and in course relation Cid is the key

If the above relational algebra query executes over the given database table, how many tuples are there in the result of the query?