Database Normalization

Modification Anomalies

  • Once our E-R model has been converted into relations, we may find that some relations are not properly specified. There can be a number of problems:
    • Deletion Anomaly: Deleting one fact or data point from a relation results in other information being lost.
    • Insertion Anomaly: Inserting a new fact or tuple into a relation requires we have information from two or more entities – this situation might not be feasible.
    • Update Anomaly: Updating one fact in a relation requires us to update multiple tuples.
  • Here is a quick example to illustrate these anomalies: A company has a Purchase Order form:

  • Our dutiful consultant creates the E-R Model directly matching the purchase order:

  • When we follow the steps to convert to a set of relations this results in two relations (keys are underlined):
    PO_HEADER (PO_Number, PODate, Vendor, Ship_To, ...)
    
    LINE_ITEMS (PO_Number, ItemNum, PartNum, Description, Price, Qty)
    
    
  • Consider some sample data for the LINE_ITEMS relation:
    PO_Number ItemNum PartNum Description Price Qty
    O101 I01 P99 Plate $3.00 7
    O101 I02 P98 Cup $1.00 11
    O101 I03 P77 Bowl $2.00 6
    O102 I01 P99 Plate $3.00 5
    O102 I02 P77 Bowl $2.00 5
    O103 I01 P33 Fork $2.50 8
  • What are some of the problems with this relation ?
    1. What happens if we want to add the fact that Order O103 has quantity 5 of part P99 ?
    2. What happens when we delete item I02 from Order O101 ?
    3. What happens if we want to change the price of the Plate (P99)?
  • These problems occur because the relation in question contains data about 2 or more themes.
  • Typical way to solve these anomalies is to split the relation in to two or more relations – This is part of the Process called Normalization discussed next.

Tags: , , , , , , , , , , , , , , , , , ,

5 Responses to “Database Normalization”

  1. anup #

    Its a best web site to understand normalization of database,m resally thank for admin……

    January 12, 2014 at 9:57 pm
    • Vincent6767 #

      Yes it is, This website provide examples that are easy to understand. My understanding about normalization has improve because of this website :D

      May 29, 2014 at 2:39 am
  2. Dinesh Shaw #

    it helped me very well thank you very much admin sir

    May 27, 2014 at 10:13 am

Trackbacks/Pingbacks

  1. Logical meaning of all the conditions given in all normal forms(1NF, 2NF, 3NF, BCNF)? | Developers Questions - Msn4Free.com - December 9, 2013

    […] I read all the theoretical blogs that tells about what is normalizationa and all normal forms(1NF, 2NF, 3NF, BCNF). There are some links that I read, LINK 1, LINK 2, LINK 3. […]

  2. Interview | Pearltrees - January 13, 2014

    […] Database Normalization Full Tutorial – Page 3 […]

Leave a Reply

You must be logged in to post a comment.