How do you convert a Sql command into Relational Algebra?

Question

I have this question.

for two table : first, is employee : Employee(id, roomID) and second is : Room(roomID, roomName)

The question is : How many employees work in each room.

I can do this easily in SQL Language :

select Room.roomID, COUNT(Employee.id) as NumofEmployee
from Employee, Room
where employee.roomID = Room.roomID
group by Room.roomID

The same question, but writing under Relational Algebra language. This question makes me headache so much, because I know in this language, just have some simple operation : join selection projection difference. So, many SQL command I don't know how to do with, for example : group by or count.

Thanks :)

Don't write "`FROM Employee,Room`" like that. It's bad. Use the JOIN keyword: `FROM Employee INNER JOIN Room ON employee.roomID = Room.roomID`. It's not as important at this stage, but as you start building tougher queries it makes a _huge_ difference. — Joel Coehoorn, May 31 '13 at 16:44
@JoelCoehoorn not that your comment did anything to help the poster at all, what reasoning can you cite for this statement? I'm always under the impression if the programmers didn't want you to do it a certain way they wouldn't allow the construct? — AbsoluteƵERØ, May 31 '13 at 16:53
The reason the syntax exists at all is historical. It's from the ansi-89 standard. ansi-92 and later prefer using the join keyword, and some recent standards have even deprecated the old A,B syntax in certain situations. — Joel Coehoorn, May 31 '13 at 16:55
@JoelCoehoorn I very rarely see anyone using the 92 standard except on here (or in non-US countries). Thanks for the thorough response. I guess this also answers my question. http://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89 — AbsoluteƵERØ, May 31 '13 at 17:00
As an aside you might want to read the meta faq [Should questions include “tags” in their titles?](http://meta.stackexchange.com/q/19190/148672). Note that leading with a tag in particular is discouraged. I've edited your title to reflect this. I've also added the relational-algebra tag to your question. — Conrad Frix, May 31 '13 at 19:42
"and some recent standards have even deprecated the syntax" Which "standards" (note your own PLURAL) were you thinking of ? — Erwin Smout, Jun 08 '13 at 23:51

score 4 · Accepted Answer · answered May 31 '13 at 17:38

Aggregation and grouping operations can't be constructed from the basic relational algebra operations. You would have to define your own COUNT and GROUP BY operators to perform this.

There are many proposed extensions to the basic relational algebra that you could use, or you could even define your own - but to do this formally, I suspect the mathematics would get reasonably complex.

A simple proposal (without much formality) can be found here, http://myweb.lmu.edu/dondi/share/db/relational3.pdf. (Section 3.2)

Using the extended algebra proposed in that link, your expression would be written like:

How do you convert a Sql command into Relational Algebra?

1 Answers1