Relationships in MySQL EER Diagram

Question

I want to map inheritance in MySQL EER diagram.

Scenario:

The employee table is the superclass of doctor and nurse tables.

The employee has PK - emp_no and the common attributes of both of the subclasses.

I want to create these schemas in MySQL Workbench.

I searched for many examples and I figured out that there are 3 solutions for inheritance.

One of them is to create a separate table for each entity and include the PK of the superclass in the subclasses.

I have following entities:

employee (emp_no,...)
doctor(doct_no (pk),emp_no(fk),..)
nurse (nurse_no (pk), emp_no(fk),..)

I think this solution is quite reasonable, however, I'm confused about the type of relationships between employee and its subclasses.

Is it a non-identifying relationship because both of the subclasses have their own PKs?

But on the other hand, all the details about the doctor and nurse are stored in the employee table so I guess we can not identify who the doctor is by looking at only the doctor table.

If it is the identifying relationship, then the primary key for the doctor will become the composite key - doct_no,emp_no, right?

And also, in general, if we have employee_no which identifies all the doctors and nurses do we actually need doct_no, and nurse_no as unique identifiers in subclasses?

Answer 1

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