This is how we have to do it according to the slides:

Question 1 Given is the relational schema R= (A, B, C, D, E) and the set of functional dependencies F = {AB BC, C + A, ABC + D). Is R in BCNF? If not, give a lossless decomposition which is in BCNF. Explain your answer carefully. Testing for BCNF Simplified test: To check if a relation schema R is in BCNF, it suffices to check only the dependencies in the given set Ffor violation of BCNF, rather than checking all dependencies in Ft. If none of the dependencies in F causes a violation of BCNF, then none of the dependencies in F+ will cause a violation of BCNF either. However, using only F is incorrect when testing a relation in a decomposition of R Consider R = (A, B, C, D, E), with F={A B, BC D} - Decompose R into R, = (A,B) and R2 = (A,C,D,E) - Neither of the dependencies in Fcontain only attributes from (A,C,D,E) SO we might be mislead into thinking R, satisfies BCNF. - In fact, dependency AC - D in F+ shows R, is not in BCNF. B1050 19/20 6AN. Yakovets Testing Decomposition for BCNF To check if a relation R in a decomposition of R is in BCNF, Either test R for BCNF with respect to the restriction of F+ to R (that is, all FDs in Ft that contain only attributes from R) or use the original set of dependencies F that hold on R, but with the following test: - for every set of attributes a Ri, check that a+ (the attribute closure of a) either includes no attribute of R-a, or includes all attributes of R; - If the condition is violated by some B in F, the dependency a > a+-a) n Ri can be shown to hold on R, and R; violates BCNF. - We use above dependency to decompose R; Both approaches take exponential time BCNF Decomposition Algorithm result := {R}; done := false; compute Ft; while (not done) do if (there is a schema Rin result that is not in BCNF) then begin let a be a nontrivial functional dependency that holds on R; such that a R is not in Ft, and a n = 0; result := (result - {R}) U ({R; - B} U {a U B}); end else done := true; Note: each Ri is in BCNF, and decomposition is lossless-join BI050 19/20 6AN. Yakovets