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

<code> 
DOC[SPE]
</code>   
//The duplicities are filtered automatically//

2. Names of all orthopeds.
 
<code> 
DOC(SPE='ORTOPED')[DNA]
</code>
But two orthopeds with the same name are treated as one in above solution, hence we can do:
<code>
 DOC(SPE='ORTOPED')[DNA, DID]
</code>

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

<code> 
 PAT(PDB < '19200101')[PNA, PAD]
</code>
//Texts and data atributes are enclosed in apostrophes.//
 
4. DID's of doctors visited by patient Scott.

<code> 
PAT(PNA='SCOTT')[PID]*VIS[DID]
</code>
or
<code>
PAT*VIS(PNA='SCOTT')[DID]
</code>
//Formulations are equivalent. The later is more readable.//

5. Names of internist visited by patient Scott.

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

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

<code> 
PAT*VIS*DOC(DNA='JONES')[PNA, PAD]
</code>

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

<code> 
 PAT*VIS*DOC(DNA='JONES')[PNA, PAD]
 - (set minus)
 PAT*VIS*DOC(DNA<>'JONES')[PNA, PAD]
</code>
 
8. Two-tuples of names of patients living in the same address.

<code> 
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]
</code>

9. The doctors who already had patients.

<code>
 DOC <* VIS
</code>
 
10. Names of doctors who have no patients yet.

<code> 
DOC “antijoin” VIS
alternatively:
DOC - (DOC<*VIS)
</code>
 
11. Which specialization did not work yet.

**Carifull, this is wrong:  !!!** 
<code>
 DOC “antijoin” (VIS [SPE]) 
</code>
//There may be more doctors with the same spec.//
 
**And this is correct:** 
<code>
DOC[SPE] - (DOC*VIS[SPE])
</code>

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

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

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

<code> 
PAT<*VIS
</code>

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

<code>
((PAT[PID] x DOC[DID]) – VIS[PID,DID])[PID]*PAT
</code>

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:// 
<code> VIS[PID]</code>
 
//The patients who did not visit any doctor://
<code> 
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
</code>

**Solution with using relational division** 
<code>
(VIS[PID, DID] “divided by” DOC[DID]) * PAT
</code>

16. The patients who visited at least one othoped.
 = the patients who visited any orthoped
<code> 
PAT<*(VIS*DOC(SPE='ORTOPED'))
</code>

17. The patients who did not visited any orthoped.
<code>
PAT - (PAT<*(VIS*DOC(SPE='ORTOPED')))
alternatively
PAT “antijoin” (VIS*DOC(SPE='ORTOPED'))
</code>

18. The patients from Pardubice who visited each orthoped.
<code> 
(VIS[PID, DID] 
 “divided by” 
(DOC(SPE='ORTOPED')[DID]))) 
* PAT (PAD='PARDUBICE')
</code>