Question

Normalization: Answer all 4 questions.

You are given the following relation R and some functional dependencies.

R(SID, Project, Code, ListOfSupplies, Name, Initials, Abbrev)

• Project → ListOfSupplies
• SID → Name
• Name → Initials
• Project, Initials → Abbrev
• SID, Project → Code
• Code → SID

1. Is R in 1NF? If not, normalize R into a collection of 1NF relations.
2. Is R in 2NF? If not, normalize R (or your collection of 1NF relations) into a collection of 2NF relations.
3. Is R in 3NF? If not, normalize R (or your collection of 2NF relations) into a collection of 3NF relations.
4. Is R in BCNF? If not, normalize R (or your collection of 3NF relations) into a collection of BCNF relations, or explain why it is impossible.

Answer 1

Solution:

Explanation:

=>Let say SID = A, Project = B, Code = C, ListOfSupplies = D, Name = E, Initials = F, Abbrev = G

=>Hence relation = R(A, B, C, D, E, F, G)

=>Set of functional dependencies = {B -> D, A -> E, E -> F, BF -> G, AB -> C, C -> A}

(1)

Explanation:

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

(2)

Explanation:

Finding candidate keys:

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

=>(AB)+ = ABCDEFG

=>(BC)+ = ABCDEFG

=>As attribute closure of AB and BC derive all the attributes of the relation hence AB and BC are candidate keys of relation R.

=>Candidate keys = {AB, BC}

Checking 2 NF:

=>Set of functional dependencies = {B -> D, A -> E, E -> F, BF -> G, AB -> C, C -> A}

=>Candidate keys = {AB, BC}

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

=>Functional dependencies voilating 2 NF = {B -> D, A -> E}

Converting relation in 2 NF:

=>R1(B, D) with functional dependency = {B -> D}

Candidate key = {B}

=>R2(A, E) with functional dependency = {A -> E}

Candidate key = {A}

=>R3(A, B, C, F, G) with functional dependendencies = {BF -> G, AB -> C, C -> A}

Candidate keys = {AB, BC}

=>Now relations are in 2 NF.

(3)

Explanation:

=>Relations are 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 = {BF -> G}

Converting relations into 3 NF:

=>R1(B, D) with functional dependency = {B -> D}

Candidate key = {B}

=>R2(A, E) with functional dependency = {A -> E}

Candidate key = {A}

=>R3(B, F, G) with functional dependency = {BF -> G}

Candidate key = {BF}

=>R4(A, B, C, F) with functional dependendencies = {AB -> C, C -> A}

Candidate keys = {AB, BC}

=>Now relations are in 3 NF.

(4)

Explanation:

=>Relations are not in BCNF because all the functional dependencies are not of type X - > Y where X is super key.

=>Functional dependency voilating BCNF = {C -> A}

Converting relations into BCNF:

=>R1(B, D) with functional dependency = {B -> D}

Candidate key = {B}

=>R2(A, E) with functional dependency = {A -> E}

Candidate key = {A}

=>R3(B, F, G) with functional dependency = {BF -> G}

Candidate key = {BF}

=>R4(A, C) with functional dependency = {C -> A}

Candidate key = {C}

=>R4(B, C, F) with no functional dependency

=>Now relations are in BCNF.

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