Question

For the following relations and set of FDs: 1. give a key for the relation; 2. state whether the relation is in BCNF, and if it is not state why: 3. give a set of relations in 3NF equivalent to the original relation 1. (33 points) What is the closure of (A,B) with respect to R(A,B,C,D,E,F,G)if R has the following functional dependencies? (a) GCDE AF BF - ABC FC G (b) D-AC-D A+B ABC 2 33 points for each of the following questions, if the schema RA,B,C,D,E) is in BCNF or NF with respect to the given the functional dependencies, draw a circle around BCNF or 3NF (or both) is appropriate. Show your work! (a) BCNF NF ABC AB A-DEC-D (6) BCNF NF E-BCD D-AE B+ CD

Answer 1

Solution:

(1)

Given relation R(A, B, C, D, E, F, G)

(a)

Given functional dependencies ={ G -> CDE, A -> F, BF -> ABC, FC -> G}

=> Closure of AB => (AB)⁺ = ABFCGDE => (AB)⁺ = ABCDEFG

(b)

Given fucrtional dependencies = { D ->A , C -> D, A -> B, AB -> C}

=> Closure of AB => (AB)⁺ = ABCD

(2)

Given relation R(A, B, C, D, E)

=> 3NF: A functional dependency X -> Y is in 3 NF iff

(i) X must be a superkey

(ii) Y must be a prime attribute

At least one of the two conditions should be satisfied in case of 3 NF

=>BCNF: A functional dependency X -> Y is in BCNF iff

(i) X must be a superkey

(a)

Given functional dependencies = { AB -> C, A-> B, A -> DE, C -> D}

=> Finding candidate keys: We can see that there is no "A" attribute at the right-hand side part of any of the functional dependencies so attribute "A'' must be included in all the candidate keys as candidate keys have the ability to derive all the attributes of the relation.

(A)⁺ => Closure of A = ABCDE means A can derive all the attributes of the relation R so A is candidate key and there will be no more candidate keys as we must need to include attribute "A" in all candidate keys and "A" is already a candidate key so super keys will be formed in that case.

=> Candidate keys = {A}
=> Checking 3 NF: Given functional dependency C -> D is not in 3NF as C is not superkey and D is not a prime attribute.

=> Checking BCNF: As the relation is not in 3NF so it can't be in BCNF

So the relation is not in 3 NF and also not in BCNF

(b)

Given functional dependencies = { E -> BCD, D -> AE, B -> CD}

=> Finding candidate keys:

(B)⁺ = ABCDE

(D)⁺ = ABCDE

(E)⁺ = ABCDE

=>candidate keys are = {B, D, E}

=> Checking 3 NF: Relation is in 3 NF as all the left part of every functional dependency is a superkey.

=> Checking BCNF:Relation is in BCNF as all the left part of every functional dependency is a superkey.

So the relation is in both 3 NF and BCNF