The following is a relational schema for a database to document shark sightings for a research project. Primary keys are underlined. Foreign Keys are identified by FK, and their name indicates the attribute that they reference. ________________________________________________________________ Sharks(shark_id:Integer, name:String, species_name:String, tagged_by_mission_id:Integer (FK)) Sightings(shark_id:Integer (FK), sighting_num:Integer, sight_time:Timestamp, lat:Real, lng:Real, ocean:String, mission_id:Integer (FK)) Missions(mission_id:Integer, start_date:Date, end_date:Date, vessel_id:Integer (FK)) ResearchVessels(vessel_id:Integer, name:String) ________________________________________________________________ Sharks identified by a unique id, have a name which is not necessarily unique, and a scientific species name. All Sharks are tagged and are sighted by Research Vessels, which are research ships that go on research Missions throughout the year. Each Mission has a start and end date. Shark sightings record the shark’s id, and the mission that sighted the shark. Sightings are enumerated for each shark (i.e. the sighting_num) starting with 1. That means for the initial sighting of a shark the sighting’s sighting_num is 1, the next time that shark is sighted the sighting_num is 2 and so on. Every sighting includes the time (as a timestamp), the location (as latitude and longitude coordinates), and the ocean name (i.e. “Pacific”, “Atlantic”) in which the sighting took place. When a new Shark is added to the database, the Mission which tagged the shark is recorded in the Shark table (tagged_by_mission_id). (a) Write the Relational Algebra expressions for the set of research vessel names which have sighted sharks in the “Pacific” in 2015 (Assume that includes only missions that began and ended in 2015). What would be the correct answer to (a)?
The following is a relational schema for a
sightings for a research project. Primary keys are underlined. Foreign
Keys are identified by FK, and their name indicates the attribute that they
reference.
________________________________________________________________
Sharks(shark_id:Integer, name:String, species_name:String,
tagged_by_mission_id:Integer (FK))
Sightings(shark_id:Integer (FK), sighting_num:Integer,
sight_time:Timestamp, lat:Real, lng:Real, ocean:String, mission_id:Integer
(FK))
Missions(mission_id:Integer, start_date:Date, end_date:Date,
vessel_id:Integer (FK))
ResearchVessels(vessel_id:Integer, name:String)
________________________________________________________________
Sharks identified by a unique id, have a name which is not necessarily
unique, and a scientific species name.
All Sharks are tagged and are sighted by Research Vessels, which are
research ships that go on research Missions throughout the year. Each
Mission has a start and end date.
Shark sightings record the shark’s id, and the mission that sighted the
shark. Sightings are enumerated for each shark (i.e. the sighting_num)
starting with 1. That means for the initial sighting of a shark the sighting’s
sighting_num is 1, the next time that shark is sighted the sighting_num is 2
and so on. Every sighting includes the time (as a timestamp), the location
(as latitude and longitude coordinates), and the ocean name (i.e.
“Pacific”, “Atlantic”) in which the sighting took place.
When a new Shark is added to the database, the Mission which tagged the
shark is recorded in the Shark table (tagged_by_mission_id).
(a) Write the Relational Algebra expressions for the set of research
vessel names which have sighted sharks in the “Pacific” in 2015
(Assume that includes only missions that began and ended in 2015).
What would be the correct answer to (a)?
Step by step
Solved in 2 steps with 2 images
