The first 2 are store receipts, like in a drug store.Can you picture yourself having been in a store and buying these items? Should be a familiar experience - we do it all the time. Now how would the database for this look?

Now in the third tab is the "First Try" where we try to analyze these receipts from a database standpoint. Of course this is not fini,shed. We still to apply the 3 rules, NF1, NF2, and NF3 until we have a completely normalized database.

Now as before, the answer is given as the fourth tab Step 3, which results after you have applied the three steps.

Can you create 2 new tabs with the intermediate results - step 1 and step 2?

Hint: The unique key for the "First Try" tab is

"order #" and

"part no"

Do you know why?

Store Receipts

part no.	part desc	qty	quot cost	cost this item
103	toothpaste	2	4.5	9
250	toothbrush	9	1.5	13.5
222	mouthwash	3	9.5	28.5
301	powder	1	5.5	5.5
555	bandaids	4	3.4	13.6
		19		70.1

Receipt 2

		Order	1017		Date:	10/15/2014
		Custmer #	1023
		Customer	Sarie Thumb		cust addr	444 First St
part no.	part desc	qty	quot cost	cost this item
103	toothpaste	2	4.5	9
250	toothbrush	9	1.75	15.75
		11		24.75

First Try

	Database preparation
order #	order date	cust #	cust name	cust addr	part no	part desc	qty	quot price	std price
1003	9/30/2014	1001	Tom Thumb	101 Main St	103	toothpaste	2	4.5	4.5
1003	9/30/2014	1001	Tom Thumb	101 Main St	250	toothbrush	9	1.5	1.75
1003	9/30/2014	1001	Tom Thumb	101 Main St	222	mouthwash	3	9.5	9.5
1003	9/30/2014	1001	Tom Thumb	101 Main St	301	powder	1	5.5	5.5
1003	9/30/2014	1001	Tom Thumb	101 Main St	555	bandaids	4	3.4	3.4
1017	10/15/2014	1023	Sarie Thumb	444 First St.	103	toothpaste	2	4.5	4.5
1017	10/15/2014	1023	Sarie Thumb	444 First St.	250	toothbrush	3	1.75	1.75
Note that the order # + the part no is the primary key - it uniquely specifies each row
Three Rules of Database Design
NF1 - no repeating groups
NF2 - is NF1 and no field depends on only part of the key
NF3 - is NF2 and no field depends on a non-key
definitions - relational database - depends on relations - real world entities
(not relationships - that comes later)
primary key - each row or instance or record in the database must have a unique key which specifies it

Step 3

	Solution: Keep the non-key they depend on in the main table, and remove the depedent non-keys to a separate table
	here the cust #, cust name, and cust address are moved to the customer table
		Order Table				Customer Table
	order #	order date	cust #		cust #	cust name	cust addr
	1003	9/30/2014	1001		1001	Tom Thumb	101 Main St
	1017	10/15/2014	1023		1023	Sarie Thumb	444 First St.
		Order-Part Table					Part Table
	order #	part #	qty	quot price		part #	part desc	std price
	1003	103	2	4.5		103	toothpaste	4.5
	1003	250	9	1.5		250	toothbrush	1.75
	1003	222	3	9.5		222	mouthwash	9.5
	1003	301	1	5.5		301	powder	5.5
	1003	555	4	3.4		555	bandaids	3.4
	1017	103	2	4.5
	1017	250	3	1.75
Exercise: Add one more order by customer 1023, with date 10/22/14, order # 1018
for 2 mouthwash and 2 powder at the standard price?