# 数据库设计代写 | CS 338 Database Assignment 1 Relational Algebra

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.

E-mail: itcsdx@outlook.com  微信:itcsdx