Search code examples
databaseerdvisual-paradigm

ERD Inheritance & Identifying Relationship simultaneously

Let's assume we have 2 ERD entities connected with 1 relationship. Visual Paradigm allows to mark such a relationship as "Subtype" and "Identifying" at the same time. And I just fail to imagine any case where such a combination could make sense.

Is that actually legal? If yes I would appreciate some example and a procedure of translating it to the relational model (what are the changes in comparison to only-"Subtype" or only-"Identifying" case?).

Solution

  • What Visual Paradigm calls an ERD is in fact a table diagram. Diagrams that don't use shapes for relationships don't support attributes and relationships on relationships, nor ternary and higher order relationships. If a diagramming notation doesn't support all the concepts of the Entity-Relationship model, we can't call its diagrams Entity-Relationship diagrams.

    From an ER point of view, all subtype relationships are identifying relationships, since the parent entity set's identity is a component of the child entity set's identity. However, all identifying relationships aren't subtype relationships. The difference between a subtype and a weak entity set is that the latter uses an additional weak key component to distinguish multiple children of a parent. Subtypes depend only on the supertype for identity and so each instance of the supertype can have only instance per subtype.

    For example, an entity set Person (identified by person_id) may have a subtype Employee (which is also identified by person_id). Compare this with an Invoice (identified by invoice_id) and LineItem (identified by invoice_id and line_number).

    Subtype and identifying ER diagram

    The ERD (conceptual model) translates into the following tables (physical model):

    Subtype and identifying table diagram