Self - Doubt

Question

Consider the relation R(ABCDE) with the FD set F={A-->E,BC-->D,E-->CD} . find the 3NF decomposition .

Comments

@Manisha Jaishwal What do previous year questions on database normalization say?

---

@Arjun

The relation given is R(ABCDE)

The FD set given F={A->E,BC->D,E->CD}

The Candidate key is =AB

The algorithm to decomposed a relation to 3NF:-

1. Eliminate redundant FD’s resulting in a canonical cover $F_{c}$ of $F$.
2. Create a relation $R_{i}$=$XY$ for each FD $X→ Y$ in $F_{c}$.
3. If the key K of $R$ does does not occurs in any relation $R_{i}$,Create one more relation $R_{i}$=$K$.

If we try to compute the minimal cover for our given FD set we found out that $F$=$F_{c}$. 

So from step 2 of the algorithm the decomposed relations are :- AE,BCD,ECD

Now in step 3 , the key $K$ of R does not occur in any relations  $R_{i}$ so we have to add the key AB as another relation.

so we have minimum $4$ decomposed relation $AB,AE,BCD,ECD$.

---

BCNF is more stricter than 3NF but may not be dependency preserving which implies we are not always go for dependency preserving . so our main focus should be lossless join to reduce the redundancy of data .But in practice i believe we prefer 3NF which is easy to achieve.

 

The algorithm link := https://faculty.ksu.edu.sa/sites/default/files/E-%20Decomposition.pdf

---

Thanks 🙏