Question

Principles of Information and Data Management problem:

3. Given the following schema of a relation R(A,B,C,D,E) with the following functional depen- dencies: AB → C, C → D, D → B. D → E. and the following data: l B | C | D | E 112 3 2 1 23 1 2 13 5 25 22 3 1 | 2 237 25 3 11 3 2 3 2 4| 2 3 |352 5 (a) Decompose R into tables in BCNF b) Project the data of the original table into the new tables. (c) Join the data of the new tables to obtain one table and compare it to the original data.

Answer 1

First Let's find the candidate keys:-

clearly,since A can't be determined it must be a part of candidate key.

(AB)⁺ = ABCDE

since,D determines B so,(AD) is also candidate key.

candidate keys = {AB , AD}

a).Definition of BCNF:-

A relation is said to be in BCNF if all the functional dependencies have its left side as super key.

clearly,Functional dependencies like C->D and D->BE doesn't satisfies the above definition.

so,to make Relation R BCNF we need to decompose let's say after decomposition R becomes R1,R2 and R3.

R1(A,B,C)

AB->C

R2(C,D)

C->D

R3(D,B,E)

D->BE

There is foreign key from R1's C to R2's C ad R2's D to R3's D.

Now all the functional dependencies have its left side as key.so,above decompostion is in BCNF.

b).Projecting data :-

R1(A,B,C)

A	B	C
1	1	2
1	2	3
1	3	5
2	1	2
2	2	3
2	3	7
3	1	1
3	2	4
3	3	5

R2(C,D)

C	D
2	3
3	1
5	2
7	2
1	3
4	1

R3(D,B,E)

D	B	E
3	1	2
1	2	2
2	3	5

c).First we will join R2 and R3 then we will get

R23(B,C,D,E)

B	C	D	E
1	2	3	2
1	1	3	2
2	3	1	2
2	4	1	2
3	5	2	5
3	7	2	5

Now,joining R1 and R23 we get

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

A	B	C	D	E
1	1	2	3	2
2	1	2	3	2
3	1	1	3	2
1	2	3	1	2
2	2	3	1	2
3	2	4	1	2
1	3	5	2	5
3	3	5	2	5
2	3	7	2	5

Clearly,R123 is exactly same as R.