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

Step 1: 1NF

INVOICE(OrderID, OrderDate, CustomerNum, CustomerName,CustomerAddress,ProductID, ProductDescription, ProductStandardPrice, OrderedQuantity)

Composite Primary Key(OrderID,ProductID)

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
1006	10/24/2018	2	HugeCorp	Chicago, IL	8	Widgets	Chrome	790	4
1006	10/24/2018	2	HugeCorp	Chicago, IL	5	Wadgets	Cherry	325	2
1006	10/24/2018	2	HugeCorp	Chicago, IL	4	Scaffolding	Silver	650	1
1007	10/25/2018	6	LittleCorp	Edwardsville, IL	7	Widgets	Platinum	800	1
1007	10/25/2018	6	LittleCorp	Edwardsville, IL	11	Ladder	Silver	275	3
1008	10/26/2018	2	Huge Corp	Chicago, IL	5	Wadgets	Cherry	325	2
1008	10/26/2018	2	Huge Corp	Chicago, IL	4	Scaffolding	Silver	650	1

STEP 2:

functional dependencies :

Partial Dependencies:

OrderID -> OrderDate, CustomerNum, CustomerName, CustomerAddress

ProductID ->ProductDescription, ProductFinish, ProductStandardPrice

Full Dependency:

OrderID,ProductID -> OrderedQuantity

Step 3:

Tables in 2NF

Order(OrderID OrderDate, CustomerNum, CustomerName, CustomerAddress)

Order ID	Order	Customer	Customer	Customer
	Date	Num	Name	Address
1006	10/24/2018	2	HugeCorp	Chicago, IL
1007	10/25/2018	6	LittleCorp	Edwardsville, IL
1008	10/26/2018	2	Huge Corp	Chicago, IL

Product(ProductID ,ProductDescription, ProductFinish, ProductStandardPrice)

Product	Product	Product	Product
ID	Description	Finish	Standard Price
7	Widgets	Platinum	800
8	Widgets	Chrome	790
5	Wadgets	Cherry	325
4	Scaffolding	Silver	650
11	Ladder	Silver	275

OrderProduct(OrderID,ProductID , OrderedQuantity)

Order ID	Product	Ordered
	ID	Quantity
1006	7	2
1006	8	4
1006	5	2
1006	4	1
1007	7	1
1007	11	3
1008	5	2
1008	4	1

STEP 4:

3NF

Transitive Dependencies:

CustomerNum -> CustomerName, CustomerAddress

Tables in 3NF

Order(OrderID OrderDate, CustomerNum, CustomerName, CustomerAddress)

Order ID	Order	Customer
	Date	Num
1006	10/24/2018	2
1007	10/25/2018	6
1008	10/26/2018	2

Product(ProductID ,ProductDescription, ProductFinish, ProductStandardPrice)

Product	Product	Product	Product
ID	Description	Finish	Standard Price
7	Widgets	Platinum	800
8	Widgets	Chrome	790
5	Wadgets	Cherry	325
4	Scaffolding	Silver	650
11	Ladder	Silver	275

OrderProduct(OrderID,ProductID , OrderedQuantity)

Order ID	Product	Ordered
	ID	Quantity
1006	7	2
1006	8	4
1006	5	2
1006	4	1
1007	7	1
1007	11	3
1008	5	2
1008	4	1

Customer(CustomerNum , CustomerName, CustomerAddress)

Customer	Customer	Customer
Num	Name	Address
2	HugeCorp	Chicago, IL
6	LittleCorp	Edwardsville, IL

underlined are primary keys and italicised are foreign keys. Some attributes are both.

Do ask if any doubt. Please upvote.