[Paul Gardner]

27/08/2021 at 4:43 pm

Now I’m confused over this topic.
The notes in the course spec. seem quite clear to start. “The mandatory side of any relationship is indicated by using a vertical line”. “The optional side of a relationship is indicated by using a bold circle.”

And the example, Customer -|—-O< Order, reads to me that Customer is mandatory, Order is optional.

Annotating (in brackets) the notes from the course spec. “Mandatory participation describes a relationship where at least one occurrence of an entity (Customer) must exist before any occurrences can be added to its associated entity (Order). The mandatory side (Customer) of any relationship is indicated by using a vertical line.”

“Optional participation describes a relationship between two entities where it is possible to add occurrences of one entity (Customer) without the need to have existing occurrences in the associated entity (Order). The optional side (Order) of a relationship is indicated by using a bold circle”.

However, the Travel Agency example that follows is described differently. “Resort has optional participation in this relationship” (even though the vertical bar is at the Resort end in the diagram). The written rational is based on the entity-occurrence diagram but it disagrees with the diagram and seems (to me) to be incorrect.

I think of it like this. If I want to create a new Resort and a new Hotel, which do I have to create first? (Resort). I can create a Resort without a Hotel but I can’t create a Hotel without an existing Resort. So, in the relationship, Hotel is optional and Resort is mandatory as the diagram suggests.

Either way the diagrams should correspond with the description that “The mandatory side of any relationship is indicated by using a vertical line”.

Am I missing something?

[mcgivernj]

27/08/2021 at 9:34 pm

Hi Paul. My head has spun many times around this, but I’m good to go now.
It’s all to do with how you read the diagram. Take the Resort – Hotel example.
Start at Resort and then jump to the optional participation at the other side of the relationship. A Resort is optional in the relationship with hotel. You can have a Resort but it does not have to be tied in with any hotel occurrence.
Then go the opposite way starting from the Hotel entity and read it by jumping to the mandatory at the opposite side of the relationship. A hotel is mandatory in the relationship with hotel in that you cannot have a hotel occurrence that exists without a connection to an existing Resort.
You alluded to it yourself in that it appears the opposite of what is presented in front of you in the diagram. And this is correct. All in the interpretation (or reading of the diagram).
With that thinking in place, it makes the course notes correct and the SQA paper correct also.
I agree with your way of thinking about how occurrences are formed.
Now ready to formulate my final version of notes for students. Unless someone comes back with something different, and back to the drawing board again!

[Paul Gardner]

30/08/2021 at 4:42 pm

The Scholar notes at

https://scholar.hw.ac.uk/courses/content/ahccmp/2/1/2/5/Database-design-and-development__Design__Entity~relationship-diagrams__Relationship-participation

cover relationship participation and contradict the written explanation of the Travel Agency scenario in the AH Course Spec.

I looked into how ER diagrams and relationships used to be described in earlier courses and think I see the source of confusion.

Alternative/earlier ER diagramming techniques showed optional/mandatory relationships that were described differently. The descriptions sometimes followed on from entity occurrence diagrams that concluded that, for example, if a Resort could occur without a corresponding Hotel, then Resort was optional. Similarly, if a Hotel could not occur without a corresponding Resort, then Hotel was mandatory.

A diagram for this might be show as

Resort – —–< Hotel

where the broken line shows that the relationship was optional at the Resort end (a Resort may have an associated Hotel) and mandatory at the Hotel end (a hotel must have an associated Resort).

Now, at AH, this relationship would be shown as

Resort -|—-O< Hotel

and the description/terminology used has changed direction. E.g. If a Resort could occur without a corresponding Hotel then Hotel’s participation in the relationship is optional. Similarly, if a Hotel could not occur without a corresponding Resort, then Resort’s participation in the relationship is mandatory.

The notes in the AH Course Spec introduce ‘relationship participation’ using the new diagram style and terminology but explain the Travel Agency example using the old. Also, the marking schemes for the specimen paper and the 2020 paper use the new diagram style but the older approach to determine which end is optional and which is mandatory.

I like the Scholar material and am (now) using it with my AH group this year.