Relational Algebra

Relational Algebra November 13, 2014

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:

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.

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

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.

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))

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.

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))

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.

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

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

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)

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.

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))

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:

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$.

Sample instances Solution 2 (a) – 1 Players: p1 p2 p3 Step 1: get all the players with salary more than 2M R1: pname,tname(salary> 2M(Played)) p1, t1 p1, t2 p1, t3 p2, t2 p2, t3 p3, t1 Teams: t1 t2 t3 Played: p1, t1, 3 p1, t1, 1 p1, t2, 4 p1, t3, 5 p2, t1, 1 p2, t2, 4 p2, t3, 4 p3, t1, 4

Sample instances Solution 2 (a) – 2 Players: p1 p2 p3 Step 2:[all possible combination] R2: pname,tname (Player x Team) p1, t1 p1, t2 p1, t3 p2, t1 p2, t2 p2, t3 p3, t1 p3, t2 p3, t3 Teams: t1 t2 t3 Played: p1, t1, 3M p1, t1, 1M p1, t2, 4M p1, t3, 5M p2, t1, 1M p2, t2, 4M p2, t3, 4M p3, t1, 4M

Sample instances Solution 2 (a) – 3 Players: p1 p2 p3 Step 3: get players that have not played for all the teams R3: R2-R1 p2, t1 p3, t2 p3, t3 Teams: t1 t2 t3 Played: p1, t1, 3 p1, t1, 1 p1, t2, 4 p1, t3, 5 p2, t1, 3 p2, t2, 4 p2, t3, 4 p3, t1, 4 Step 4: R4:pname(R3): P2 p3 Step 5: get players that have played for all the teams pname(Player)-R4 p1

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

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.

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.

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.

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, yearPlayed)) Winner2(pname2, year2, tname2) (Winner1) pname1(pname1=pname2 AND tname1<>tname2 AND (year1=year2+1 OR year1=year2-1) (Winner1  Winner2))

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.

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))

Relational Algebra