Normalisation Worked example for an Order Remember The

Normalisation Worked example for an Order Remember : The data should depend upon the key, the whole key and nothing but the key.

Anderson Garden Centre - Order Number : 1235 Cust. No : JA 007 Cust. Name : Jim Anderson Date : 17 -May-2008 Product No 3785 8798 3439 Description Wheel Barrow Watering Can Garden Shed This slide shows one of the garden centre’s orders. What attribute would you select to be a unique identifier? Is there any information that repeats? Quantity 1 5 1 Unit Price £ 23. 99 £ 5. 99 £ 350. 00

UNF ORDER #Order No Cust Name Date #Product No Description Quantity Unit Price Unnormalised Form Entity Name Primary Key Order number has been chosen as the unique identifier. A unique identifier for the repeated group has been chosen and the repeated data italicised. The repeated data should now be removed into a separate relation and original key added to maintain identity.

First Normal Form UNF ORDER #Order No Cust. Name Date #Product No Description Quantity Unit Price 1 NF ORDER #Order No Cust. Name Date ORDER ITEM #Order No #Product No Description Quantity Price The original relation has been named as ORDER. The repetition of product information has been removed into a separate relation and the original key brought down. Remove non-key attributes and depending key into separate relation

Second Normal Form UNF ORDER #Order No Cust. Name Date #Product No Description Quantity Unit Price Description & Price only depend on Product and are removed along with depending key into a separate relation 1 NF ORDER #Order No Cust. Name Date ORDER ITEM #Order No #Product No Description Quantity Price 2 NF ORDER #Order No Cust Name Date ORDER ITEM #Order No #Product No Quantity PRODUCT #Product No Description Price Do all non-key attributes depend on the primary key or are there any inter data dependencies?

Third Normal Form UNF ORDER #Order No Cust Name Date #Product No Description Quantity Unit Price 1 NF ORDER #Order No Cust Name Date ORDER ITEM #Order No #Product No Quantity Price Customer name depends on Customer No remains in the Order relation as a foreign key linking to the primary key of the new relation, CUSTOMER 2 NF ORDER #Order No Cust Name Date ORDER ITEM #Order No #Product No Quantity PRODUCT #Product No Description Price 3 NF ORDER #Order No * Cust No Date ORDER ITEM #Order No #Product No Quantity PRODUCT #Product No Price CUSTOMER Cust No Cust Name