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

Instead of deriving the relations one by one, count by category: how many strong entities are there, how many multi-valued attributes, and how many many-to-many relationships, since each of these always needs a dedicated relation of its own under the standard mapping rules.

1. Strong entities: E1 and E2. Both are regular strong entities with their own primary keys (A11 and A21). Each strong entity always becomes exactly one relation on its own, contributing 2 relations so far: E1(A11, A12, A13) and E2(A21, A23), since A22 does not stay with E2.
2. Multi-valued attributes: just A22. A multi-valued attribute always gets pulled out into its own relation keyed on (owner's primary key, the attribute itself), because a relational column can only hold one atomic value per row. This adds 1 relation: A22_table(A21, A22). Total so far: 3.
3. Many-to-many relationships: just R12. A relationship with M:N cardinality can never be folded into either side's entity table, since that side would then need to store a variable number of foreign keys. It always becomes its own relation holding the primary keys of the two entities it connects, regardless of whether participation is total or partial. This adds 1 more relation: R12(A11, A21). Total: 4.

Total participation would only save a relation if the relationship were one-to-many, since then the "many" side's table could absorb the foreign key of the "one" side. R12 is many-to-many, so that shortcut does not apply, and it must stay as its own bridge table.

Adding up: 2 entity relations + 1 multi-valued attribute relation + 1 relationship relation gives 4 relations in total, and since there is no other functional dependency, each is already in 3NF without further splitting.

$$\boxed{4}$$