Question

Q1)This is given information from a local electronic discount store,/ PID- P Descㅡㅡㅡ CPhone-C.Name-State CZ code. CID l p·Manufacture CountrylDtCountry s101 TV S0102 TV s103 TV v333 VideoGame 555-666 Tom v555 VideoGame 555-666 Tom 11 Japan 11Japan 1 USA 1 USA 1 USA 1 USA 1 USA 1 USA 1 USA 1 USA 12 China 555-666 Tom 333-222 Bob TX TX NV TX TX 68002 1001 Samsung 10888 2002 Samsung 75080 3003 LG 68002 1001 FIFA19 444-111 Liz v555 VideoGame 444-111 Liz N 75080 3003 Call of Duty 4 333-222 Bob 333-222 Bob 10888 2002 LG s106 TV b999 Battery p545 Printer 444-111 Liz h222 Headphone 555-666 Tom TX68002 1001 JBL h333 Headphone 444-111 LizNV TX TX NV 10888 2002 Duracell 75080 3003 HP-M402 75080 3003 TMZ 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 points) b) Is this table needs any 2NF normalization? Why? (10 points) c) Is this table needs any 3NF normalization? Why? (5 points) d) Is this table needs any BCNF normalization? Why? (5 points) Please answer all question with YES and NO then explain Why ? Show your work with diagrams. solution
Database Fundamentals class.

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

Normalization in database helps to remove redundancy and inconsistancy of data.

Here given local electronic discount table needs to normalize into the normal form as given below.

First Normal Fomm :In First Normal Form (1NF) all the columns in the table should be atomic in nature that means each column should contain one value & each row should contain the same number of columns. Repeating groups or duplicate columns are not allowed in first normal form.

Question a :

No this table does not need any further first normalization because given table is in the first normal form so there is no column in the table can have multiple values and no duplicate column exists in the table.

Second Normal Form (2NF):Second Normal Form (2NF) should full fill requirements of first normal form (1NF) and all the columns in the table should functionally depend upon primary key column in the table. Partially dependencies are not allowed in second normal form

Question b :

Yes , given table needs to normalize into second normal form because there are some columns partially depends upon another column like P.Desc, P.Manufacture depends upon P.ID hence needs to keep all these details into separate table also c.Name , c,phone depends upon C.ID hence needs to keep all the details in teh separate table.Below are the table in second normal form.

1.Table Name : Country

Description : This table stores country details

Schema :CountryID-Primary key , CountryName

Below is the table data

CountryID	CountryName
1	USA
11	Japan
12	China

2.Table Name :State

Description :This table stores the state details

Schema :StateID-Primary key , StateName,CountryId-Foreign key

Below is the table data

StateID	StateName	CountryId
101	TX	11
102	NV	1

Note :Here in the electronic discount table contains last row having state as NV and country as China but zipcode is same for NV that belongs to USA . Hence last row state NV is not considered here in this state table.

3.Table Name : Customer

Description :This table is used to store customer information.

Schema : CID-Primary key , CName,CPhone,CZipcode, StateID-Foreign key

CID	CName	CPhone	CZipcode	StateID
1001	Tom	555-666	68002	101
2002	Bob	333-222	10888	101
3003	Liz	444-111	75080	102

4.Table Name :Product

Description :This table stores the products details.

Schema :PID-Primary key , PDes, PManufacture

Below is the table data

PID	PDes	PManufacture
s101	TV	Samsung
s0102	TV	Samsung
s103	TV	LG
v333	VideoGame	FIFA19
V555	VideoGame	Call of Duty 4
s106	TV	LG
b999	Battery	Duracell
h222	Headphone	JBL
p545	Printer	Hp-M402
h333	Headphone	TMZ

Third Normal Form (3NF): In Third Normal Form (3NF), It should full fill all the requirements of second normal form and all the columns in the table should be depend upon primary key column. Transitive dependency are not allowed in the 3NF. Transitive dependency means non key column depends upon non key column in the table.

Question c:

Yes , this table needs to normalize into the third normal form because this tabale contains transitive dependancies. Also primary key of one table is used as foreign key in another table to form a relation as given below.

Table Name :OrderDetails

Schema :OrderDetailsID-Primary key , CID-Foreign key , PID -foreign key

OrderDetailsID	CID	PID
1	1001	s101
2	2002	s0102
3	3003	s103
4	1001	v333
5	1001	v555
6	3003	v555
7	2002	s106
8	2002	b999
9	3003	p545
10	1001	h222
11	3003	h333

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.