Creating Software with Modern Diagramming Techniques: is ERD correct? (page 89)


There doesn’t seem to be a difference in the notion of independence between GENRE ->> TITLE_GENRE <<-- TITLE, and ACTOR -->> TITLE_ACTOR <<-- TITLE. Eliminating the title does not eliminate the actor nor the genre, and likewise for eliminating the actor or genre does not eliminate the title. TITLE_GENRE does’t explicitly include the primary key designation the way the TITLE_ACTOR model does. This seems to fly in the face of earlier statements defining the independence of relationships based on primary key.emphasised text

@raels Thank you for reporting, I think this section needs a little bit of a clean up, and it doesn’t help the diagram is slightly wrong for TITLE / TITLE_GENRE / GENRE (it should be both PK/FK). The ACTOR / TITLE_ACTOR / TITLE one is correct though with its keys, so a mistake on my part to not make it the same on both in terms of keys!

I’ll try to clarify the wording though, as it’s not about eliminating so much, it’s about “uniquely identifying” (in the case of identifying) or not. Out of curiosity, did the eliminating come from this chapter, and if so what part? As the domain modelling chapter touches on this with regards to composite vs aggregate, but it’s quite different in an ERD and identifying / non-identifying doesn’t really map to composite and aggregate, so was just curious if perhaps that lead to some of the confusion too.

So if we take ACTOR / TITLE_ACTOR, it’s identifying because the TITLE_ACTOR can’t be identified without ACTOR. The keys are a good way to decide too, in that title_id is in the primary key for TITLE_ACTOR.

Then for non-identifying, it means they are linked, but the relationship is just a foreign key, and the parent entity’s primary key isn’t used in the child entity. We can see this in TITLE and REVIEW, where TITLE has an ID, and so does REVIEW, and they are both primary keys. However, REVIEW is linked to TITLE by a foreign key, but it’s non-identifying because TITLE can be identified without REVIEW and vice-versa (because a review might be against a title, but it might be against an episode or season, and then title_id would be NULL). Whereas in the case of TITLE and TITLE_ACTOR, we can’t identify the TITLE_ACTOR without TITLE.

To take it one step further and explain it more, if we said a REVIEW was only against a TITLE, and we changed REVIEW to have a composite primary key of review_id & title_id, then it becomes identifying.

Does that make it a little clearer? I will try to make it clearer in the chapter too, as I did go back and forth on a few different ways and wordings for this part of the chapter, so an area for improvement I think, so thank you again for highlighting!