CODEWITHSUNDEEP
sqlrelational-algebra
KevinCameron1337
KevinCameron1337

Reputation: 517

SQL to Relational-Algebra

Tables:

Show(show_id, title, start_date, duration,singer,hall_id)
Concert(concert_is,date,hour,show_id)
Hall(Hall_id, name, address, capacity)

The above is a DB of 3 tables. I need to convert some simple SQL statments into RA:

SELECT date
FROM Concert C, Show S, Hall H
WHERE C.show_id = S.Show_id
AND S.hall_id = H.hall_id
AND S.singer = 'A'
AND H.name = 'B'

In RA:

pi date (s(show_id = show_id)Show |><| Concert)

the above is not complete, i just need to know if im on the right track. There is limited information on RA.

Upvotes: 3

Views: 419

Answers (2)

Roberto Navarro
Roberto Navarro

Reputation: 958

Here goes one shot at it using "NATURAL JOIN". Also note, I did NOT use your tuple variables: 

pi date (sigma name = 'B' AND singer = 'A' (CONCERT x SHOW x HALL))

The expression above is one of the least efficient wasy of evaluating your SQL query; however, syntacticly it more closely follows your SQL.

Here is the query template I used:

pi TargetList sigmaSelection_Condition (REL1 x ... X RELn)

I'll update soon with one more variant that uses your |><| operator, if you'd like...

Upvotes: 1

Jodaka
Jodaka

Reputation: 1247

The first step would be to put the query into words: what is being asked? In this case we want the dates of all concerts where the show at the concert was sung by 'A' and the hall the concert was in was named 'B'. (This step isn't always necessary - in many cases, we could probably generate the RA straight from the SQL - but it's never a bad idea to make sure you understand what it is you're doing).

So let's build your RA statement from the inside out. First, we want to make sure that anytime we look at a show and a concert at the same, we are only looking for the show at that concert, and in this case, the show's id is a field in concert. So your intuition to use natural join/|><| is correct. I've always learned that unless otherwise specified, natural joins will automatically match on all equivalent column names (though I would check with your teacher's standard first), so in this case, we don't need to specify the join condition that the show id's match. Even if we did, that would be a subscript of the join, and not part of the select statement. Next we want to make sure that when we look at a hall and a show, we only look at shows in that hall. So we want to natural join hall to the previous result (note that order of joining matters - you can't naturally join concert and hall if one hasn't already been joined to show).

The next step is to only get the rows of information we want. In this case, the select/sigma statements are pretty easy - just specify that the name and hall must be what you want. Finally, we project/pi only the data element we want from the rows we have retrieved - in this case we will only project the date. After that, we're done. And from your RA so far, I can see that you already have all the operators in the right order, you're just missing the operands!

Note that there are a few other things to consider. You might want to consider using rename/rho on each table to simplify writing things out. Furthermore, if your RA needs to exactly duplicate the SQL, you'll have to use rename, and also replace your natural joins with cross join/X and place the join conditions in your select statement.

Upvotes: 1

Related Questions