21-11-2012, 11:43 AM
CS 649 Database Management Systems
Normalization.pdf (Size: 153.24 KB / Downloads: 213)
Normalization
First Normal Form (1st NF)
• The table cells must be of single value.
• Eliminate repeating groups in individual tables.
• Create a separate table for each set of related data.
• Identify each set of related data with a primary key.
Do not use multiple fields in a single table to store similar data.
For example, to track an inventory item that may come from two
possible sources, an inventory record may contain fields for
Vendor Code 1 and Vendor Code 2. But what happens when you
a dd a third vendor? Adding a field is not the answer; it requires
program and table modifications and does not smoothly
accommodate a dynamic number of vendors. Instead, place all
vendor information in a separate table called Vendors, then link
inventory t o vendors with an item number key, or vendors to
inventory with a vendor code key.
Second Normal Form (2nd NF)
If it's in 1st NF and if the Primary Key is composite (multiple
columns) then any fact in that table must be a fact about the
entire composite Primary Key not just part of the Primary Key.
For example, if an inventory table has a primary key made
up of two attributes PartId and WarehouseId. Suppose the
inventory table has the warehouse address in it, since
warehouse address is a fact about WarehoseId and not
about the PartId the w arehouse address is in the wrong
table. This is in violation of the 2nd Normal Form.
Third Normal Form (3rd NF)
• If it's in the 2nd NF and there are no non-key fields that
depend on attributes in the table other than the Primary
Key.
Suppose in the Student table you had student birth date as an
attribute and you also had student's age. Students age depends
on the student's birth date (a fact about his/her birth date) so
3rd Normal Form is violated. Also, a student table that contains