Question

Let’s consider the following table storing the information about various dealers for various products. And it stores the dealer details along with the products we purchase from them and the product count.

Dealer_ID	Dealer_Name	Product_ID	Quantity
D102	Jade Garden	P121	66
D102	Jade Garden	P103	188
D110	Tim Ho Wan	P102	88
D105	Goose Manor	P121	66
D104	Daikiya	P110	288
D104	Daikiya	P102	10

(a) Identify all Functional Dependencies for this relation. (4 points)

(b) List all candidate keys of this relation. (4 points)

(c) Is the relation in 3NF and why? If not, normalize it into 3NF . (5 points)

(d) Is the relation in BCNF and why? If not, normalize it into BCNF . (5 points)

Answer 1

Solution:

(a)

Explanation:

=>Let say Dealer_ID = A, Dealer_Name = B, Product_ID = C, Quantity = D and relation name = R

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

Finding functional dependencies:

=>On both the Dealer_ID D102 and D104 it is resulting same Dealer_Name Jade Garden and Daikiya respectively hence we can write A -> B

=>Similary we can also write B -> A, D -> C

=>We can also write AC -> BD, AD -> BC, BC -> AD, BD -> AC

=>Hence set of functional dependencies = {A -> B, B -> A, D -> C, AC -> BD, AD -> BC, BC -> AD, BD -> AC}

(b)

Explanation:

Finding candidate keys:

=>(AC)+ = ABCD

=>(AD)+ = ABCD

=>(BC)+ = ABCD

=>(BD)+ = ABCD

=>Hence candidate keys = {AC, AD, BC, BD}

(c)

Explanation:

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

=>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 is also in 3 NF because all the functional dependencies of type X -> Y where either X is super key or Y is prime attribute.

(d)

Explanation:

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

Decomposition of relation:

=>R1(A, B) with functional dependencies = {A -> B, B -> A}

Candidate keys = {A, B}

=>R2(C, D) with functional dependencies = {D -> C}

Candidate key = {D}

=>Now relations are in BCNF.

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