Consider the following data set:
DROP TABLE IF EXISTS employee;
CREATE TABLE employee
(employee_id SERIAL PRIMARY KEY
,name VARCHAR(30) NOT NULL UNIQUE
,role VARCHAR(12) NOT NULL
);
INSERT INTO employee VALUES
(101,'Dr. No','Doctor'),
(102,'Dr. Pamela Lillian Isley','Doctor'),
(103,'Dr. Miranda Bailey','Doctor'),
(104,'Margaret Houlihan','Nurse'),
(105,'Thor Lundgren','Nurse'),
(106,'Paul Flowers','Nurse');
DROP TABLE IF EXISTS patient;
CREATE TABLE patient
(patient_id SERIAL PRIMARY KEY
,name VARCHAR(30) NOT NULL
);
INSERT INTO patient VALUES
(11,'Alice'),
(12,'Ben'),
(13,'Charlie'),
(14,'Dawn'),
(15,'Eric');
DROP TABLE IF EXISTS examination;
CREATE TABLE examination
(examination_id SERIAL PRIMARY KEY
,patient_id INT NOT NULL
,employee_id INT NOT NULL
,examination_date DATE NOT NULL
,UNIQUE(patient_id,employee_id,examination_date)
);
INSERT INTO examination VALUES
(1,11,101,'2021-01-01'),
(2,12,104,'2021-01-01'),
(3,12,102,'2021-01-02'),
(4,13,106,'2021-01-02'),
(5,14,105,'2021-01-03'),
(6,15,101,'2021-01-03'),
(7,15,104,'2021-01-03'),
(8,15,106,'2021-01-05');
A query:
SELECT e.examination_id
, e.examination_date
, p.name patient
, u.name examined_by
, u.role
FROM examination e
JOIN employee u
ON u.employee_id = e.employee_id
JOIN patient p
ON p.patient_id = e.patient_id;
+----------------+------------------+---------+--------------------------+--------+
| examination_id | examination_date | patient | examined_by | role |
+----------------+------------------+---------+--------------------------+--------+
| 1 | 2021-01-01 | Alice | Dr. No | Doctor |
| 3 | 2021-01-02 | Ben | Dr. Pamela Lillian Isley | Doctor |
| 2 | 2021-01-01 | Ben | Margaret Houlihan | Nurse |
| 4 | 2021-01-02 | Charlie | Paul Flowers | Nurse |
| 5 | 2021-01-03 | Dawn | Thor Lundgren | Nurse |
| 6 | 2021-01-03 | Eric | Dr. No | Doctor |
| 7 | 2021-01-03 | Eric | Margaret Houlihan | Nurse |
| 8 | 2021-01-05 | Eric | Paul Flowers | Nurse |
+----------------+------------------+---------+--------------------------+--------+
...and not an EER/ERD in sight