How do we implement an IS-A Relationship?

Question

We implement an One-to-Many relationship by adding one Table's PK, as FK to the other Table. We implement a Many-to-Many relationship by adding 2 Table's PKs to a third Table.

How do we implement an IS-A Relationship ?

The Entities are TECHNICIAN and ADMINISTRATIVE which both are EMPLOYEE. I could just use an extra field in the Table EMPLOYEE(id, name, surname, role, ...AdminFields..., ...TechFields...)

but i would like to explore the IS-A option.

EDIT: I did as Donnie suggested, but without the role field.

Answer 1

It depends if you are building a mono-hierarchy or poly-hierarchy. This is a hard coded design, which I believe is what you wanted.

For mono (child table has one parent table), where child IS-A parent, the FK and PK is the same in the child table, and that key is also the PK in the parent table.

For poly (child table has multiple parent tables), where child IS-A parent-1 and child IS-A parent-2, you'll have a composite key (meaning multiple primary keys to make table record unique), where the rule is the same as a mono-hierarchy for each key.

Answer 2

The IS-A relationship is also known as the gen-spec design pattern. Gen-spec is short for "generalization specialization".

Relational modeling of gen-spec is different from object modeling of gen-spec because the relational model doesn't have inheritance built in.

Here is a good article that shows how to implement gen-spec as a collection of tables.

http://www.javaguicodexample.com/erdrelationalmodelnotes1.html

Pay particular attention to the way primary keys are set up in the specialized tables. That's what makes using these tables so easy.

You can find lots of other articles by googlin "generalization specialization relational modeling".