GATE IT 2004 | Question: 73

Question

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$

Comments

And what will be the minimum number of tables required for this question, Will it be 2??

Answer 1

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

Comments

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

---

@Arjun sir, the given data doesn’t say anything about dependency between A12 and A13, which could lead to a transitive dependency and would fail the definition of 3NF. Sir, how can we conclude that determinants of all FDs are keys? Request yo to kindly explain. Thank you.

Answer 2

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.

 

Comments

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.

Answer 3

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

hope you will get an answer from here:

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