Gate 2008 (Extension: convert into 3NF)

Question

Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional depen­dencies are known to hold: AB → CD, DE → P, C → E, P → C and B → G.

Convert the above schema in 3NF

Comments

@hs_yadav

upto 3NF decomposition, we can always get lossless and dependecy preserving decomposition, but for BCNF, it is always not possible to get lossless and dependecy preserving decomposition

---

@joshi

(A,B,C,D)   (B,G)    (C,E)      (D,E,P)  above decomposition is also in 3rd NF but not having Fds preserved...then is it would be correct answer according to question?

---

I also have this doubt ! How to make sure this decomposition is lossless?

Answer 1

This DATA is from GATE 2006

And

The minimal cover is { A,B-->C; A,B-->D; D,E-->P; C-->E; P-->C; B-->G }

We can decompose into 3NF Schemas in 5 relations as below:

• Relation 1 is C,E
• Relation 2 is P,C
• Relation 3 is B,G
• Relation 4 is A,B,C,D
• Relation 5 is D,E,P

Answer 2

5 table are required

Comments

@abhishekmehta4u , @gunjan79 , @joshi_nitish : Can you please confirm. How this is lossless. I have tried with combinations, but eventually giving lossy decomposition. It would be helpful for us if you provide a detailed solution. on lossless decompostion and no. of relations needed to be in 3NF.