Relational Algebra Question

Dan Czarnecki
Greenhorn
Posts: 29
Hello there.

I'm not entirely sure if I'm posting this in the right area, but I thought I'd post it here anyway.

Anyway, I'm working on some relational algebra assignment for my database class as a precursor to SQL, and there's some problems I'm getting stumped on.

Before I get to the problems I'm stuck on, here's the schema for the problems I'm working on:
Category(class, function, country, num_guns, max_altitude, max_fuel)
Planes(pname, class, year)
DogFights(dname, date)
Outcomes(pname, dname, result)

With that out of the way now, here are the problems I'm getting stumped on:
List the name and year of maiden flight for the plane/s that fought in most battles that Hard Luck also fought in.
List all planes that won more battles than they lost.
What was the average number of guns for all planes that won more than 8 battles.

I know for each of these that I'll have to start off by doing a join, but from there, I am then stumped.

If anyone out there has the slightest idea as to how I would go about doing this, please feel free to respond when you can.

Brian Tkatch
Bartender
Posts: 567
25
If this is a precursor to SQL, you are really hard core! Anyway, these are SQL questions and best asked in SQL forums.

It's not always easy to guess or test without sample data, but, this might get you started:

 removed complete solution because we are NotACodeMill and LetThemDoTheirOwnHomework, which doesn't mean you can help them by giving pointers and hints (e.g. what about using a group by) so they are able to code the solution themselves instead of using some code which they don't have any clue about.

Dan Czarnecki
Greenhorn
Posts: 29
Brian Tkatch wrote:If this is a precursor to SQL, you are really hard core! Anyway, these are SQL questions and best asked in SQL forums.

It's not always easy to guess or test without sample data

Thanks for the help!

Dan Czarnecki
Greenhorn
Posts: 29
Brian Tkatch wrote:If this is a precursor to SQL, you are really hard core! Anyway, these are SQL questions and best asked in SQL forums.

It's not always easy to guess or test without sample data

Just wondering, what do the little asterisks mean in some of the problems?

I'm working turning one of the problems into relational algebra right now, and I just noticed them.

Here's what I've got so far for one of them (problem K):
PCO <- Planes ⨝ Category ⨝ Outcomes
MoreBattles <- planes.pname ^ avg(category.num_guns) ^ planes.pname = outcomes.pname ^ category.class = planes.class ^ outcomes.result = won(PCO)
planes.pname count(outcomes.result = won > 8)(PCO)

Am I on the right track so far?

(note: there's supposed to be a little aggregate function symbol by avg and count, but those didn't paste into here)

Roel De Nijs
Sheriff
Posts: 10662
144
Dan Czarnecki wrote:Just wondering, what do the little asterisks mean in some of the problems?
Try executingandYou'll see the difference immediately.
Dan Czarnecki wrote:I'm working turning one of the problems into relational algebra right now, and I just noticed them.
That's what you get if you are using ready-to-use solution instead of a self-crafted one.
Dan Czarnecki wrote:Am I on the right track so far?
I have no clue. It was the first time I have heard about "relational algebra"

Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
This is the right forum (for JDBC, SQL and other database questions). I haven't done anything with relational algebra since college. So at least I've seen it before, but it has been a while.

Brian Tkatch
Bartender
Posts: 567
25
 removed complete solution because we are NotACodeMill and LetThemDoTheirOwnHomework, which doesn't mean you can help them by giving pointers and hints (e.g. what about using a group by) so they are able to code the solution themselves instead of using some code which they don't have any clue about.

Oops, sorry 'bout that chief.

Roel De Nijs
Sheriff
Posts: 10662
144
Brian Tkatch wrote:
 removed complete solution because we are NotACodeMill and LetThemDoTheirOwnHomework, which doesn't mean you can help them by giving pointers and hints (e.g. what about using a group by) so they are able to code the solution themselves instead of using some code which they don't have any clue about.

Oops, sorry 'bout that chief.

No problem! Next time you could for example share one solution (the first one or the most easy one) and let then try the OP to complete the others. Or you could do something like this one.

Brian Tkatch
Bartender
Posts: 567
25
Roel De Nijs wrote:
Brian Tkatch wrote:
 removed complete solution because we are NotACodeMill and LetThemDoTheirOwnHomework, which doesn't mean you can help them by giving pointers and hints (e.g. what about using a group by) so they are able to code the solution themselves instead of using some code which they don't have any clue about.

Oops, sorry 'bout that chief.

No problem! Next time you could for example share one solution (the first one or the most easy one) and let then try the OP to complete the others. Or you could do something like this one.

None of those were basic queries, which made it curious. The first one was actually the most confusing (because of the sub-query.) They seemed to complicated to be homework questions (which tend to be much, much more basic.) I was so confused i just wrote the queries, not knowing of ranch policy. Besides, i want to be helpful, and not just the one who says "bad, bad, don't do that!" whenever someone uses dynamic sql or is asking about bad design.

Roel De Nijs
Sheriff
Posts: 10662
144