SQL AND RELATIONAL ALGEBRA

Almost a year ago found an amazing tool where you can test the relational algebra and SQL at the same time. The coolest part that you don’t need to download any SDK etc. Just use your browser.

Even more, I am going to share some code with you guys.

So first of all why you need relation algebra calculator?? It’s a just perfect tool where you can make sure your logic behind is working perfectly.

Without any more of wasting time, click here

There are you can explore the GUI and find out that you can use test data set or create your own. Maybe modify too?

And then simply add your code: and test.

Let’s actually go through one interesting example of relation algebra. As you may know, some operations in relation algebra are much more difficult to perform than in SQL.

One of them is finding the maximum or minimum value in the dataset.

FINDING MAXIMUM IN THE RELATIONSHIP ALGEBRA

Select Kemper Datenbanksysteme (en) test data and execute a query with the following code

X=πg1.student_id (ρg1 (grade)⨯ρg2 (grade))
Y=πg1.student_id σg1.student_id<g2.student_id (ρg1 (grade)⨯ρg2 (grade))
X-Y

How do you know it’s correct? Well just run the Query to see the value of X and the value of Y

EXPLANATION

X=πg1.student_id (ρg1 (grade)⨯ρg2 (grade))

First of all, you can see that I have a cross product at X with itself. Which is simply to get all the student IDs of X.  And there’s also a rename operation.

Y=πg1.student_id σg1.student_id<g2.student_id (ρg1 (grade)⨯ρg2 (grade))

Here’s a bit more tricky. There is cross product exist. And the selection operator where we select students IDs which are less than second student ids. So we get the list of all the students IDs which are less than maximum.

X-Y

This is simple we take all the student IDS and subtract them with the IDs which we found in Y.

And get the maximum value. Yeay. Surprisingly as it maybe seems simple now to understand, I wasn’t able to find any code or explanation which actually works. So I hope it’s going to help you.

FINDING MINIMUM VALUE IN THE RELATION ALGEBRA

Surprisingly you need to change just a sign and that’s it

X=πg1.student_id (ρg1 (grade)⨯ρg2 (grade))
Y=πg1.student_id σg1.student_id<g2.student_id (ρg1 (grade)⨯ρg2 (grade)) 
X-Y

So in Y, you are finding all the values which are bigger than the minimum value.

That’s it I hope you enjoyed my simple explanation. Make sure to ask a question in the comments and press an FB like if it helped you.