CS336 Principles of Database Management Systems
ANSWERS to Query Review Problems

1.

The following relations are part of a patient billing database for a hospital.

• Patient(PID,name,address,phone)
• Stay(PID,admitDate,dischargeDate)
• Treated(PID,Tcode,Tdate)
• Treatment(Tcode,Tdescription,Tfee)

Write queries in the Relational Algebra (RA) for each of the following:

(a)

The address of patient ``D. Z. Spells''.

• RA: $\pi_{address}(\sigma_{name='D.Z.Spells'}(Patient))$
• TRC: $\{t \vert \exists s \in Patient (s[name] = 'D.Z.Spells' \bigwedge s[address] = t[address])\}$
• SQL:

  SELECT address
  FROM Patient
  WHERE name = "D.Z.Spells";
  

(b)

The names of all patients treated on June 1, 2002.

• RA: $\pi_{name}(\sigma_{Tdate=''1-June-2002''}(Patient \bowtie Treated))$
• TRC: $\{t \vert \exists s \in Patient (\exists m \in Treatment(m[PID]=s[PID] \bigwedge m[date] = ''1-June-2002'' \bigwedge t[name]=s[name]))\}$
• SQL:

  SELECT name
  FROM Patient P, Treated T
  WHERE P.PID = T.PID AND
        Tdate = "1-June-2002";
  

(c)

The PIDs of any patients treated under Tcode='000100'.

• RA: $\pi_{PID}(\sigma_{Tcode='000100'}(Treated))$
• TRC: $\{t \vert \exists s \in Treated (Tcode='000100' \bigwedge t[PID]=s[PID])\}$
• SQL:

  SELECT PID
  FROM Treated
  WHERE Tcode='000100';
  

(d)

The description of all treatments received by patient ``N. Welle''.

• RA: $\pi_{Tdescription}(\sigma_{name='N.Welle'}(Patient \bowtie (Treated \bowtie Treatment)))$
• TRC: $\{t \vert \exists s \in Patient (\exists u \in Treated (\exists v \in Treatment... ...ID] \bigwedge s[name]='N.Welle' \bigwedge t[Tdescription]=v[Tdescription])))\}$
• SQL:

  SELECT Tdescription
  FROM Patient P, Treated D, Treatment T
  WHERE P.PID = D.PID AND
        D.Tcode = T.Tcode AND
        P.name = "N.Welle";
  

(e)

The description of the most expensive treatment.

• RA: $\pi_{description}(Treatment - \pi_{A.Tcode,A.Tdescription,A.Tfee}(\sigma_{A.Tfee < B.Tfee}(\rho_{A}(Treatment) \times \rho_{B}(Treatment))))$
• TRC: $\{t \vert \exists s \in Treatment( \not \exists u \in Treatment(u[Tfee]>s[Tfee] \bigwedge t[Tdescription]=s[Tdescription]))\}$
• SQL:

  SELECT Tdescription
  FROM Treatment
  WHERE Tfee >= ALL
        (SELECT Tfee
         FROM Treatment);
  

(f)

The Tcode of any treatment received by all of the patients on June 9, 2002.

• RA: $\pi_{Tcode,PID}(Treated) \div \pi_{PID}(\sigma_{Tdate='9-June-2002'}(Treated))$
• TRC: $\{t \vert \exists s \in Treated (\forall u \in Treated (u[Tdate] = '9-June-2002... ... Treated (u[PID]=v[PID] \bigwedge s[Tcode]=v[Tcode] \bigwedge t[Tcode]=s[Tcode]$
• SQL:

  SELECT T1.Tcode
  FROM Treatment T1
  WHERE NOT EXISTS
          (SELECT T2.PID
           FROM Treatment T2
           WHERE
                T2.Tdate = '9-June-2002')
        MINUS
          (SELECT T3.PID
           FROM Treatment T3
           WHERE
                T1.Tcode = T3.Tcode AND
                T3.Tdate = '9-June-2002');
  

2.

For the relations in Problem Set 6, write the following query in SQL:

The descriptions of the treatments received by the patient who received the most treatments in the month of February, 2002, in alphabetical order.

SELECT T1.Tdescription
FROM Treatment T1
WHERE
     Tcode IN
       (SELECT T2.Tcode
        FROM Treated T2
        WHERE Tdate LIKE '%-February-2002'
        GROUP BY Tcode
        HAVING COUNT(*) >= ALL
          (SELECT COUNT(*)
           FROM Treated
           GROUP BY Tcode)
        );