Question

covert the schema into 3NF.

TableC (a1,a2,a3,a4,a5)

functionally dependencies:

a1 --> {a2,a3,a5}

a4 --> {a1,a2,a3,a5}

a3 -->{a5}

Answer:

Relation1:

Relation2:

Answer 1

Solution:

Given,

=>Relation = C(a1, a2, a3, a4, a5)

=>Set of functional dependencies = {a1 -> a2a3a5, a4 -> a1a2a3a5, a3 -> a5}

Explanation:

Finding candidate keys:

=>There is no attribute "a4" at the right hand side part of any of the given functional dependencies so attribute "a4" must be present in every candidate key as candidate key has ability to derive all the attributes of the relation.

=>(a4)+ = a1a2a3a4a5

=>As attribute closure of a4 contains all the attributes of the relation so a4 is the only candidate key and if we include any other attribute with a4 then it will be super key.

=>Candidate key = {a4}

Finding normal form of relation:

=>Relation C is in 1 NF because there is no multivalued or complex attribute in the relation.

=>Relation is also in 2 NF because there is no partial functional dependencies of type X -> Y where X is proper subset of candidate key and Y is non prime attribute.

=>Relation C is not in 3 NF because all the functional dependencies are not of type X -> Y where either X is super key or Y is prime attribute.

=>Functional dependencies voilating 3 NF = {a1 -> a2a3a5, a3 -> a5}

Converting into 3 NF:

=>C1(a1, a2, a3, a5) with functional dependencies = {a1 -> a2a3a5}

Candidate key = {a1}

=>C2(a3, a5) with functional dependency = {a3 -> a5}

Candidate key = {a3}

=>C3(a1, a2, a3, a4, a5) with functional dependency = {a4 -> a1a2a3a5}

Candidate key = {a4}

=>Now relations are in 3 NF.

I have explained each and every part with the help of statements attached to the answer above.