Entity–Relationship (ER) to Relational mapping: When converting a many-to-many unary (recursive) relationship on a single entity into relations, how many separate relations do you typically end up with in the relational schema?

Introduction / Context:During logical database design, we translate Entity–Relationship (ER) models into relational tables. A common point of confusion is how to map a many-to-many unary (recursive) relationship, where instances of the same entity relate to each other (for example, Employee mentors Employee). Understanding the correct number of relations ensures a clean, normalized schema.

Given Data / Assumptions:

• The ER model has one entity type participating twice in a many-to-many relationship with itself.
• We want a normalized relational mapping.
• Primary keys and foreign keys are used to enforce relationships.

Concept / Approach:

In relational mapping, a many-to-many relationship is represented by an associative (junction) relation. For a unary many-to-many, we still need a separate relation to store the pairs of related instances, but both foreign keys point to the same parent entity’s primary key.

Step-by-Step Solution:

Verification / Alternative check:

If you attempt to store the relationship inside the entity table itself, you cannot represent many-to-many pairs without repeating groups or violating normalization. The associative table is necessary and sufficient.

Why Other Options Are Wrong:

One relation: cannot represent many-to-many pairs cleanly.

Three or four relations: unnecessary for a single unary relationship; two suffice.

Common Pitfalls:

Forgetting to add a composite key or unique constraint on (mentor_id, mentee_id) to prevent duplicates; confusing unary with binary relationships.

Final Answer:

Two relations are created.