relational-databasedatabase-normalizationfunctional-dependenciesbcnf

Convert relation to BCNF


I am given the relation R = {C, SN, OD, CH, CL, I, S, Y, D, RM, NS}.

The following functional dependencies hold:

{C} -> {OD, CH, CL}

{C, SN, S, Y} -> {D, RM, NS, I}

{RM, D, S, Y} -> {I, C, SN}

I need to convert this to BCNF.

I split this into 2 sub relations R1 = {C,OD,CH,CL} and R2={C,S,Y,D,RM,SN,I,NS}

Now I can see that R1 is in BCNF but I'm not sure about R2. This comes from the idea that {C, SN, S, Y} -> {D, RM, NS, I} so it seems like some non key attributes are determining part of the key. But the non-key attributes also need S,Y which are key attributes so I'm not sure if BCNF rule holds.

So is R2 in BCNF?


Solution

  • Assuming that the functional dependencies that you have given are a cover of all the functional dependencies of R, the candidate keys of the relation are {C, S, SN, Y} and {D, RM, S, Y}. This can be checked by computing the closure of both the sets of attributes, which contains all the attributes of R, while the closure obtained by removing any attribute from them does not contain all the attributes.

    Your decomposition is in BCNF, and R2 is in BCNF. In fact, a cover of the dependencies of R2 is:

    {D, RM, S, Y} -> {C, I, SN}
    {C, S, SN, Y} -> {D, NS, RM}
    

    and we can see that in both of them the determinant is a candidate key.