What kind of return on investment is there in learning Set Theory, with respect to database design and query?

Question

I am learning database right now -- and some of the queries are getting just plain crazy. I know that during query optimization you may sometimes rewrite queries in different ways to minimize certain things. I am looking at a correllated subquery example and see that it results in the same resultset as a LEFT JOIN.

I am thinking that possibly mathematically (set theory) they may be related, and if someone comfortable with set theory would be able to see that easily.

My question is: What kind of return on investment would I see from learning set theory? Would I see any return on investment? Would it be faster to learn database just by writing more queries, or should I spend time on set theory? I don't want to fall down a rabbit hole. I want to be good with database, but I need to get there as fast as possible.

Answer 1

If by "set theory" you mean understanding intersection, union, difference, and so on, then definitely, it will help to understand those fundamentals. It shouldn't take you long, and will be well worth it.

Don't get distracted by axioms or infinities, etc.

Relational databases are built on set theoretic foundations (for example, the reason there's no guarantee on the order of rows is because it's a set of records returned, unless you use an ORDER BY).

Answer 2

IMHO, learnging set theory would certainly improve your conceptual model of whats going on with tables objects, but to really get good I think you need to knuckle down and start writing production level SQL, deal with them on a day to day basis.

Read books that are specific to SQL, look into actual examples, and most important is to try it yourself.

As an example, I can know all the deepest aspects of how a computer works, down to the TGEHP structure, but that will do little to solve my current SQL 'puzzle' :)

Darknight

Answer 3

That's a tricky question. I've taken courses in set theory and relational algebra at school, and I can definitely say that they have aided my understanding of relational databases. But just learning set theory by itself probably won't help a lot. Relational algebra, which requires a little basic set theory, is a better formalism for modeling relational databases (by design), and it's a great thing to learn if you're planning on using them a lot. It will help with not only query optimization but also database design, which can seem like a black art without examining the theoretical underpinnings. Database normal forms, for example, can be succinctly describes as simple expressions in relational algebra.

If you're only doing simple SQL querying and no complex queries or database design, then the effort to learn relational algebra may not be justified by the benefit.

It's worth mentioning that real-world relational databases are not actually sets, but rather bags or multisets. Applying set theory to bags tends to cause headaches from the impedance mismatch.

Answer 4

Real set theory is more about axiom theory, classes vs. sets, etc. While I find that an extremely interesting topic, I don't think there is much to be learned from it that is applicable to databases.

What might be more appropriate (if you want to extend your theoretical background) is the theory of relations (Wikipedia as a starter) and -- of course -- relational database theory per se.

Answer 5

While most relational db work depends on some fundamental set theory concepts, I don't think you're going to see much immediate "ROI" from going back and studying it now - especially if you're looking to get some quick turn around on your time investment.

I would suggest just putting in some more time experimenting with queries and their results and then learning the basics of indices and the what the different types of joins are. It might also be useful to get a book on the particular DBMS you're using.

Answer 6

I think the answer depends on your age. If you're younger than, say 22 and your main focus is studying rather than working, then it by all means study set theory, abstract algebra, etc. This mental gymnastics could help prepare you for your future programming challenges.

(If you've studied different algebraic structures as part of your math curriculum, then you know that, e.g. square matrices form an algebra with 3 operations (addition, multiplication and multiplication by scalar). Then you meet relational databases and see an algebra with 5 operations on tables - selection, projection and three types of joins, no big deal. Modern computer cryptography is based on the theory of rings, in particular, rings of whole numbers an polynomials. Etc.)

But if you're older and have a full-time programming job, my guess is that the set theory's ROI will be low and you'll be better off spending your time to practice writing and optimizing queries.

Answer 7

"Would it be faster to learn database just by writing more queries, or should I spend time on set theory? I don't want to fall down a rabbit hole. I want to be good with database, but I need to get there as fast as possible."

If I can take that last sentence literally, then I say start studying theory. Set theory, relational algebra, type theory, calculus, the link with predicate logic, theory of normalization, ...

There's enough to keep you studying for at least 10 years, and to me, that seems indeed a reasonable estimate of "the fastest possible" to get REALLY good with databases.

If you think that it is possible to become a genuine database expert in a matter of one year's time, then you are just, well, plain deluding yourself.

Answer 8

I agree with the previous answers but I like to add some thoughts about this. I had the same question in mind while ago. First, studying math is always good and will keep our problem solving skills sharp. Secondly, the amount of ROI depends on the complexity of your daily problems, take a look on the NoCoug SQL Challenge and Joe Celko's book SQL Puzzles and Answers. For a simple database design probably set theory will not help a lot. Finally, I find more helpful studying mathematical proofing techniques (where set theory is a big part of it) because they can help you to prove some statements transformations. For a reference in mathematical proofing you can see the book How to Prove It.

Answer 9

No. Having studying set theory and a range of other 'pure' mathematical topics, I can honestly say that the relation back to the real world is very minimal.

As pointed out above, very elementary set theory (such as union & intersection) would be helpful but I would imagine you already understand this.

If you want to study mathematics with the intent of improving your code efficiency, I would suggest formal logic. It involves equivalence and simplification of expressions, and ensures that you are able to express logically sound arguments.

But if you want to become 'good' at databases you should study databases. That is the most direct route to proficiency.

Answer 10

The book, Database in Depth - Relational Theory for Practitioners, by C.J. Date might be a good read for you. It is mostly theory-based, but looks at it in the context of databases.

It can be a bit dry at times, and Mr. Date has a tendency to rail against the companies that build RDBMS platforms, but if you're looking to tie set theory to your learning process, this should help.