Question

Consider an ER model with the entities E1(A11, A12, A13) and E2(A21, A22, A23), where A11, A12, A13 are the attributes of E1, and A21, A22, A23 are the attributes of E2. Let A22 be a multi-valued attribute. A11 and A21 are the primary keys of E1 and E2, respectively.

Let R12 be a many-to-many relationship between E1 and E2. Participation of both E1 and E2 in R12 is total.

The minimum number of relations required to convert the ER model into relational model (assuming there is no other functional dependency) where each relation is in third normal form (3NF) is _______. (Answer in integer)

Hint:

Count separately: one relation per strong entity, one relation for the multi-valued attribute, and one relation for the many-to-many relationship, since M:N relationships always need their own table regardless of total participation.

Answer 1

Correct Answer: 4

Step 1: Recall the standard ER-to-relational mapping rules.
Three rules matter here: every strong entity becomes its own relation, every multi-valued attribute needs its own separate relation because a single column cannot hold a set of values, and every many-to-many relationship needs its own separate relation, regardless of whether participation is total or partial, because it cannot be merged into either entity's table.

Step 2: Build the relation for E1.
E1 has attributes A11, A12, A13, with A11 as the primary key. None of these are multi-valued, so E1 converts directly into one relation: E1(A11, A12, A13). This is relation 1.

Step 3: Build the relation for E2, pulling out the multi-valued attribute.
E2 has attributes A21, A22, A23, with A21 as the primary key, and A22 is multi-valued. A multi-valued attribute cannot sit inside the main entity relation, since storing a set of values in one column would break first normal form. So it is removed from the main E2 relation and placed in a relation of its own that references the entity's key.
Main E2 relation: E2(A21, A23). This is relation 2.
Multi-valued attribute relation: A22_table(A21, A22), where A21 is a foreign key referencing E2, and the primary key of this table is the combination (A21, A22). This is relation 3.

Step 4: Build the relation for the many-to-many relationship R12.
Even though participation of both entities in R12 is total, a many-to-many relationship can never be absorbed into either participating entity's table, because doing so would force repeating groups of foreign keys inside that table. It always needs its own bridge relation carrying the primary keys of both entities: R12(A11, A21), with the pair (A11, A21) forming the primary key of this bridge table. This is relation 4.

Step 5: Check that no relation can be merged further.
E1 and E2 cannot merge (they are separate strong entities). The multi-valued attribute table cannot merge into E2 without violating 1NF. R12 cannot merge into E1 or E2 even though participation is total, because it is a many-to-many relationship, not one-to-many. So all four relations are required at minimum, and each is already in 3NF by the question's assumption.

Final Answer:
The minimum number of relations needed is 4. \[ \boxed{4} \]