Database redesign and reverse engineering: does the data model produced from reverse engineering exclude associative (intersection) tables, or should those many-to-many bridge entities be captured in the model as well?

Introduction / Context:
This statement tests your understanding of reverse engineering in database design. Reverse engineering converts an existing schema (tables, keys, constraints) back into a conceptual or logical data model that explains entities and relationships, including many-to-many associations that are commonly implemented as associative (intersection) tables.

Given Data / Assumptions:

• You start from an existing relational schema.
• Intersection tables are present to implement many-to-many relationships (e.g., student_course).
• The goal is to recover a platform-neutral model (entities, attributes, and relationships).

Concept / Approach:
In reverse engineering, designers analyze primary keys, foreign keys, and naming conventions to infer which tables represent core entities and which are associative entities that bridge two parents. These associative tables are not discarded; rather, they are recognized and mapped to the correct many-to-many relationship (optionally kept as an explicit associative entity if it carries attributes like enrollment_date, role, or status).

Step-by-Step Solution:
Identify tables with a composite primary key made of two foreign keys to separate parent tables.Classify such tables as associative (intersection) tables.In the data model, represent the many-to-many association and keep the associative entity if it has attributes beyond the keys.Confirm that the reverse-engineered model does include these artifacts; it does not omit them.

Verification / Alternative check:
Run a schema mining tool that detects FK-to-PK patterns; it will flag intersection tables as relationship carriers between two entities.

Why Other Options Are Wrong:
Claiming the model omits intersection tables is incorrect; they are central to representing many-to-many relationships. DBMS licensing (open source vs. commercial) is irrelevant.

Common Pitfalls:
Confusing “data” with “structure.” Reverse engineering focuses on schema structure (tables/keys), not necessarily on exporting all row data. Another pitfall is treating every small table as lookup rather than recognizing an associative role.

Final Answer:
Incorrect — a proper reverse-engineered data model includes the associative (intersection) tables/relationships.