MadeEasy Test Series 2018: Databases - Er Diagram

Question

$E_{11}\left (\underline AB \right )$ and $E_{12}\left (\underline {AC} \right )$ is obvious separation but can we not merge like this to satisfy 2NF $E_{11}R\left (\underline ABD \right )$

As $A$ is a primary key, apart from $A\rightarrow B$, $A\rightarrow D$ also satisfies because of full participation. What are the actual tables?

Comments

Though it is full participation but it is M:N and hence we can’t merge them.

hence relations will be

E11, E12, R(AD), E21(DEF)

---

When do we merge then?

---

Ooh.. got it. Even though it's full participation the M:N means that in A an element can have more than one relations to some element in B.

Answer 1

for relation M:N we need to one  separate table . and one for multivalued then total no of table is required is 4.

Comments

I got a doubt here Sir,

Since its total participation but its many to many Here one instance of E1 can relate to any number of instance with E2 but if we take (A,D) as the key of merged relation it wont be a problem at all isn't it ? and there also be no NULL values then why we cant merge entire thing into 1 table and for multivalued we can have a seperate table. So total table should be 2.

        
Please Correct me Sir Here :(

---

Yes, I think it can be merged. The final relations will be R1(A, C) and R2(A, B, D, E, F). 

In case that the M: N relationships have total participation on both sides, the relationship along with the entities on both sides can be merged into a single table.

( I don't have any book references right now other than an answer by @Habibkhan here. Also, we need to note that the standard books like Korth and Navathe have algorithms for conversion from ER model into "standard" relational models. They do not deal with the "optimized" case of min. no. of tables needed.)