1. Registered User
Join Date
Apr 2007
Posts
7

Relational algebra question

Good morning...I am learning about Relational algebra and am taking an online course on the subject. I was wondering if I could get someone to check my work on a few questions. I have done the studying and am a bit unsure of these answers...

Here is the information:

Species (Species Code, Latin Name, English Name)
Location (Location Code, Place in Building, Size, Head Keeper)
Population (Species Code, Location Code, Number of Animals)
Food (Type of Food)
Feeding (Species Code, Type of Food, Amount of Food, Feeding Interval)
Habitats (Habitat Type)
Can Live (Species Code, Habitat Type)
Where Found (Location Code, Habitat Type)
Source (Source Code, Source Name, Source Address, Source Phone, Contact Person)
Can Supply (Source Code, Species Code)
Shipment (P.O. #, Source Code, Order Date, Order Total \$)
Shipment Items (P.O. #, Species Code, Number of Animals, Each \$, Line \$)

The questions I am having difficulty with are:

List the Latin name of all species that do not eat mealworms.
Join Species to Feeding Over Species Code Giving X;
Select from X Type of Food = mealworms Giving Y;
Project Y Over Latin Name Giving Result species Latin name not eating mealworm

List the Latin name of all species that do not live in location 1 (the main tank).
Join Species to Population Over Species Code Giving X;
Select from X Where Location = Location 1 (main tank) Giving Y;
Project Y Over Latin Name Giving Result species Latin name not living in location 1 (main tank)

List the name of all sources that can supply Yellow Tangs, Niger Triggers, and Blue Damselfish.
join Source to Can_Supply over Source_Code giving A
join A to Species over Species_Code giving B
select B where English_Name = “Yellow Tang” giving C1
select C1 where English_Name = “Niger Trigger” giving C2
select C2 where English_Name = “Blue Damselfish” giving C3
join C3 to C2 to C1 giving C
project C over Source_Name giving Sources for Specialty Fish

I am pulling out my hair because I don't think they are correct.....

2. Registered User
Join Date
Oct 2002
Location
Posts
697
Well, I've never seen the relational algebra done the way you've got it... one book I had used lots of greek symbols, I guess to make it look more authentically mathy. So I'll have to assume your syntax is right.

Species (Species Code, Latin Name, English Name)
Location (Location Code, Place in Building, Size, Head Keeper)
Population (Species Code, Location Code, Number of Animals)
Food (Type of Food)
Feeding (Species Code, Type of Food, Amount of Food, Feeding Interval)
Habitats (Habitat Type)
Can Live (Species Code, Habitat Type)
Where Found (Location Code, Habitat Type)
Source (Source Code, Source Name, Source Address, Source Phone, Contact Person)
Can Supply (Source Code, Species Code)
Shipment (P.O. #, Source Code, Order Date, Order Total \$)
Shipment Items (P.O. #, Species Code, Number of Animals, Each \$, Line \$)

The questions I am having difficulty with are:

Originally Posted by bjboyce68
List the Latin name of all species that do not eat mealworms.

Join Species to Feeding Over Species Code Giving X;
Select from X Type of Food = mealworms Giving Y;
Project Y Over Latin Name Giving Result species Latin name not eating mealworm
That'd be "type of food <> mealworms".

Originally Posted by bjboyce68
List the Latin name of all species that do not live in location 1 (the main tank).
Join Species to Population Over Species Code Giving X;
Select from X Where Location = Location 1 (main tank) Giving Y;
Project Y Over Latin Name Giving Result species Latin name not living in location 1 (main tank)
Uh, same problem. If you want the ones that *don't* live in location one, use <> instead of =. Unless your professor prefers != or something like that.

Originally Posted by bjboyce68
List the name of all sources that can supply Yellow Tangs, Niger Triggers, and Blue Damselfish.
join Source to Can_Supply over Source_Code giving A
join A to Species over Species_Code giving B
select B where English_Name = “Yellow Tang” giving C1
select C1 where English_Name = “Niger Trigger” giving C2
select C2 where English_Name = “Blue Damselfish” giving C3
join C3 to C2 to C1 giving C
project C over Source_Name giving Sources for Specialty Fish
This one is a bit tricky. What you're doing is you join the three tables together, getting you rows that relate source names to species names.

But then after you've restricted that to only rows with "Yellow Tang" you're restricting it to rows that have "Niger Trigger." Well, each row can only have one or the other, right?

What you need to do is collect source codes that can supply a particular species and project out the source codes. Do that for each species, save it in a separate relation. Then get the set intersection of all three of these. Finally, join that intersection to get the names.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•