Question

This is a simple list table of a company trying to keep track of parts that they sell and orders that came in purchasing those parts (in other words, not a database but a flat one table file). You will design a database for this company so that they won’t be relying on a simple 1 table list system to keep track of their data. Looking at the table below, produce the 3NF of the data.

OrderNum	OrderDate	PartNum	Description	NumOrdered	QuotedPrice
21608	10/20/2010	AT94	Iron	11	$21.95
21610	10/20/2010	DR93	Gas Range	1	$495.00
21610	10/20/2010	DW11	Washer	1	$399.99
21613	10/21/2010	KL62	Dryer	4	$329.95
21614	10/21/2010	KT03	Dishwasher	2	$595.00
21617	10/23/2010	BV06	Home Gym	2	$794.95
21617	10/23/2010	CD52	Microwave Oven	4	$150.00
21619	10/23/2010	DR93	Gas Range	1	$495.00
21523	10/23/2010	KV29	Treadmill	2

Create the database in an actual database application (i.e Microsoft Access, MySQL or Oracle, etc.). You must use SQL commands to create the tables in the database (i.e Create Table command). Submit the SQL commands with the completed database as part of the assignment. The tables should be populated with records from the scenario you chose.

Answer 1

Consider the relation given

FIrst Normal Form (1NF) :

• This relation is in the first normal form because all columns are atomic in nature
• each column contains single value '
• and each row has same number of olumns

Second Normal Form (2NF) :

• This table contains partial dependency that needs to remove by making new table like
  • Part :Part table store part details like PartNum , Description
  • Orders :This table stores order details likeIron orderNum and OrderDate

Below are tables in 2NF

1.Table Name :Part

Schema :Part (PartNum , Description )

FD :PartNum==>Description

Below is the table data

PartNum	Description
AT94	Iron
DR93	Gas Range
DW11	Washer
KL62	Dryer
KT03	Dishwasher
BV06	Home Gym
CD52	Microwave Oven
KV29	Treadmill

2.Table Name :Orders

Schema :Orders(OrderNum , OrderDate)

FD :OrderNum==>OrderDate

Below is the table data

OrderNum	OrderDate
21608	10/20/2010
21610	10/20/2010
21613	10/21/2010
21614	10/21/2010
21617	10/23/2010
21619	10/23/2010
21523	10/23/2010

Third Normal Form (3NF) :

• Above tables contains transitive dependency hence above tables needs to normalize into 3NF

Below are tables in 3NF

1.Table Name :Part

Schema :Part (PartNum , Description )

FD :PartNum==>Description

Below is the table data

PartNum	Description
AT94	Iron
DR93	Gas Range
DW11	Washer
KL62	Dryer
KT03	Dishwasher
BV06	Home Gym
CD52	Microwave Oven
KV29	Treadmill

2.Table Name :Orders

Schema :Orders(OrderNum , OrderDate)

FD :OrderNum==>OrderDate

Below is the table data

OrderNum	OrderDate
21608	10/20/2010
21610	10/20/2010
21613	10/21/2010
21614	10/21/2010
21617	10/23/2010
21619	10/23/2010
21523	10/23/2010

3.Table Name :OrderDetails

Schema :OrderDetails(OrderNum , PartNum,NumOrdered,QuotedPrice)

FD :OrderNum,PartNum==>NumOrdered,QuotedPrice

Below is the table data

OrderNum	PartNum	NumOrdered	QuotedPrice
21608	AT94	11	$21.95
21610	DR93	1	$495.00
21610	DW11	1	$399.99
21613	KL62	4	$329.95
21614	KT03	2	$595.00
21617	BV06	2	$794.95
21617	CD52	4	$150.00
21619	DR93	1	$495.00
21523	KV29	2

============================================

Below ae tables in MySQL :

1.Table Name :Part
create table Part(
PartNum varchar(20) Primary key,
Description varchar(100));

/*inserting records into Part Table*/
insert into Part values ('AT94','Iron');
insert into Part values ('DR93','Gas Range');
insert into Part values ('DW11','Washer');
insert into Part values ('KL62','Dryer');
insert into Part values ('KT03','Dishwasher');
insert into Part values ('BV06','Home Gym');
insert into Part values ('CD52','Microwave Oven');
insert into Part values ('KV29','Treadmill');

/*selecting records from Part*/
select * from part;

Screen in MySQL :

***********************************************

2.Table Name :Order
create table Orders(
OrderNum int Primary key,
OrderDate Date);

/*inserting records into Orders Table*/
insert into Orders values (21608,'2010/10/20');
insert into Orders values (21610,'2010/10/20');
insert into Orders values (21613,'2010/10/21');
insert into Orders values (21614,'2010/10/21');
insert into Orders values (21617,'2010/10/23');
insert into Orders values (21619,'2010/10/23');
insert into Orders values (21523,'2010/10/23');

/*selecting records from Orders*/
select * from Orders;

Screen in MySQL :

**********************************************

3.Table Name :OrderDetails
create table OrderDetails(
OrderNum int ,
PartNum varchar(20),
NumOrdered int,
QuotedPrice decimal(6,2));

/*inserting records into OrderDetails Table*/
insert into OrderDetails values (21608,'AT94',11,21.95);
insert into OrderDetails values (21610,'DR93',1,495.00);
insert into OrderDetails values (21610,'DW11',1,399.99);
insert into OrderDetails values (21613,'KL62',4,329.95);
insert into OrderDetails values (21614,'KT03',2,595.00);
insert into OrderDetails values (21617,'BV06',2,794.95);
insert into OrderDetails values (21617,'CD52',4   ,150.00);
insert into OrderDetails values (21619,'DR93',1,495.00);
insert into OrderDetails values (21523,'KV29',2,null);

/*selecting records from OrderDetails*/
select * from OrderDetails;

Screen in MySQL :