7

Learning how to formulate query-like expressions in relational algebra is a traditional part of many, perhaps most, "Introduction to Databases" courses.

This is usually justified by the assertion that relational algebra is the mathematical foundation of relational databases in general and SQL in particular with the implication that it is important to know it.

However it seems to me that formulating expressions in relational algebra is basically the same as formulating queries in SQL and that much the same thought processes underly both tasks. In particular, I can't really see that knowing relational algebra makes it easier to write SQL queries or vice-versa. This makes me wonder if the teaching of relational algebra is just some sort of historical hangover, or if there actually specific benefits to knowing it.

So my question is "are there specific practical benefits to knowing relational algebra, of sufficient importance to make it worthwhile teaching?"

As Database Administrators, do you feel that relational algebra is or was important to your career trajectory?

A sort of sub-question is whether the time spent learning relational algebra could more effectively be used by learning more SQL?

9

When Codd defined the relational model he defined a set of operators which could be applied to relations. In specifying a relational algebra, much like specification of an integer algebra, we are able to use symbols in place of relations to solve queries. These operators are subject to the same algebraic properties that integer algebra operators (+, -, *, /) are. As a result, we can assume certain laws that always apply to a relation, any relation, undergoing that operation. For example, in integer algebra we know that addition and multiplication are associative in that we can change the grouping of operands and not change the result:

a + ( b + c ) = ( a + b ) + c

Similarly, in relational algebra we know that natural join is associative and thus know that A join B join C can be executed in any order. These properties and laws create the power to re-write query formulations and be guaranteed to get the same results. The book Applied Mathematics for Database Professionals provides significant detail on the various re-write rules you can use to precisely formulate the same query in different ways. In a perfect world any formulation producing the same result would have the same performance. A modern optimizer, while an amazing piece of software, isn't perfect however. Thus if you have formulated a query one way and are getting poor performance, you have the skills to formulate it a different way and know it has the same semantics. Another practical advantage to this is in the specification of database constraints. First, understanding the relational algebra enables you to determine the simplest way to formulate the constraint. Second, by formulating the constraint in formal logic, you can immediately clarify any ambiguity in intent from the business subject matter experts who formulated the business rule in loose English and avoid bugs.

It was Leonardo da Vinci who said:

He who loves practice without theory is like the sailor who boards ship without a rudder and compass and never knows where he may cast.

In this same way, a data practitioner who doesn't understand the fundamentals of relational theory cannot be in as complete command of the technology as they can be with that understanding. Some great references on relational algebra are SIRA_PRISE's Introduction to the Relational Algebra page, and CJ Date's SQL and Relational Theory. Date's book shows the practicality in understanding relational algebra so that you can write much more accurate SQL queries. SQL has many quirks and pitfalls and having a sound grasp of how it works vs. the original relational algebra operators really helps realizing where the pitfalls are and avoiding them.

  • Thanks for your thoughtful reply - would it then be fair to say that mastery of relational algebra is more important for deep understanding or implementation of query optimisation and complex query formulation than for casual / basic use of SQL? – Gordon Royle Aug 19 '15 at 12:26
  • 1
    Yes I would agree with that. But I think any introduction to database course needs to teach relational algebra so that students understand the foundations that led to SQL. Teaching just SQL would be like an introduction to industrial design class teaching just a CAD/CAM tool and not first exposing the students to the fundamental principles of industrial design. – Todd Everett Aug 19 '15 at 12:58
  • @LearningFast & ToddEverett RA in its simplest form is way more simple & straightforward for reasoning (including designing, querying & constraining) (also internally like optimizing) than SQL, which is a very poorly designed language. With the right choice of 'relation' and operators on them it is obvious that RA expressions are 1:1 with predicate logic expressions, ie are interchangeable with them. Predicate logic is the language of precision of science (including computer science), engineering (including software engineering) & math. (See my RA dba.stackexchange & stackoverflow answers.) – philipxy May 7 '17 at 11:19
  • Bit late to the party - many years ago I completed a Relational Database OU course which covered relational algebra. This understanding along with knowing what the SQL would be fetching from the tables at each stage allowed me to rewrite most of my amateur SQL and in some cases the speed increased from 20minutes to a couple of seconds. But then my employer took me off of database work for a couple of years and I forgot the lot.... :( I'm now consigned to Access hell with a knowledge of what could be.... – Darren Bartrup-Cook Jan 4 '18 at 17:00
4

I strongly feel you can be successful as a DBA without taking a formal course in relational algebra, just like you can be a successful programmer without taking a formal course in discrete math. The need to take a course in relational algebra would very much depend on your career path/goals. See this question on Programming StackExchange. The OP wanted to get a Masters in Algorithms etc. I would say it is fairly obvious that he needs to take and master discrete math. Similarly if your goal is to write a database engine or be part of the core team working on a major relational database engine then I would strongly recommend mastering relational algebra, stats, and the like.

That being said insights gained studying relational algebra will most likely help you understand why queries are behaving like they are, which will be valuable for troubleshooting if nothing else. Researching and studying the formal studies surrounding relational logic will also help you in other ways.

For example I don't have much formal knowledge in relational theory, but I can hack a decent query together and have a decent general sense on how to design good solutions. That being said I have trouble truly groking some excellent answers by users such as MDCCL because I can't really follow the notation and/or concepts fully. I don't know if MDCCL has formal training or not, but I feel that a stronger theoretically foundation would give me the ability to play at MDCCL's level.

That is just one example which shows the real value of relational algebra, etc. isn't necessarily in applying it directly to SQL, but in allowing you to digest the knowledge out there at a higher level.

MDCCL I hope it is ok if I called you out in this answer. If you don't want to be mentioned in this post let me know and I'll edit you out.

  • Thanks for your reply - I too believe that it is possible to be a decent DBA without knowing the theory of relational algebra, so I was interested in exploring the motivation for teaching it. Between the two answers, I'm much clearer than before (but I can't accept two answers at once). – Gordon Royle Aug 20 '15 at 10:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.