SQL — Doctor/Patient/Visit: 25-Comp-B3 Data Bases and File Systems, December 2014, Q5

Question 5: SQL — Doctor/Patient/Visit (20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Given. Doctor(licno, drname, specialty), Patient(patid, patname, address, phone, DOB), Visit(licno, patid, date, type, diagnosis, charge), with Visit.licno/patid foreign keys into Doctor/Patient.

(a) All doctors and their specialties — a direct projection, no join needed.

SELECT drname, specialty
FROM   Doctor;

(b) licno of doctors who saw patient Mary Adams — join Visit to Patient on patid, filter by name.

SELECT DISTINCT V.licno
FROM   Visit V, Patient P
WHERE  V.patid = P.patid AND P.patname = 'Mary Adams';

(c) names of doctors who have made house calls — join Doctor to Visit on licno, filter by visit type.

SELECT DISTINCT D.drname
FROM   Doctor D, Visit V
WHERE  D.licno = V.licno AND V.type = 'house call';

(d) names and addresses of patients diagnosed with peptic ulcers — join Patient to Visit on patid, filter by diagnosis.

SELECT DISTINCT P.patname, P.address
FROM   Patient P, Visit V
WHERE  P.patid = V.patid AND V.diagnosis = 'peptic ulcer';

Sample-data check — Question 5
Query	Result on the sample dataset
(a)	(Chen, Cardiology), (Osei, Pediatrics), (Blake, GeneralPractice)
(b)	licno 1, 3
(c)	Blake
(d)	Mary Adams, John Kim

Concept & Theory: Joins over a Ternary-Key Relationship Table

Topic: Visit is a classic "relationship-as-table" with a composite key spanning both participants plus a discriminator (date), carrying its own descriptive attributes.

Key relations: A query that filters on an attribute of one endpoint (Patient.patname, Doctor.specialty) but selects columns of the other requires joining THROUGH the relationship table on both foreign keys.

Why this works: DISTINCT is needed in (b)/(c)/(d) because the same doctor/patient can appear on multiple qualifying Visit rows (e.g. Mary Adams may have seen the same doctor's group more than once), which would otherwise duplicate the output.

Common pitfall: Forgetting the DISTINCT and reporting the same doctor/patient once per matching visit row instead of once per qualifying person.

Source: Ramakrishnan & Gehrke, Database Management Systems, ch. 5 (SQL: Queries, Constraints, Triggers).

Question 5 of 8: SQL — Doctor/Patient/Visit

Question 5: SQL — Doctor/Patient/Visit (20 marks)