I am trying to convert this relation into 3rd Normal Form and BCNF.
Given a relation R(A1, A2, A3, A4), with three FDs
A2, A3 → A4 ;
A3, A4 → A1;
A1, A2→ A3.
Provide the 3NF and BCNF form of the schema and explain why.
I remember doing this but am a but rusty.
Here is my start.
I split on the functional dependency A2, A3-> A4
Thus that creates:
R1(A2, A3, A4)
R2(A1, A2, A3)
Since there aren't any more functional or transitive dependencies, this should be in 3NF.
My questions are first if this approach is correct.
Second since both the keys (A2, A3) for R1 and (A1, A2) for R2 are both composite keys, wouldn't this be in BCNF as well?
I was wondering if this is true, and if not what would be the BCNF for this relation?
Please let me know if you need any further information.
Thank you for your help.
Given the fact that a schema is in 3NF if every determinant is a superkey or each determinate attribute is prime, the schema is already in 3NF. In fact, the only candidate keys of R are {A1, A2} and {A2, A3}. A2 must be in every key, since it does not appear in any right hand side of the dependencies. If you add to it A1 you can discover that you obtain a candidate key since A1, A2 determines all the attributes, and the same if you try to add A3 to A2. This is not true adding A4, A1, A2 and A3 are prime attributes and no functional dependency violates the 3NF.
Given the fact that a schema is in BCNF if every non-trivial dependency has a determinat which is a superkey, your schema is not in BCNF for the dependency A3, A4 → A1, since{A3, A4} is not a superkey.
Applying the “analysis” algorithm to produce a decomposed schema in BNCF, you can use the dependency A3, A4 → A1 to decompose in BCNF, and this produces two relations:
R1 (A1, A3, A4) (with candidate key {A3, A4})
R2 (A2, A3, A4) (with candidate key {A2, A3})
Both are in BCNF, so no further decomposition is necessary, but note that in this process the dependency A1 A2 → A3 is lost.