Skip to main content

RA: count accounts in each branch

Short answer

Group Acc tuples by b_name and apply count(a_no) aggregate. Rename the result to show branch name and number of accounts.

Consider the relational schema: Acc (a_no, b_name, bal) Branch (b_name, b_city, assets) Customer (c_name, c_street, c_city) Loan (l_no, b_name, amt) Depositor (c_name, a_no) Borrower (c_name, l_no) Count number of accounts in each branch. (Write a relational algebra expression.)

Relational algebra symbols used: σ select, π project, ⋈ natural join, ∪ union, ∩ intersection, − difference, ρ rename, 𝒢 aggregate.

Counting needs the aggregate (generalized) operator 𝒢, written as: grouping attributes 𝒢 aggregate function(attribute) (relation).

Relational algebra expression

b_name 𝒢 count(a_no) (Acc)

Renaming the result column:

ρ branch_count(b_name, no_of_accounts) ( b_name 𝒢 count(a_no) (Acc) )

Explanation

  • Acc tuples are grouped by b_name, one group per branch.
  • count(a_no) counts the account numbers in each group.
  • The output has one tuple per branch: (b_name, number of accounts).

Example: if Acc has accounts 101, 102 in "Putalisadak" and 103 in "Lakeside", the result is (Putalisadak, 2), (Lakeside, 1).

A branch with no account does not appear in the result. To list it with 0, a left outer join with Branch would be needed.

Equivalent SQL

SELECT b_name, COUNT(a_no) FROM Acc GROUP BY b_name;

✓ Answer checked by Claude Opus 5.5 (high effort)

Related questions

All Database Management System chapter 3 questions →