Hey guys, can anyone give me a hand with relational algebra? I'm shaky with MySQL as is.
I have four tables;
Suppliers(S#, SNAME, STATUS, CITY)
Parts(P#, PNAME, COLOR, WEIGHT, CITY)
Jobs(J#, JNAME, CITY)
and SPJ(S#, P#, J#, QTY) which is a table which represents shipments of parts from suppliers to jobs. S, P, and J are all foreign keys to their respective tables.
I need to find a relational algebraic statement that returns the names of projects that have at least a red part sent to them.
Currently, I've selected the red parts and have them in a subset of red parts.
Red_Parts ← σcolor=”Red” (P)
Red_Sent ←
I don't know how to select tuples from SPJ that contain tuples in red_parts. I know from there I have to project just the name. Would it be as easy as performing an intersection on SPJ and Red_Parts or do I have to do a join?
>>59521718select * from spj where P# in (select P# from Parts where color like 'red')
Why are you joining three tables to one intermediary table...?
Use an embedded SQL query.
Also, unless I don't understand quite properly what your structure is, I'll give you some advice:
SELECT JNAME FROM Jobs WHERE...
Join on SPJ table with primary key
Join to Parts table
Select from Parts
Use Where clause on color
Using Join is your best option.
>>59521770
>>59521745
I've managed to do it in MySQL but translating to relational algebra is where I'm falling, I can struggle through the practicality.
For clarification SPJ is a table that lists the supplier, the part and the job it's going to, along with the quantity. It exists as it's own relation.
>>59521770
Of course he has to join through three tables, that's where the pieces are. The name has to come from the jobs. The color has to come from the parts and the joining table is what joins them. How the fuck else would that happen? What the fuck kind of shit alternative would you suggest?
>embedded SQL query
>I don't understand quite properly what your structure is
>Using Join is your best option.
You don't know what you're doing.
SELECT spj.*
FROM spj INNER JOIN parts
WHERE parts.color like 'red';
Result_Temp← Pcolor=”red” ⋈ SPJ
RESULT ← πJNAME(Result_Temp)
Like this then?
>>59522562
⋈ = join
π = select attribute
Thanks for the help guys!
OP, where can I read up on the "notation" that you're using? Eg. S#, P# (auto incrementing IDs?), Red_Parts ← σcolor=”Red” (P)
Red_Sent ←
Nice to see people actually doing shit other than discuss woodscrews and housefires.
>>59523333
Not him, but i teach databases. Here's a pretty good book: http://people.inf.elte.hu/miiqaai/elektroModulatorDva.pdf
>>59523381
I already know basic MySQL, I just think it'd be a very useful tool to be able to ask questions like this.
That's a really big PDF. We'll see how much I actually end up reading it. Also, funny that you linked a hungarian domain. My first java teacher was hungarian.
>>59523413
Chapter 2.4 has most of the relational algebra, a lot of the book is about normalization and actually creating a RDBMS, which isn't terribly relevant here.
Also, the hashtags are 99% just primary/foreign keys, since you cant underline here. It's probably auto-incrementing, but that doesn't actually matter for the query.