MadeEasy Test Series (Number of tables required)

Question

Answer given is 3 tables which are $R_1(A, B, C, H)$ , $R_2(G, H)$ , $R_3(D, F, A)$

My doubt is if we join $E_1$ and $E_2$ making a new relation $R_{13}(A,G,B,C,H)$ with AG as key

and $R_2(A,D,F)$. So this can be done with only 2 tables as $E_1$ and $E_2$ are totally participating hence no loss of any data and relation $R_2$ with having AD as key can identify $E_2$.

Comments

Why can't we merge it in single table as (ADCE) AD as a key ?

---

actually that is a rule... i am not able to give supporting links, if i found then i will post here

---

@jatin khachane 1

yes, as you said it can combine if E₁ is total participation, otherwise we can't combine.

 

actually, weak entities can not merged into strong entities.

In reality we keep tables atleast in 3NF, so this statement is true, but in the question it is in 1NF ( we can consider it as by default, due to minimum tables occur in this form only ), therefore above statement is not valid.

Answer 1

From Database Design by Korth

So as per standard books, we need separate tables from E1 and E2.

What is the reason that this is a good design?

For example, if we have dependent as a weak entity and employee as a strong entity. It is possible that for the most employee there is no dependent or say that information is unavailable etc. If we merge the table then we will have many null entries for the dependent column. Also for a particular employee, there may be many dependents for a particular employee, (remember there is no primary key in it before we add the primary key of employee table to it), so if we merge this will become a multivalued attribute which isn't allowed in the relational table. (anyway we will need a separate table for multivalued attribute even if we merge, so merging serves no purpose.)

Comments

yes here u can see why 2 tables

https://gateoverflow.in/77410/doubt

https://gateoverflow.in/74563/made-easy-test-series?show=74563#q74563

https://www.gateoverflow.in/218954/what-an-interesting-dbms-question?show=234701

---

smsubham $R_2 (\underline{A,D},F)$ A is partially participating but F is fully participating. This table will not have any null entries.