NIELIT 2022 April Scientist B | Section B | Question: 49

Question

Let $\text{R (A, B, C, D)}$ be a relational schema with the following functional dependencies: 

$\text{A} \rightarrow \text{B,  B} \rightarrow \text{C}$ 

$\text{C} \rightarrow \text{D}$ and $\text{D} \rightarrow \text{B}$

The decomposition of $\text{R}$ into

$\text{(A, B), (B, C), (B, D)}$

• A. gives a lossless join, and is dependency preserving
• B. gives a lossless join, but is not dependency preserving
• C. does not give a lossless join, but is dependency preserving
• D. does not give a lossless join and is not dependency preserving

Answer 1

Answer – A

In Relation R (A, B, C, D) –

1. Closure of A = { A, B, C, D }
2. Closure of B = { B, C, D }
3. Closure of C = { B, C, D }
4. Closure of D = { B, C, D }

Now, R is decomposed into – 

1. R1 (A, B) having FD { A → B }; A is the super key.
2. R2 (B, C) having FD’s { B → C, C → B }; B and C both are super keys.
3. R3 (B, D) having FD’s { B → D, D → B }; B and D both are super keys.

If the common attribute of 2 relation is a super key of any relation, then their join is lossless.

Here, B is common in all 3 relations and B is a super key of 2 relations among them. Thus, join is lossless.

Original relation R has 4 non-trivial functional dependencies.

1. A → B. This can again be obtained from FD of R1.
2. B → C. This can again be obtained from FD’s of R2.
3. C → D. This can again be obtained from FD’s of R2 and R3 ie C → B and B → D thus C → D.
4. D → B. This can again be obtained from FD’s of R3.

Thus, join is dependency preserving also.