Sometimes is good to dive into the foundations of something, to find new elements to add more power to our current skills. This article is the second part of the previous week Relational Algebra article. In Relational Algebra, there are several data operations, and almost all of them have a simple SQL statement representing the same process. However, there is one Algebra Relational operator that is not simple to express in SQL: The division operator. In this article, we will cover the division operator, explaining what is for, and how we can implement in SQL.
A simple data model to work with
As the division is a hardest to understand (and to explain also) relational algebra operator, we need a simple and clear data model to work with. Let’s suppose we have the relations student, course, professor. You can see the attributes of the relations, and the data in the following print screen:
The first point to explain is what are the data questions to be resolved with the division operator. Example of this data questions are:
- What are the students who took courses with ALL professors?
- What are the professors who had in his/her courses ALL the students?
- What are the students who took courses with ALL the professors in the Chemistry area?
As we can see, the word “ALL” appears in all these data questions. The division operator returns the elements which seem related to ALL the components of other categories. In our examples, the categories are students and professors.
Let’s see in Relational Algebra the solution to the previous question: “What are the students who took courses with ALL professors”:
A first try to the solution can be:
COURSES % PROFESSOR
refining the solution, we can leave only the ids
PROYECTIONstudent_id,professor_id(COURSES) % PROYECTIONprofessor_id (PROFESSOR)
The result of the previous relational algebra expression is:
Is clear that student_id 100 is the only student who took courses with all the professors. Let’s see how we can implement the division in SQL. Let’s look in the following image in TeamSQL.
Let’s continue with more advanced division concepts. First, a theoretical, but interesting point. You know why this operation is called division ?. Because is the complementing operation with the cartesian product (we talked about cartesian product in our last article). let’s see a property of the division operator:
( STUDENT X PROFESSOR ) % STUDENT = PROFESSOR
Make sense. It’s equivalent to the division with numbers.
Let’s add now a little level of complexity to our previous exercise: Obtain the students who took courses with ALL the professors from computer sciences. We take our previous solution and add the SELECTION to filter computer sciences, professors:
PROYECTIONstudent_id,professor_id(COURSES)% SELECTIONarea=’computer_sciences’(PROYECTIONprofessor_id (PROFESSOR))
and the result of the previous division is
Now, let’s go to TeamSQL to implement the previous expression. In the SQL implementation we need to add the condition area = ‘Computer Sciences’ to the subquery as we can see in the following image:
Se can see the result of the SQL query in the previous TeamSQL print-screen, it is the same result we obtained in the last example in relational algebra
We reviewed the division operator of the Relational Algebra, and we showed how to implement it in SQL using TeamSQL. Relational Algebra perhaps can be a little bit boring, however, is like the foundations of the SQL language Sometimes solving a query first in Relational Algebra, can open the door to find the solution in SQL. Let’s continue learning SQL, and applying with TeamSQL !!