Below is a sample of how the database may look like. Note the data is arbitrary
ID: 3746129 • Letter: B
Question
Below is a sample of how the database may look like. Note the data is arbitrary and in general may contain millions of rows per table. Use it only as a guide to understand the data format, do not do any cross references manually, you won’t be able to do it when working with large datasets.
Meteorologist
MID
Name
Age
Affiliation
0
Balfour Currie
78
NWS
1
Frank Field
91
WWOR
2
Mr. G
69
WCBS
3
Francis Beaufort
83
MRIA
4
Edmond Halley
86
NWS
Forecast
MID
Date
ForecastWeather
ForecastTemperature
0
1972-02-05
Sunny
35
1
1972-02-05
Sunny
40
4
1972-02-05
Cloudy
42
0
1972-02-06
Rain
40
3
1972-02-06
Rain
40
2
1972-02-06
Cloudy
40
1
1972-02-08
Cloudy
15
2
1972-02-09
Hail
-10
3
1972-02-09
Snow
0
4
1972-02-10
Rain
40
Weather
Date
RealWeather
RealTemperature
1972-02-05
Sunny
35
1972-02-06
Rain
40
1972-02-07
Cloudy
10
1972-02-08
Sunny
40
1972-02-09
Hail
-10
1972-02-10
Rain
30
1972-02-11
Rain
40
Write Relational Algebra Statements to solve the following queries:
Find all Forecasts made by Meteorologist Mr. G. Display Date, ForecastWeather, and ForecastTemperature.
Find all Forecasts where the Weather and the Temperature was correct. Display Meteorologist Name, Date, ForecastWeather, and ForecastTemperature.
Find all Forecasts made by Meteorologist affiliated with NWS where both ForecastTemperature and ForecastWeather were incorrect. Display Meteorologist Name, Date, ForecastWeather, ForecastTemperature, RealWeather, and RealTemperature.
Find all Forecasts where the Weather was correct and the Temperature was off by at most 5 degrees. Display Meteorologist Name, Date, Weather, Temperature, and RealTemperature.
Find the dates that no Meteorologist made a forecast for. Display Date, RealWeather, and RealTemperature.
MID
Name
Age
Affiliation
0
Balfour Currie
78
NWS
1
Frank Field
91
WWOR
2
Mr. G
69
WCBS
3
Francis Beaufort
83
MRIA
4
Edmond Halley
86
NWS
Explanation / Answer
(1)Find all Forecasts made by Meteorologist Mr. G. Display Date, ForecastWeather, and ForecastTemperature.
Query- select Date ,ForecastWeather,ForecastTemperature
from Forecast f -- f is alies of table Forecast
join Metrologist m on f.MID=m.MID -- m is alies of table Metrologist
where m.Name='Mr. G'
In this query, we perform the inner join. There only match MID data of Mr. G shown
(2)Find all Forecasts where the Weather and the Temperature was correct. Display Meteorologist Name, Date, ForecastWeather, and ForecastTemperature.
Query- select m.Name,Date ,ForecastWeather,ForecastTemperature
from Forecast f
join Weather w on w.date=f.date and f.ForecastWeather=w.RealWeather and f.ForecastTemperature=w.RealTemperature
join Metrologist m on f.MID=m.MID
In this query we match all data of Forecast table to Weather .
(3)Find all Forecasts made by Meteorologist affiliated with NWS where both ForecastTemperature
and ForecastWeather were incorrect. Display Meteorologist Name, Date, ForecastWeather,
ForecastTemperature, RealWeather, and RealTemperature.
Query- select m.Name,Date ,ForecastWeather,ForecastTemperature,w.RealWeather,w.RealTemperature
from Forecast f
join Weather w on w.date=f.date and f.ForecastWeather!=w.RealWeather and f.ForecastTemperature!=w.RealTemperature
join Metrologist m on f.MID=m.MID
where m.Affiliation='NWS'
(4)Find all Forecasts where the Weather was correct and the Temperature was off by at most 5 degrees.
Display Meteorologist Name, Date, Weather, Temperature, and RealTemperature.
Query- select m.Name,Date ,ForecastWeather,ForecastTemperature,w.RealTemperature
from Forecast f
join Weather w on w.date=f.date and f.ForecastWeather=w.RealWeather and f.ForecastTemperature+5=w.RealTemperature
join Metrologist m on f.MID=m.MID
(5)Find the dates that no Meteorologist made a forecast for. Display Date, RealWeather, and RealTemperature.
Query- select Date ,w.RealWeather,w.RealTemperature
from Forecast f
join Weather w on w.date!=f.date
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.