Relational Algebra

1
Relational Algebra

• February 18, 2014

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

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.

4
Solution 1 (a)

• Give the name of doctors who don't suffer from
  any disease.
• ?DoctorsWhoSuffered(?name (?namepatient_name
• (Doctor ? Treated)))
• ?name (Doctor) - DoctorsWhoSuffered

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.

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 (?name1name2 AND diagnostic1 ltgt
  diagnostic2
• (Patient1 ? Patient2))

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.

8
Solution 1 (c)

• Give the names of doctors who are also patients
  suffering from a disease in their own
  specialization.
• ?name (?namepatient_name AND disease-of-specialty
  diagnostic (Doctor ? Treated))

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.

10
Solution 1 (d)

• Find diseases for which there is only one
  medication.
• ?DiseaseWithMoreThanOneTreatment (?t1.disease
  (?t1.diseaset2.disease AND t1.medication ltgt
  t2.medication (?t1(Treatment) ? ?t2(Treatment))
• ?disease (Treatment) - DiseaseWithMoreThanOneTreat
  ment

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

12
Solution 1 (e)

• Find the names of patient who had an operation
  done by a doctor with HIV.
• ?DoctorsWithHIV(doc_name) (?name
  (?namepatient_name AND diagnostic HIV
  (Doctor ? Treated))
• ?patient_name (?doc_namedoctor_name AND
  procedureintervention (DoctorsWithHIV ?
  Treated)

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
  patients name, doctors name, the date, and the
  diagnosis.

14
Solution 1 (f)

• List the patients who consulted 2 or more doctors
  and was given exactly the same diagnosis. List
  patients name, doctors name, the date, and the
  diagnosis.
• ?t1.patient_name, t1.doctor_name, t1.date,
  t1.disgnostic (?t1.patient_namet2.patient_name
  AND t1.doctor_nameltgtt2.doctor_name AND
  t1.diagnostict2.diagnostic
• (? t1(Treated) ? ? t2(Treated))

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

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.

17
Solution 2 (a) 1
Sample instances

• Teams
• t1
• t2
• t3

Players p1 p2 p3
Step 1 get all the players with salary more than
2M R1 ?pname,tname(?salarygt 2M (Played)) p1,
t1 p1, t2 p1, t3 p2, t2 p2, t3 p3, t1
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
18
Solution 2 (a) 2
Sample instances

• Teams
• t1
• t2
• t3

Players p1 p2 p3
Step 2all 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
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
19
Solution 2 (a) 3
Sample instances
Players p1 p2 p3

• Teams
• t1
• t2
• t3

Step 3 get players that have not played for all
the teams R3 R2-R1 p2, t1 p3, t2
p3, 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
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 gt 2M (Played)))
• 2) ?pname(Player)-R4

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.

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 a1a2

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

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(?pname1pname2 AND tname1ltgttname2 AND
  (year1year21 OR year1year2-1)
• (Winner1 ? Winner2))

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.

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.salaryltp2.salary AND
  p1.tnamep2.tname
• 
  (?p1(Played) ? ?p2(Played)))
• ?HighestPaidPlayer (?pname, tname, salary(Played)
  NotHighestPaidPlayers)
• ?coach( ?Team.tnameHighestPaidPlayer.tname AND
  Team.salarygtHighestPaidPlayer.salary
• 
  (Team ? HighestPaidPlayer))