Question

1. Given the schema R(A,B,C,D,E) with the functional dependencies

F = { A → C,D D B, E B, C + D, E E → B,C } Is this schema in BCNF? If it is, prove it. If not, find a BCNF decomposition and then prove that the decomposition is in BCNF. You must prove each step carefully.

Answer 1

For R(A,B,C,D,E)

From functional dependencies given we have following SuperKeys:

ABCDE (All attributes is always a super key)

AD ( from A->CD , D->BE)

ABC (from A->CD , BC->DE)

AE (from A->CD , E->BC)

Note :- One thing is to observe here is that A never appears on the right side of functional dependencies , so every key must include A

Now we will find candidate keys taking Superkeys only which have A:-

ABC can be trimmed down to A as we get CD from A (A->CD) and from D we get BE (D->BE)

AD can be trimmed down to A as we can get D from A (as A->CD) and from D we get BE (D->BE)

AE can be trimmed down to A as we can get D from A (as A->CD) and from D we get BE (D->BE)

Similarly ABCDE can be trimmed down to A using above methods.

So candidate key is : A

Since all left hand side of functional dependencies does not include Candidate Key . Therefore , the schema is not in BCNF.

Now we will find another BCNF decomposition which is in BCNF :-

F = { AD->BCE }

Since AD is the candidate key in this case.

And AD is the left side of functional dependency here which includes AD (the candidate key)

So the above functional dependency is in BCNF.