| Author |
Topic |
|
Pluto3
Starting Member
3 Posts |
 Posted - 2012-06-06 : 10:25:06
|
| Hi!I'm trying to translate this tuple relational calculus to SQL. At top of the picture the database structure is given and then there is the tuple relational calculus.My translation to SQL is:Is this correct? And if not, what have I missed?Thanks for answearing. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-06 : 10:32:43
|
| Don't think this is a natural join and you have missed the join conditions. Also you have missed out the table Vara with the associated join.Try translating it into english firstt.adv such that t in Lager and exists (u,v,w) such that u in Avdelning and v in Lager and w in Vara and t.foretag = v.foretag and ...==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-06 : 10:46:19
|
quote:
Originally posted by nigelrivett
Don't think this is a natural join and you have missed the join conditions. Also you have missed out the table Vara with the associated join.
Natural joins always occur on common column names. |
|
|
|
Pluto3
Starting Member
3 Posts |
Posted - 2012-06-06 : 10:47:58
|
| nigelrivett, im think like this: Avdelning, Lager and Vara are associated, and they are associated by the following conditions: u.avd = v.avd and v.varunr = w.varunr. Hence we should be able to use NATRUAL JOIN in SQL as it does those associations for us? Then we have the conditions w.typ = 'blomma' and u.våning = 1 (translated in english (this is a database for a department store) w.type = 'flower' and u.floor = 1) and we then group by the avd (department that meet the conditions of selling flowers and beeing on floor 1.)robvolk, do you know if my translation is correct? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-06 : 10:59:03
|
| OK - didn't notice that - also missed the third table in your scriptLooks ok - I would have distinct rather than group by - and do you need to exclude nulls. Depends on the adv column definition.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Pluto3
Starting Member
3 Posts |
Posted - 2012-06-06 : 11:05:42
|
| nigelrivett, that seems like a better solution. So the SQL would then be: And that would give me all the departments (not displaying the same several times) on floor 1 selling flowers right?Just want to make sure that i got it. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-06 : 11:19:09
|
| Looks like it. You are joining on avd so it can't be null.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-07 : 08:11:11
|
| Not very friendly to remove the question - other peple might be interested.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-07 : 09:25:34
|
quote:
Originally posted by nigelrivett
Not very friendly to remove the question - other peple might be interested.
I think their professors are amongst the interested parties.  |
|
|
|
|
Tuple relational calculus