1,808 views
0 votes
0 votes

Consider the following F.D.

ab -> cd  ;  e->c ;  b->ef

Which options show the relations normalized to 3NF of R(A,B,C,D,E,F)

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

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

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

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

Ans is C

2 Answers

2 votes
2 votes

See ab -> cd  ;  e->c ;  b->ef

It is not in 3NF. As it has partial dependency .

Here ab is the key of the relation

and  b->ef contains partial dependency here

So to make a table of 3NF we have to remove this partial dependency.

Again e->c is a   non key -> non key dependency , which is not allowed in 3NF

So, we cannot merge any of these two dependency in 1 table

So, table will be R(A,B,C,D) , R(C,E) , R(E,F,B)

i.e. Ans A) or C)

1 votes
1 votes

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

Given FDs are - AB - > CD,  E -> C, B -> EF.


Now check option A.

We are decomposing R into 3 relation R1(A, B, C, D), R2(C,E), and R3(E, F, B).

where DFs for R1 : AB -> CD

  and   DFs for R2 : E -> C

   and  DFs for R3 : B -> EF.

Here all FDs are preserved and all decomposition are lossless too( because common attribute between every decomposed table is a C.K. in one of the table. E.g. - common attribute between R2 and R3  is E which is a key in R2 table...similarly you can check for others). And FDs of all three decomposed relation contains super key in the left side of FDs. So all decomposed relation are in 3NF and even in BCNF.


Now check option B.

Here we are decomposing R into 3 relation R1(A, B, D), R2(E,C), and R3(E, F, B).

where DFs for R1 : AB -> D

  and   DFs for R2 : E -> C

   and  DFs for R3 : B -> EF.

Here it seems like AB -> CD is not preserved but we can get AB -> C also with the help of other FDs as  B -> EF and now from E -> C. So here also all the FDs are preserved and FDs of all three decomposed relation contains super key in the left side of FDs. So all decomposed relation are in 3NF and even in BCNF.


Now check option C.

Here we are decomposing R into 3 relation R1(A, B,C, D), R2(C,E), and R3( B,E,F).

Same as option A.


Now check option D.

Here we are decomposing R into 3 relation R1(A, B,C, D,E,F), R2(E,C), and R3(E, F, B).

where DFs for R1 : AB -> CD, E->C, B->EF

  and   DFs for R2 : E -> C

   and  DFs for R3 : B -> EF.

 

Now if we see relation R1 where C.K is B and its FDs are-  

AB -> CD  ( left side is a super key ..so no probem)

E -> C       ( neither left side contains super key nor right side contains prime attribute so it violates the 3NF rule )

B -> EF   ( left side is a super key ..so no probem ).


Ans - A, B, C

Related questions

0 votes
0 votes
1 answer
1
0 votes
0 votes
2 answers
2
Souvik33 asked Jan 14, 2023
506 views
A relation is in 3NF if every non-prime attribute of R is fully functionally reliant on every key of RTRUEFALSE
2 votes
2 votes
0 answers
3
h4kr asked Dec 15, 2022
329 views
Since the schema is already in 3NF we can keep the relations to 1, cant we? If you think the answer is different please explain how
0 votes
0 votes
0 answers
4
Varun Raj Akula asked Mar 12, 2022
382 views
Can anyone please explain about Statement 4 ? I know that transitive dependencies are allowed in 3NF as long as RHS is Prime Attribute. But, what is intransitive dependen...