Results 1 to 2 of 2
  1. #1
    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.
    My answer is:
    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).
    My answer is:
    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.
    My Answer is:
    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. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    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:

    Quote Originally Posted by bjboyce68
    List the Latin name of all species that do not eat mealworms.
    My answer is:

    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".

    Quote Originally Posted by bjboyce68
    List the Latin name of all species that do not live in location 1 (the main tank).
    My answer is:
    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.

    Quote Originally Posted by bjboyce68
    List the name of all sources that can supply Yellow Tangs, Niger Triggers, and Blue Damselfish.
    My Answer is:
    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
  •