How to Do Normalization in Dbms
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,
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,