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

GATE 2004 · Databases · ER Diagram · medium

Answer: B. 3 — The minimum number of 3NF tables is 3: one for E1, one for E2 (with R's FK embedded), and one for the multi-valued attribute A23.

  1. Map entity E1 to a table: E1 has attributes A11 (key), A12, A13 — all single-valued. Table 1: E1(A11, A12, A13) Primary key: A11 This table is trivially in 3NF (no non-prime attribute transitively depends on A11).
  2. Map entity E2 and handle 1:m relationship R: E2 has attributes A21 (key), A22, and A23 (multi-valued). We exclude A23 for now. For the 1:m relationship R (no attributes), we merge it into E2's table by adding A11 as a foreign key. Table 2: E2(A21, A22, A11) Primary key: A21; Foreign key: A11 references E1(A11) This table is in 3NF: A22 and A11 depend directly on key A21, no transitive dependency.
  3. Handle multi-valued attribute A23: A23 is multi-valued, so it cannot be stored directly in E2's table. Table 3: E2_A23(A21, A23) Primary key: (A21, A23) — composite key A21 is a foreign key referencing E2(A21). This table is in 3NF: no non-prime attribute (none exist here) transitively depends on the key.
  4. Count total tables: Table 1: E1(A11, A12, A13) Table 2: E2(A21, A22, A11) Table 3: E2_A23(A21, A23) Total = 3 tables All three tables are in 3NF. No further decomposition needed.