What is NORMALIZATION

 NORMALIZATION

WHAT IS NORMALIZATION’S?

      Normalization is a process in which we systematically examine relations for anomalies and, when detected remove those anomalies by splitting up the relation into two new, related,relation.

NORMALIZATION

      Normalization is an important part of the database development process:

      Designer get their first real look into how the data are going to interact in the database.

THE IMPORTANT OF NORMALIZATION

      Finding the problem with the database structure during normalization is strongly preffered to finding problem further along in the developnment proccess because at this point it is fairly easy to cycle backto the conceptual model entity relational model and make change.

      Trade-off between data redudancy and performance, normalization a relation reduce data redudancy but introduce the need for joins when all of the data is required by an application such as report query.

 THE DISADVANTAGE OF NORMALIZATION

      Hignest  normalization is not always desirable

      More joins are required

      Affect data retrievel perfirmance / high reponnse time

      for most bussines database design purpose 3NF is as high as we need to go in normalization process.

MODIFICATION ANOMALIES

      Once our e-r model has been converted into relaTION are not properly specified . there can be a number of problem:

      Deleted anomalies: deleting one fact or data point from a relation result in other information being lost.

      Insertion anomalies: inserting a new fact or tuple into a relation requiress we have information from two or more entities – this situation might not be feasible.

      Update anomalies: updating one fact in a relation requires us to update multiply tuples

EXAMPLES OF ANOMALIES

      Here is quick example illustrate these anomalies : a company has a purchase order form:

Purchase Order

PO#:0101        Date: 9/24/97

Vendor : Happy V Co.

Ship To: 123 Cool Way

 

Ln   Part  Desc     Price  Qty

1   P99     Plate   $3.00     7

2   P98     Cup     $1.00     11

      E-R Model Directly Macting The Purchase Order:

 

      When we follow the step to covert to a set of relation this result in two relation (keys are undrlined):

      PO_HEADER (PO_HEADER,PONumber, PODate, Vendor, ship to,.....)

      LINE_ITEM (PO_number , ItemNum, PartNum, description, price , qty)

      Consider some sample data for the line item

Po_Number	Itemnumber	Part Num	Decription	Price	Qty
O101	Io1	P99	Plate	$3.00	7
O101	Io2	P98	Cup	$1.00	11
O101	Io3	P77	Bowl	$2.00	6
O102	Io1	P99	Plate	$3.00	5

      What are some of the problem with this relation ?

      What Happens If We Want To Add The Fact That Order O101 Has Quatity 11 Of Part P99?

      What Happen When We Delete Item Io2 From Order O101?

      What Happent If We Want To Change The Price Of The Plate(P99)?

      These problem occur because the relation in question contains data about 2 or more themes.

      Typical way to solve these anomalies in to split a relation in to two more relation this is part of the process called normalization discussed latter.