edited by
18,740 views
59 votes
59 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

  1. $2$
  2. $3$
  3. $5$
  4. $4$
edited by

5 Answers

Best answer
115 votes
115 votes

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. 

Reference: https://www.cs.uct.ac.za/mit_notes/database/htmls/chp06.html#relationship-participation-condition-membership-class

edited by
20 votes
20 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.

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

Answer:

Related questions

38 votes
38 votes
3 answers
2
Ishrat Jahan asked Nov 2, 2014
10,851 views
Consider two tables in a relational database with columns and rows as follows:$$\overset{\text{Table: Student}}{\begin{array}{|c|c|c|} \hline \textbf {Roll_no} & \textbf{...
43 votes
43 votes
6 answers
3
Ishrat Jahan asked Nov 2, 2014
12,131 views
Consider the following schedule $S$ of transactions $T1$ and $T2:$$${\begin{array}{l|l}\textbf{T1}& \textbf{T2} \\\hline\text{Read(A)} \\\text{A = A – 10}\\& \text...
43 votes
43 votes
5 answers
4
Ishrat Jahan asked Nov 2, 2014
10,532 views
A table T1 in a relational database has the following rows and columns: $$\begin{array}{|c|c|c|} \hline \text {Roll no. } & \text {Marks} \\\hline 1& 10 \\\hline 2 & 20 ...