Relational division - SQL Tutorial

- [Tutor] Given two sets named R and S. R divided by S is a binary operation that returns their restrictions of tuples in R to the attribute names unique to R for which it holds true that all their combinations with tuples in S are present in R. Crystal clear, isn't it? Don't answer. It's a rhetorical question. Back to our analogy the fictional timeless chess tournament. We got our matches by multiplying team Russia with team USA and you can think of it as analogous to the 35 being the result of multiplying five and seven. To get the arithmetic division we moved 35 to the left side of the equation and seven to the right. Similarly, we can move relations from one side of the equation to the other as well. Matches is the dividend, team Russia the divisor and the quotient, the result of their relational division in this case will be team USA just like the arithmetic division. Let's correlate this back through the formal definition. Given two sets named R and S, the dividend matches and the divisor team Russia. R divided by S is a binary operation and binary just means that it takes in two sets and results in one. Closure, remember? That returns the restrictions of tuples in R means that the result will consist of a subset of the rows from R to the attribute names unique to R means the attributes from R that do not appear in S. Shakhmatist exists in S which leaves player as the only attribute that is unique to R and for which it holds true that all their combinations with the tuples in S are present in R. This means only rows from R that have all their possible combinations with every row in S. These are the USA players that competed against all Russian Shakhmatist. In this simple example matches was the result of multiplying team Russia and team USA to begin with. So naturally all US players will qualify for the quotient but that's not always the case. What will happen if we add another match? Garry Kasparov versus Wesley So. Do you think this match should change the result? Here is the formal definition for your reference. Pause the video for a minute and think about it. Should So qualify for the quotient? The answer is absolutely no. Wesley So does not qualify because he did not play against all the Russian Shakhmatist. It fails the last part of the definition for which it holds true that all their combinations with the tuples in S are present in R. So played Kasparov not all Russians so the results should not change. Pun intended. What will happen if we add a different match? Anatoly Karpov versus Bobby Fischer. Should this match change the result? Pause the video and think about it. Should Fischer be eliminated from the quotient? This one is a little trickier but the answer is still no. The key here is also for which it holds true that all their combinations with the tuples in S are present in R. A player qualifies for the quotient only if they have competed against all Russian Shakhmatists. This still holds true for Fischer. He did play all Shakhmatist. There is no reason to exclude him. His match with Anatoly Karpov is a remainder to the division just like dividing seven by two results in a three and a remainder of one half. It does not mean that three is not still the correct result and it doesn't matter if Fischer were to play every Shakhmatist on the planet. The division only cares whether he played Kasparov and Tal the divisor team Russia. To make things more interesting we can ask a slightly different question and add the condition and only those combinations. This is known as an exact division and it eliminates any player who has a remainder an extra match and an exact division will exclude Fischer from the quotient. The Renegade dare challenge Karpov to an unofficial match. And Bobby Fischer was quite a renegade indeed. He was a chess prodigy at the age of 13, the youngest US champion at 14 and the youngest grand master at 15. After becoming world champion at age 29 Fischer avoided any public games and emerged 20 years later to beat Boris Spassky for a revenge match. His career was full of controversies and oddities and if you're not familiar with Fischer's history, look it up. It's fascinating. Keep in mind that an exact division is just an ordinary relational division with an additional constraint sprinkled on top.