If yes, then why do we need to check for 1NF because that would not be possible then.

Dark Mode

14,156 views

55 votes

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

- $2$
- $3$
- $5$
- $4$

103 votes

Best answer

We need just two tables for $1NF$.

$\text{T1: {A11, A12, A13}}$

$\text{T2: {A21, A22, A23, A11}}$

$\text{A23}$ being multi-valued, $\text{A21, A23}$ becomes the key for $T2$ as we need to repeat multiple values corresponding to the multi-valued attribute to make it $1NF$. But, this causes partial FD $A21 \to A22$ and makes the table not in $2NF$. In order to make the table in $2NF$, we have to create a separate table for multi-valued attribute. Then we get

$T1: \{A11, A12, A13\} -$ key is $A11$

$T2: \{A21, A22, A11\} -$ key is $A21$

$T3: \{A21, A23\} -$ key is $\{A21, A23\}$

Here, all determinants of all FDs are keys and hence the relation is in $\text{BCNF}$ and so $3NF$ also. So, we need minimum $3$ tables.

Correct Answer: $B$

PS: Even if “3NF” isn't explicitly mentioned in question we should assume it and we cannot add NULL entries during ER to Relational conversion.

0

@Arjun sir,

Can’t the relation like R1 (A11, A12, A13, A21) and R2 (A21, A22, A23) possible? I think it also satisfies 3NF

0

@rish1602 If you consider this, then A21 will be the Primary Key for R1 relation due to 1:M mapping ( as A11 can repeat ) and thus Partial Participation at E1 side will not be possible.

0

19 votes

one table for E1 , two tables for E2(A21,A22 and A21,A23) bcoz we need to make a seperate table for multi-valued attribute to satisfy minimum 1NF condition that requires atomic attributes. Now, Relation table can be merged with (A21,A22). So we have following tables.

E1(A11,A12,A13) E21(A11,A21,A22) and E22(A21,A23)

So ans is 3 tables in all.

1

Here the primary attribute of first entity is included in order to create a relationship between the two tables. To understand well you can take table E21 as Employee table where each employee can participate in only one relationship and table E1 as Department table where each department can participate in multiple relationships.

2

11 votes

$E1(\underline{A11},A12,A13)$

$\underline{A11}\rightarrow A12\ A13$

$CK:A11$

$3NF:\checkmark$

$E2R(\underline{A21},A22,A23,A11) $

$A21\rightarrow\rightarrow A23=Allowed\ in\ 2NF/3NF/BCNF$

$A21\rightarrow A22\ A11$

$CK:\underline{A21A23}$

$\underbrace{A21\rightarrow A22\ A11}=partial\ dependency$

Therefore Decompose into tables

$(A21,A23)$

$A21\rightarrow\rightarrow A23=Trivial\ MVD$

$CK:\underline{A21A23}$

$(A21,A22,A11)$

$A21\rightarrow A22\ A11$

$CK:A21$

$3NF:\checkmark$