IE: Design. Normalisation Worked example

• First Normal Form (1NF)
  - Remove repeating groups and identify entities and primary keys. (identify any sub-forms)
  • Invoice
    • (InvId, InvDate, CustId, CustLName, CustFName, CustAdd1, CustAdd2, CustAdd3, Freight, Insur, GST, TotalDue)
  • Invoice Item
    • InvId*, ProdCode, Dsn, Qty, Price, Amt)
• Second Normal Form (2NF)
  - Remove non-key data elements not dependent on the full key.
  • Invoice (only 1 key field so ignore)
    • (InvId, InvDate, CustId, CustLName, CustFName, CustAdd1, CustAdd2, CustAdd3, Freight, Insur, GST, TotalDue)
  • Invoice Item (look for fields dependent on ProdCode)
    • (InvId*, ProdCode, Dsn, Qty, Price, Amt)
    • becomes..
      • Invoice item
        • (InvId*, ProdCode*, Qty, Amt)
      • Product
        • (ProdCode, Dsn, Price)
• Third Normal Form (3NF)
  - Remove non-key fields dependent on other non-key fields.
  • Invoice
    • (InvId, InvDate, CustId, CustLName, CustFName, CustAdd1, CustAdd2, CustAdd3, Freight, Insur, GST, TotalDue)
    • becomes
      • Invoice
        • (InvId, InvDate, CustId*, Freight, Insur, GST, TotalDue)
      • Customer
        • (CustId, CustLName, CustFName, CustAdd1, CustAdd2, CustAdd3 )
  • Invoice item
    • (InvId*, ProdCode*, Qty, Amt)
  • Product
    • (ProdCode, Dsn, Price)
• Finally remove calculated fields
  - Remove calculated fields document any variations.
  • Invoice
    • (InvId, InvDate, CustId*, Freight, Insur, GST, TotalDue)
    • becomes..
      • (InvId, InvDate, CustId*, Freight, Insur)
  • Customer
    • (CustId, CustLName, CustFName, CustAdd1, CustAdd2, CustAdd3 )
  • Invoice item
    • (InvId*, ProdCode*, Qty, Amt)
    • becomes ..
      • (InvId*, ProdCode*, Qty)
  • Product
    • (ProdCode, Dsn, Price)

Ack: K. Allpress Unitec99: [Rev 01/03/01] 16/5/99 ©1999 V/2-Com (Verhaart), Box 8415, Havelock North, New Zealand.