Question

Below is an invoice sent out by MegaCorp.

Order ID	Order	Customer	Customer	Customer	Product	Product	Product	Product	Ordered
	Date	Num	Name	Address	ID	Description	Finish	Standard Price	Quantity
1006	10/24/2018	2	HugeCorp	Chicago, IL	7	Widgets	Platinum	800	2
					8	Widgets	Chrome	790	4
					5	Wadgets	Cherry	325	2
					4	Scaffolding	Silver	650	1
1007	10/25/2018	6	LittleCorp	Edwardsville, IL	7	Widgets	Platinum	800	1
					11	Ladder	Silver	275	3
1008	10/26/2018	2	Huge Corp	Chicago, IL	5	Wadgets	Cherry	325	2
					4	Scaffolding	Silver	650	1

STEP 1:

Convert the above to 1NF

1. Provide a relation of the above INVOICE, underlining the primary keys.

STEP 2:

Consider the following functional dependencies of the invoice data:

OrderID à OrderDate, CustomerNum, CustomerName, CustomerAddress

ProductID à ProductDescription, ProductFinish, ProductStandardPrice

STEP 3:

Convert your 1NF table to 2NF. (i.e., look only at non-key attributes that are dependent on a single PK)

A relation is in 2NF if it contains no partial functional dependencies.

1. Provide me 2NF relations based on the dependencies above. BE CAREFUL:   I am NOT asking you to do 3NF yet!!

STEP 4:

Now, you’ve realized there are some additional functional dependencies:

CustomerNum à CustomerName, CustomerAddress

Convert your 2NF relations to 3NF based on the dependencies you just received.

Answer 1

To convert to 1NF, all cells should have some value. The table will become like this:

The given functional dependencies are:

OrderID → OrderDate, CustomerNum, CustomerName, CustomerAddress

ProductID → ProductDescription, ProductFinish, ProductStandardPrice

These need to be removed to convert to 2NF. This will result in the following database:

There is still a partial functional identity:

CustomerNum  → CustomerName, CustomerAddress

This needs to be removed to convert to 3NF. The database will become as follows: