14,156 views

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

1. $2$
2. $3$
3. $5$
4. $4$

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.

by

Does relational database by itself does not put the constraint of atomic attributes ?

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

@ sir,

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

@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.

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.

I am getting min 2 tables
I don't understand why it is given as E21(A11,A21,A22) ? Here what is the purpose of including the primary attribute of 1st entity.
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.
@amkrj,

For multivalued attributes there will be an additional table like MT(A21, A23). Other 2 tables for each entity and relation is maintained by adding E1's PK as FK in E2's table.

Hence 3 tables.

$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$

hope you will get an answer from here:

http://db.grussell.org/section006.html