ME Test Series

Question

Minimum relations required which satisfies the 2^nd normal from is _____

Comments

4.....

---

@Priyanka Agarwal please explain

Answer 1

it is many to many relationship

so min 3 tables are required

E1(A,B,C).   Key:AC

E2(DEF)      Key:D

R(AD) key:AD

here A in R is a foreign key refering to A but in A key is AC so here is a problem due to multivalued attrbute in   E1 so we need to decompose E1

now E1(AB)    Key A

E2((DEF)  Key D

R(AD) key AD

And E(AC)   Key AC

so total 4 tables are required

Comments

@nishant_magarde @Priyanka Agarwal $R$ can be merged with any any of $E_{1}$ or $E_{2}$ as there is total participation

---

yes only 3 tables are required

(AB)

(AC)

(DEF)

---

4 is correct. you cannot merge table AD on either side whether it's total participation on both sides or not.
They asked for 2NF not 1NF.

For 1NF it's 2 table only, 1 for multivalued attribute and 1 for R mergin with E1 and E2 in 1 table.

Answer 2

C is a multi-valued attribute, so 1 separate table[A,C] is needed for that.

Then, we have total participation of E1 and E2 on R, also, R is many to many, now one table is needed[A,B,D,E,F].

IN TOTAL, 2 TABLES ARE NEEDED.

Comments

 Please confirm the answer.

some are saying 3 or 4. I am confused. Whether I am correct or not. 

---

@nishant_magarde in your table [A,B,D,E,F] the primary key is $AD$ and there is a partial dependency $A \rightarrow B$. So, it is not in $2\,NF$ the answer should be $3$ as we have to split this table [A,B,D,E,F] into $2$ tables.

---

Got it.

So, what are the three tables.

one is AC

And, others are....?

---

3 must be the answer. The relationship can be merged to any side forming 1 table.

The another entity will have separate table.

And multi-valued attribute will form one table.

Answer 3

4 is correct !

C is a multivalued attribute hence A->C is one relation

A->B is another

And D ->EF

AD for relation R