How to Do Normalization in Dbms

Only available on StudyMode
  • Download(s) : 20
  • Published : March 1, 2013
Open Document
Text Preview
NORMALIZATION (Breaking down of a big single table into smaller ones) 1st STEP: UNF (Un-normalized Form) – A table with repeating groups of data OrderInvoice(OrderNo, OrderDate, CustNo, CustName, CustTel, CustContactPerson, {ItemCode, ItemDesc, UnitPrice, Quantity})

2nd STEP: 1NF (First Normal Form) – Remove Repeating Groups How?
By creating another table for the repeated data (the ones in curly bracket) 1. OrderInvoice(OrderNo, OrderDate, CustNo, CustName, CustTel, CustContactPerson) – orderInvoice is the table’s name 2.OrderInvoiceDetail (OrderNo, ItemCode, ItemDesc, UnitPrice, Quantity) – 2 primary keys (one from the first table) and the OrderInvoiceDetail is the table’s name here. **Make sure that your 2nd table will have a composite primary key – primary key from the 1st table will be brought to the 2nd table as a link of reference. Composite primary key – primary key that is made up to two keys.

3rd STEP: 2NF (Second Normal Form) – Remove Partial Dependency Partial Dependency – dependency of attributes to only half portion of the primary key. To be in 2NF, all attributes must be fully dependent on the entire set of primary key (which is a composite primary key. In the above example, OrderNo, ItemCode is the primary key, and all other attributes must be FULLY dependent on it) 1. OrderInvoice(OrderNo, OrderDate, CustNo, CustName, CustTel, CustContactPerson) – orderInvoice is the table’s name 2.OrderInvoiceDetail (OrderNo, ItemCode, ItemDesc, UnitPrice, Quantity) – 2 primary keys (one from the first table) and the OrderInvoiceDetail is the table’s name here. **Use the 2nd table; look at the one with a composite primary key (two primary keys) ItemDesc only needs ItemCode – partial dependency

UnitPrice only needs ItemCode – partial dependency
Quantity needs both ItemCode and OrderNo – full dependency So remove ItemDesc and UnitPrice. How? By creating another table for them. 1. OrderInvoice(OrderNo, OrderDate, CustNo, CustName, CustTel,...
tracking img