CODEWITHSUNDEEP
relational-algebra
swennemen
swennemen

Reputation: 955

How to select on a count in relational algebra

I have the following relational database: 

**Company** (company-name, address, grade, last-year-profit) 

**Agent-of** (agency-name, company-name) 

**Agency** (agency-name, city) 

**Manager** (company-name, manager-name)

Now I want to find the address of the company that has more than 8 agents (ie. agencies acting as it's agent).

How would I do this? I don't understand how to count the agencies and then select based on this count. Or is my thinking wrong?

Upvotes: 0

Views: 2955

Answers (1)

Erwin Smout
Erwin Smout

Reputation: 18408

The most common versions of relational algebra do not support aggregations (e.g. counting tuples) (but note that mileage may vary).

Without aggregations, your problem can still be answered by resorting to an 8-way self-join :

  • join 8 "copies" of agent-of over company-name to obtain (company-name agency-name1 agency-name2 ... agency-name8)
  • restrict that to the set where agency-name2 <> agency-name1 and agency-name3 <> agency-name1 and agency-name3 <> agency-name2 and ...
  • project that over company-name.

Upvotes: 2

Related Questions