MadeEasy Test Series: Databases - Er Diagram

Question

 

 

 

 

 

Comments

Is it 2?What's the answer given??

---

here both side have total participation(given in the question) 

case 1 .   lets suppose it is one to one or one to many (which is not given in the question ) 

we make a table (A,B,C,D,E,F) and ACD form a key but it have two partial dependency 

so to make 2NF we make table 

lets us try to make two table

(A, B, D, E, F) Here PK is AD  and (A,C). 

but table 1 also  have partial dependency i.e. A-->B  so have to make more table 

(A,B) (D,E,F,A) (A,C) in this no table have partial dependency and in  table (D,E,F,A) A is forgien key

case 2    many to many relation 

in this case we have to make 4 table 

( A,B)(A,C)(A,D)(D,E,F) 

Answer 1

The question is given in made easy test series. They have given the correct answer as 4. I am not convinced with the explanation. As per my understanding, I got the answer as 2.
Your review is needed here.

Comments

As per my understanding as there's total participation so 1 table is required but as answer is asked in 2nf so we need to create seperate table for multivalued attribute therefore 1 more table for that.Total=2

Please correct if I'm wrong

---

What are tables you are getting?

---

If we merge this in one table considering m:n total participation on both sides  there will be partial dependencies ...we can't merge

---

Ans is 4 only ( A,B)(A,C)(A,D)(D,E,F) will be the tables .

---

@ monika7537,

Why is (A,D) table required?
The entities are having total participation so one table is sufficient. Hence merging both E1 and E2 table 

E1E2 (A, B, D, E, F) Here PK is AD 
and (A,C).

---

For 1 NF, it is like (A,C,D,B,E,F) --- A,C,D combinenely form the key

It is in 1NF.... BUT it have partial dependencies

Therefore, (A,B) and (D,E,F) And (A,C,D)

is it (A,C,D) is lead to PFD? NO

TOTALLY 3 TABLES REQUIRED.

---

But by this approach we can't guarantee the loss-less property,

So go with (A,B) and (D,E,F),(A,D) And (A,C) -----> minimum 4 tables required.

---

@shaik masthan

you said  your above comment do not guarantee lossless property how?

---

actually in the 1 NF, itself that process is going to fail loss-less property !

from the E-R, R₁(A,B,C), R₂(A,D) and R₃(D,E,F)  but in R1 C is multi-valued

For removing this, we make AC as a key in R₁.

given relationship in ER is M:N ===> we make AD as a key in R₂.

as normally D is the key in R₃.

the tables look like R₁(A,C,B), R₂(A,D) and R₃(D,E,F)

by observing R2 and R3, common attribute is Key ===> it can combine R₂₃(A,D,E,F)

∴ in 1 NF ===> R₁(A,C,B) and R₂₃(A,D,E,F) ===> 2 tables required

 

R₁(A,C,B) have the FD A --> B

R₂₃(A,D,E,F) have the FD D --> E,D -->F

For satisfying 2 NF, we don't have PFD

R₁(A,C,B) divided as R₁₁(A,B),R₁₂(A,C)

R₂(A,D,E,F) divided as R₂₁(D,E,F),R₂₂(A,D)

∴ in 2 NF ===> R₁₁(A,B),R₁₂(A,C) and R₂₁(D,E,F),R₂₂(A,D) ===> 4 tables required