Consider the following entity relationship diagram (ERD), where two entities…
2004
Consider the following entity relationship diagram (ERD), where two entities E1 and E2 have a relation R of cardinality 1 : m.
The attributes of E1 are A11, A12 and A13 where A11 is the key attribute. The attributes of E2 are A21, A22 and A23 where A21 is the key attribute and A23 is a multi-valued attribute. Relation R does not have any attribute. A relational database containing minimum number of tables with each table satisfying the requirements of the third normal form (3NF) is designed from the above ERD. The number of tables in the database is
- A.
2
- B.
3
- C.
5
- D.
4
Attempted by 414 students.
Show answer & explanation
Correct answer: B
Answer: 3 tables.
Key idea: For a 1:m relationship with no attributes, do not create a separate relation table; place a foreign key on the "many" side. A multi-valued attribute requires its own table.
Table 1: E1 with columns (A11 PK, A12, A13).
Table 2: E2 with columns (A21 PK, A22, A11 as FK referencing E1.A11) to represent the 1:m relationship.
Table 3: E2_A23 to represent the multi-valued attribute A23. Columns can be (A21 FK referencing E2.A21, A23). Primary key can be (A21, A23) or an added surrogate key as needed.
Why not 2: Two tables would fail to represent multiple A23 values per E2 instance.
Why not 4 or 5: Those counts assume unnecessary extra tables (for example, creating a separate relation table for the 1:m relationship). Since the relationship has no attributes and is 1:m, the foreign key on the many side suffices, so no separate relation table is needed.
A video solution is available for this question — log in and enroll to watch it.