COMP 2019 Assignment 1 – A Star Search
Questions
The questions here are based on the Fishing database whose schema and related information are listed on the last page. Write relational algebra expressions to answer the following questions.
1. Find the largest fish ever caught in each specie. Your result should display the date, time, lake name, swim location in that lake of the catch, the name of the fish species and the weight of the fish.
2. Based on past experience, which swim location(s) will be best for catching the largest trout (in terms of weight)?
3. Find the pair of member and swim where the member caught the most fishes in total weight in that swim. Say, over the years, John caught 201kg of fishes in Swim10, 101 in Swim5, 34 in Swim11,while Kate caught 102kg in Swim10, 11 in Swim6, 123 in Swim 17. The result should display John, his phone number, Swim10 and 201.
4. Based on the frequency of catch reports submitted, find the name of the most frequently visited lake. Note that a member visited multiple swims of the same lake on the same day is considered
ONE visit. For example, On Jan 1, 2021, Kate caught 2 salmons in Swim10 of “Rockwool Lake”,and 5 cods in Swim 101 of the same lake. This is considered as ONE visit by Kate to “Rockwood Lake”.
5. For member(s) who has caught at least one fish in “Rockwood Lake”, find the one(s) that never caught any salmons in that lake. Your result should display the names of the members and their
phone numbers.
6. Based on the catch reports, find the most recent day when Ben Affleck and Matt Damon fished on the same day but at different swims of the same lake.
7. Find the name of the lake(s) where there has been at least one catch report submitted for each species of fish except carp. In other words, every fish species except carp has been caught in that lake(s).
8. Based on the number of fishes caught, are there more brasses, salmons or more cods in the all lakes located in Guelph? The result should be displayed in the same row like: Brasses Salmons Cods 1101 999 1111
9. Find the member(s) who never fishes in lakes located outside their own city (where they live). For this question, assume that a member never fishes in a lake if they never submitted any catch report for that lake. Note that this includes member who has never fished (never submitted any catch report). Your result should display the names of the members, their phones and cities.
10. Find out how many times Tom Brady and Payton Manning caught some brasses on the same day.