fit:bie-dbs:seminars:ra [Michal Valenta]

Relational Algebra - exercises

Suppose a hospital database with following structure:

PATient (PatientID, PatientNAme, PaADdress, PatientDofBirth)

DOCtor (DoctorID, DoctorNAme, SPEcialization)

VISit (PatientID, DoctorID, DateofVIsit, DIAgnose)

Let us short the names of relations and attribute as follows:

PAT (PID, PNA, PAD, PDB)

DOC (DID, DNA, SPE)

VIS (PID, DID, DVI, DIA)

Write following queries in realtional algebra:

1. List of all doctor's specializations

 
DOC[SPE]

The duplicities are filtered automatically

2. Names of all orthopeds.

 
DOC(SPE='ORTOPED')[DNA]

But two orthopeds with the same name are treated as one in above solution, hence we can do:

 DOC(SPE='ORTOPED')[DNA, DID]

3. Names and addresses of all patients borned before 1920 (suppose date attributes in format YYYYMMDD).

 
 PAT(PDB < '19200101')[PNA, PAD]

Texts and data atributes are enclosed in apostrophes.

4. DID's of doctors visited by patient Scott.

 
PAT(PNA='SCOTT')[PID]*VIS[DID]

or

PAT*VIS(PNA='SCOTT')[DID]

Formulations are equivalent. The later is more readable.

5. Names of internist visited by patient Scott.

 
PAT*VIS*DOC(SPE='INTERNISTA' AND PNA='SCOTT')[DNA]

6. Names and addresses of patients visited by doctor Jones.

 
PAT*VIS*DOC(DNA='JONES')[PNA, PAD]

7. Names and address of patients visited only by doctor Jones (i.e. by no other doctor).

 
 PAT*VIS*DOC(DNA='JONES')[PNA, PAD]
 - (set minus)
 PAT*VIS*DOC(DNA<>'JONES')[PNA, PAD]

8. Two-tuples of names of patients living in the same address.

 
P1 := PAT
P2 := PAT
P1 x P2 (P1.PAD = P2.PAD AND P1.PID < P2.PID)[P1.PNA, P2.PNA]
alternatively:
P1 [P1.PAD = P2.PAD AND P1.PID < P2.PID] P2 [P1.PNA, P2.PNA]

9. The doctors who already had patients.

 DOC <* VIS

10. Names of doctors who have no patients yet.

 
DOC “antijoin” VIS
alternatively:
DOC - (DOC<*VIS)

11. Which specialization did not work yet.

Carifull, this is wrong: !!!

 DOC “antijoin” (VIS [SPE])

There may be more doctors with the same spec.

And this is correct:

DOC[SPE] - (DOC*VIS[SPE])

12. Names and addresses of patients with diagnose diabetes (do not use a natural join for this query).

 
(PAT x VIS (DIA = 'DIABETES' AND PAT.PID = VIS.PID))[PNA, PAD]
alternatively:
(PAT [DIA='DIABETES' AND PAT.PID = VIS.PID] VIS )[PNA, PAD]

13. The patients who visited any doctor. = the patients who were on the visit

 
PAT<*VIS

14. The patients who did not visited each doctor. Including the patients without visits.

((PAT[PID] x DOC[DID]) – VIS[PID,DID])[PID]*PAT

15. The patients who visited all doctors.

Solution without using relational division

Idea:

All patients who were in a visit

- (set minus)

The patients who did not visit any doctor.

All patients who were on a visit:

 VIS[PID]

The patients who did not visit any doctor:

 
the universe 
(each patient who were on visit visited each doctor):
   U := VIS[PID] x DOC[DID]
reality - which visits realy happend:
   R := VIS[PID, DID]
The visits which were not realized:
   N:= U – R
The patients who did not visit any doctor 
(there is a doctor who was not visited by them)
   N[PID] 
The result is:
 
(VIS[PID] – N[PID]) * PAT

Solution with using relational division

(VIS[PID, DID] “divided by” DOC[DID]) * PAT

16. The patients who visited at least one othoped. = the patients who visited any orthoped

 
PAT<*(VIS*DOC(SPE='ORTOPED'))

17. The patients who did not visited any orthoped.

PAT - (PAT<*(VIS*DOC(SPE='ORTOPED')))
alternatively
PAT “antijoin” (VIS*DOC(SPE='ORTOPED'))

18. The patients from Pardubice who visited each orthoped.

 
(VIS[PID, DID] 
 “divided by” 
(DOC(SPE='ORTOPED')[DID]))) 
* PAT (PAD='PARDUBICE')