Question

Q1)This is given information from a local electronic discount store. Phone!C. Name|State C.ZipCode|C. I DIP Manufacture IDlCount P.ID P.Desc s101 TV 0102 TV s103 v333 VideoGame 555-666 TomTX v555 VideoGame 555-666 Tom TX 68002 1001 Call of Duty 4 v555 VideoGame s106 555-666 Tom TX 333-222 BobTX 4-111 Liz NV 68002 1001 Samsun 10888 2002 Samsun 75080 3003 LG 68002 1001 FIFA19 USA 1USA USA USA 1USA USA USA USA 2lChina 111 Liz IZ NV BobTX 750803003 Call of 10888 2002 LG 108882002 Duracell 75080 3003 HP-M402 68002 1001UBL 750803003 4 333-222 Batery 33-2 999 Batte 545 Printer NV h222 Headphone 555-666 Tom TX NV 111 Liz h333 Headphone 111 Liz They have some problems with their solution and you may help them with your solution offer a) Is this table needs any 1NF normalization? Why? (10 b) Is this table needs any 2NF normalization? Why? (10 c) Is this table needs any 3NF normalization? Why? (5 d) Is this table needs any BCNF normalization? Why? (5 Please answer all question with YES and NO then explain points) points) points) points) Why ? Show your work with solution diagrams.

Answer 1

Solution:

This solution has an explanation, step by step procedure
for your clear and better understanding.

EXPLANATION:
Before going to directly answer first let's see the approach for solving.
Normalization is the process of reducing redundancy in the table.
So for the normalization, there are different forms.
1NF,2NF,3NF,BCNF,...
1NF: When all cells having a single data and no two columns represent the same type of pieces of information.

2NF: A table is said to be in 2NF form when there is not any partial dependency.
i.e there must not any non-proper attributes which partially depend on the proper attribute.

3NF: A table is said to be in 3NF form when there is not any Transition dependency.
i.e there must not any non-proper attributes which partially depend on the non-proper attribute.

BCNF: A table is said to be in BCNF form when x must be superkeys.
       where functional dependency from x to y.
i.e x->y

so Now let's see below for more visualization:
(a) NO
As there are not any cell which has more than one values and the table doesn't have more than 1 columns which have the same type of data.
so the table doesn't need 1NF normalization.

(b) YES
As from table P.ID and C.Phone is one of the candidate keys.
so these will identify each row identically.
so A table is said to be in 2NF if the table doesn't have any partial dependency.
so here,
proper attribute: {P.ID, C.Phone}
Non-proper attribute : {P.Desc, C.Name ,State, C.Zipcode,C.ID, P.Manifacture , CountryID , Country }

so from the table, There is functional dependency from C.Phone to State.
i.e
444.111->NV
and this dependency is called as a partial dependency because
C. Phone is the proper attribute and State is a non-proper attribute.
so the table is not in 2NF form.

(c) YES
As from table P.ID and C.Phone is one of the candidate keys.
so these will identify each row identically.
so A table is said to be in 3NF if the table doesn't have any transitive dependency.
so here,
proper attribute: {P.ID, C.Phone}
Non-proper attribute : {P.Desc, C.Name ,State, C.Zipcode,C.ID, P.Manifacture , CountryID , Country }
so candidate keys will uniquely identify each row. Hence each column will be determined by candidate keys.
so C. Name and C.ID will also be determined by the candidate key.
so P. ID and C. Phone will determine C.Name and there is functional dependency from C. Name to C.ID.
i.e
Tom->1001
so here C. Name and C.ID both are non-proper attributes.
Hence The table is not in 3 NF form.
So it needs to normalize to 3NF from.

(d) YES
suppose a functional dependency from x to y hold true.
i.e
x->y
so the table must have each and every x as a super key to be in BCNF form.
and from the table, these condition doesn't hold true because there is functional dependency from C.Name to State.
i.e
Liz->NV
so here C. Name is not the super key because super keys must contain candidate keys.
Hence the table needs to be normalized to BCNF from.