What is the right way to use Associative Entity?

Question

This is the description:

Draw an Entity-Relationship diagram for Poke-Hospital which provides medical service to pokemon.

Each pokemon has an appointment with one of the nurse Joys. In addition to recording the name, type and trainer of each pokemon, the system needs to keep track of the multiple types of sickness being diagnosed for the pokemon. During an appointment, the nurse will always prescribe medicine. It is required to record the date, time and dosage of the medicine. A pokemon may need to take more than one medicine at a time. Each medicine is stored with its name, brand and cost of purchase. There is no restriction on the amount of medicine to be prescribed by any nurse.

Within an appointment, a pokemon may need to undergo procedures such as a surgery and/or diagnosis. Each procedure requires different type of rooms and a list of equipment. The date, time and the actual room of the procedure need to be recorded.

A procedure may be performed by more than one nurse. A nurse is involved in the procedure based on the training skills that she has completed. Not all nurses are qualified to perform procedures.

Name, pager number as well as office number for each nurse most be known. Your diagram should show the entities, relationships and their attributes, and the cardinality of any relationships. Mark the best primary key for each entity by underlining it.

This is my solution:

Here are my questions:

1. Should I use Have Appointment as associative entity?

2. Should I remove 2 relationships Undergo and Prescribe and connect 2 entities Procedure and Appointment Medicine directly to Have Appointment associative entity? Will the ERD still right then?

3. If it's wrong, what about the same as question 2 and I turn the Have Appointment associative entity into a relationship?

I feel really confused about the difference between using associative entity with a relationship (like in this post Enrollment with Teach and Teacher: When to use Associative entities?) and using ternary relationship (connect Teacher directly to Enrollment relationship instead of changing Enrollment to an associative entity and have the Teach relationship).

Answer 1

1. Should I use Have Appointment as associative entity?

No, I believe it should be a regular entity set. You gave it its own identity - the ID primary key - which I agree with, but that should've corresponded with a change in element type. Associative entity sets (AES) are relationships first, which means they're identified by the (keys of the) entity sets that they relate.

This is a topic that's widely confused, since AES in the entity-relationship model are different than in the network data model. The latter is intuitively more familiar to developers, since it's essentially a model based on records and pointers, but since it only supports directed binary relationships, anything more complicated - many-to-many relationships as well as ternary and higher relationships - need to be represented as AES. In this model, AES are identified by a surrogate ID, since composite keys generally aren't supported either.

The entity-relationship model supports n-ary relationships and composite keys, and so doesn't need AES nearly as frequently. One situation that can't be represented by regular entity sets and n-ary relationships is when a relationship needs to be the subject of a further relationship.

For example, let's look at the relationship between Procedure and Nurse to represent the nurses involved in a procedure.

I prefer the look-across convention for cardinality indicators - a nurse can perform 0 or more procedures, while a procedure requires 1 or more nurses. Anyway, the relationship Perform here is identified by the composite primary key (ProcedureID, NurseID).

Now, if we wanted to track the equipment used by each nurse in the performance of the procedure, we might think a simple ternary relationship would do the trick:

but that relationship would be identified by (ProcedureID, NurseID, EquipmentID), preventing us from recording nurses that assisted in the procedure without using any equipment. What we need is two separate relationships:

(ProcedureID, NurseID)
((ProcedureID, NurseID), EquipmentID)

with an FK constraint from the second to the first to prevent nurses not assisting in the procedure from handling the equipment.

Back to Have Appointment - it's not a relationship between pokemon and nurses (a pokemon can see the same nurse multiple times), it's an event that involves pokemon, nurses, procedures and medicine. It's best handled as a regular entity set with relationships to the other four. As for identity, I imagine a pokemon or nurse can only have one appointment at a time, so we could choose (PokemonID, DateTime) or (NurseID, DateTime) as a natural key. However, in practice we usually identify events by a surrogate ID since events span an interval which most DBMSs can't handle effectively as a primary key.

2. Should I remove 2 relationships Undergo and Prescribe and connect 2 entities Procedure and Appointment Medicine directly to Have Appointment associative entity? Will the ERD still right then?

No, I think you should add relationships between Pokemon and Have Appointment, and between Nurse and Have Appointment, after converting the AES to a regular entity set.

3. If it's wrong, what about the same as question 2 and I turn the Have Appointment associative entity into a relationship?

Answered above.