Relational Algebra and SQL Examples for Doctor-Patient Database

This post demonstrates relational algebra operations along with their equivalent SQL queries using the Doctor and Patient tables.

Given Tables

doctor_id	doctor_name	specialization	room_no
D101	Dr. Khan	Dermatologist	101
D102	Dr. Ali	Cardiologist	102
D103	Dr. Sultana	Neurologist	103

patient_id	patient_name	age	doctor_id
P001	Rahim	30	D101
P002	Fatema	22	D102
P003	Karim	27	D101
P004	Salma	35	D103

Relational Algebra:

σ_{age>25 ∧ doctor_id='D101'}(Patient)[patient_name, age]

SQL:

SELECT patient_name, age
FROM Patient
WHERE age > 25 AND doctor_id = 'D101';

Result:

patient_name	age
Rahim	30
Karim	27

Relational Algebra:

π_{patient_name, age} (Patient ⨝ σ_{specialization='Dermatologist'}(Doctor))

SQL:

SELECT p.patient_name, p.age
FROM Patient p
JOIN Doctor d ON p.doctor_id = d.doctor_id
WHERE d.specialization = 'Dermatologist';

Result:

patient_name	age
Rahim	30
Karim	27

Relational Algebra:

ρ_{DoctorDetails(d_name/doctor_name, room_number/room_no)}(Doctor)

SQL:

SELECT doctor_id,
       doctor_name AS d_name,
       specialization,
       room_no AS room_number
FROM Doctor;

Result:

doctor_id	d_name	specialization	room_number
D101	Dr. Khan	Dermatologist	101
D102	Dr. Ali	Cardiologist	102
D103	Dr. Sultana	Neurologist	103

Relational Algebra:

π_{specialization, age} (Doctor × Patient)

SQL:

SELECT d.specialization, p.age
FROM Doctor d
CROSS JOIN Patient p;

Result: