1 relational algebra april 13, 2015. 2 exercise 1 consider the following relational database scheme:...
TRANSCRIPT
![Page 1: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/1.jpg)
1
Relational Algebra
April 18, 2023
![Page 2: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/2.jpg)
2
Exercise 1 Consider the following relational database scheme:
Treatment (disease, medication) Doctor(name, disease-of-specialization) Treated (doctor_name, patient_name, date, procedure,
diagnostic) Patients and doctors are uniquely identified by their name A patient may suffer from several diseases, and may take
several medications for each disease. It is possible to a doctor to be a patient and vice-versa. The domains of the attributes "disease" and "disease-of-
specialization" are the same, namely, the set of all diseases. In "treated" relation, the procedure could be consultation, or
intervention (surgery etc.) and the diagnostic could be the name of a disease or the type of intervention.
Write the following queries in relational algebra:
![Page 3: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/3.jpg)
3
Exercise 1 (a) Treatment (disease, medication) Doctor (name, disease-of-specialization) Treated (doctor_name, patient_name, date,
procedure, diagnostic)
Give the name of doctors who don't suffer from any disease.
![Page 4: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/4.jpg)
4
Solution 1 (a) Give the name of doctors who don't suffer
from any disease.DoctorsWhoSuffered(name (name=patient_name
(Doctor Treated)))
name (Doctor) - DoctorsWhoSuffered
![Page 5: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/5.jpg)
5
Exercise 1 (b) Treatment (disease, medication) Doctor (name, disease-of-specialization) Treated (doctor_name, patient_name, date,
procedure, diagnostic)
List patients who suffer from more than one disease.
![Page 6: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/6.jpg)
6
Solution 1 (b) List patients who suffer from more than one
disease.Patient1(name1, diagnostic1 (patient_name, diagnostic
( Treated))Patient2(name2, diagnostic2 (Patient1)
name1 (name1=name2 AND diagnostic1 <> diagnostic2
(Patient1 Patient2))
![Page 7: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/7.jpg)
7
Exercise 1 (c) Treatment (disease, medication) Doctor (name, disease-of-specialization) Treated (doctor_name, patient_name, date,
procedure, diagnostic)
Give the names of doctors who are also patients suffering from a disease in their own specialization.
![Page 8: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/8.jpg)
8
Solution 1 (c) Give the names of doctors who are also
patients suffering from a disease in their own specialization.
name (name=patient_name AND disease-of-specialty = diagnostic (Doctor Treated))
![Page 9: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/9.jpg)
9
Exercise 1 (d) Treatment (disease, medication) Doctor (name, disease-of-specialization) Treated (doctor_name, patient_name, date,
procedure, diagnostic)
Find diseases for which there is only one medication.
![Page 10: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/10.jpg)
10
Solution 1 (d) Find diseases for which there is only one
medication.DiseaseWithMoreThanOneTreatment (t1.disease
(t1.disease=t2.disease AND t1.medication <> t2.medication (t1(Treatment) t2(Treatment))
disease (Treatment) - DiseaseWithMoreThanOneTreatment
![Page 11: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/11.jpg)
11
Exercise 1 (e) Treatment (disease, medication) Doctor (name, disease-of-specialization) Treated (doctor_name, patient_name, date,
procedure, diagnostic)
Find the names of patient who had an operation done by a doctor with HIV
![Page 12: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/12.jpg)
12
Solution 1 (e) Find the names of patient who had an
operation done by a doctor with HIV.DoctorsWithHIV(doc_name) (name (name=patient_name AND
diagnostic = ‘HIV’ (Doctor Treated))
patient_name (doc_name=doctor_name AND
procedure=‘intervention’ (DoctorsWithHIV Treated)
![Page 13: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/13.jpg)
13
Exercise 1 (f) Treatment (disease, medication) Doctor (name, disease-of-specialization) Treated (doctor_name, patient_name, date,
procedure, diagnostic)
List the patients who consulted 2 or more doctors and was given exactly the same diagnosis. List patient’s name, doctor’s name, the date, and the diagnosis.
![Page 14: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/14.jpg)
14
Solution 1 (f) List the patients who consulted 2 or
more doctors and was given exactly the same diagnosis. List patient’s name, doctor’s name, the date, and the diagnosis.
t1.patient_name, t1.doctor_name, t1.date, t1.disgnostic (t1.patient_name=t2.patient_name AND t1.doctor_name<>t2.doctor_name AND
t1.diagnostic=t2.diagnostic ( t1(Treated) t2(Treated))
![Page 15: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/15.jpg)
15
Exercise 2 Consider the following relation scheme about hockey teams:
Team (tname, year, coach, salary) Player (pname, position) Winner (tname, year) Played (pname, tname, year, salary)
Assume that players and coaches appointments with teams are on a calendar year basis.
The team relation indicates teams, their coaches on yearly basis and their salaries.
The player relation indicates players and the position each player plays.
The winner relation indicates the Stanley Cup winners. The played relation indicates players and the team they have
played for on a yearly basis. Write the following queries in Relational Algerbra:
![Page 16: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/16.jpg)
16
Exercise 2 (a) Team (tname, year, coach, salary) Player (pname, position) Winner (tname, year) Played (pname, tname, year, salary)
List all the players who played for all the teams in the league, with a salary higher than 2M$.
![Page 17: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/17.jpg)
17
Teams:
t1 t2t3
Played:
p1, t1, 3p1, t1, 1p1, t2, 4p1, t3, 5p2, t1, 1p2, t2, 4p2, t3, 4p3, t1, 4
Step 1: get all the players with salary more than 2M
R1:pname,tname(salary> 2M (Played))
p1, t1p1, t2p1, t3p2, t2p2, t3p3, t1
Players:
p1p2p3
Sample instances Solution 2 (a) –
1
![Page 18: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/18.jpg)
18
Step 2:[all possible combination]
R2: pname,tname (Player x Team)
p1, t1p1, t2 p1, t3p2, t1 p2, t2 p2, t3 p3, t1 p3, t2 p3, t3
Teams:
t1 t2t3
Played:
p1, t1, 3Mp1, t1, 1Mp1, t2, 4Mp1, t3, 5Mp2, t1, 1Mp2, t2, 4Mp2, t3, 4Mp3, t1, 4M
Players:
p1p2p3
Sample instances Solution 2 (a) –
2
![Page 19: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/19.jpg)
19
Step 3: get players that have not played for all the teams
R3: R2-R1p2, t1 p3, t2 p3, t3 Step 4: R4: pname(R3):
P2p3
Played:
p1, t1, 3p1, t1, 1p1, t2, 4p1, t3, 5p2, t1, 3p2, t2, 4p2, t3, 4p3, t1, 4
Players:
p1p2p3
Sample instances Solution 2 (a) –
3Teams:
t1 t2t3
Step 5: get players that have played for all the teams
pname(Player)-R4
p1
![Page 20: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/20.jpg)
20
Solution 2 (a) -4 List all the players who played for all the
teams in the league, with a salary higher than 2M$.
Final answer in two lines!
1) R4(pname(pname,tname (Player x Team)-pname,tname (salary > 2M (Played)))
2) pname(Player)-R4
![Page 21: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/21.jpg)
21
Exercise 2 (b) Team (tname, year, coach, salary) Player (pname, position) Winner (tname, year) Played (pname, tname, year, salary)
List coaches who have also been players, and have coached only teams they once played for.
![Page 22: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/22.jpg)
22
Solution 2 (b) List coaches who have also been players,
and have coached only teams they once played for.
R1(Tname,name)(tname,pname (Played))
R2(Tname, name)(tname,coach (Team))
Answer= name (R2) - name (R2-R1)
{a1,a2} – {a1}={a2}
R2
T1 a1
T2 a1
T3 a1
T1 a2
T4 a2
R1
T1 a1
T2 a1
T4 a1
T1 a2
T3 a2
T4 a2
Answer would be a2.
![Page 23: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/23.jpg)
23
Exercise 2 (c) Team (tname, year, coach, salary) Player (pname, position) Winner (tname, year) Played (pname, tname, year, salary)
List all the players who won the Stanley Cup during two consecutive years with two different teams.
![Page 24: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/24.jpg)
24
Solution 2 (c) List all the players who won the Stanley Cup
during two consecutive years with two different teams.
Winner1(pname1, year1, tname1) (Played.pname, Played.year,
Winner.tname (Winner tname, year Played))
Winner2(pname2, year2, tname2) (Winner1)
pname1(pname1=pname2 AND tname1<>tname2
AND (year1=year2+1 OR year1=year2-1) (Winner1 Winner2))
![Page 25: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/25.jpg)
25
Exercise 2 (d) Team (tname, year, coach, salary) Player (pname, position) Winner (tname, year) Played (pname, tname, year, salary)
List all coaches who earn more than the highest player's salary in the team.
![Page 26: 1 Relational Algebra April 13, 2015. 2 Exercise 1 Consider the following relational database scheme: Treatment (disease, medication) Doctor(name, disease-of-specialization)](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c765503460f9492a42f/html5/thumbnails/26.jpg)
26
Solution 2 (d) List all coaches who earn more than the
highest player's salary in the team.NotHighestPaidPlayers(pname, tname, salary)
(p1.pname, p1.tname, p1.salary
(p1.salary<p2.salary AND p1.tname=p2.tname
(p1(Played) p2(Played)))
HighestPaidPlayer (pname, tname, salary(Played) – NotHighestPaidPlayers)
coach( Team.tname=HighestPaidPlayer.tname AND Team.salary>HighestPaidPlayer.salary
(Team HighestPaidPlayer))