Normalization

1
Normalization
2
Purpose of Normalization

• Remove data redundancies
• Eliminate inconsistent data dependencies
• Remove any anomalies
• Break raw data into more logical units, or tables

3
Considerations

• Needs of the end user
• What data is needed?
• How will users access it?
• How should data be grouped?
• What data is most commonly accessed?
• How is data related?
• What measures are needed to ensure accuracy?

4
Benefits to Normalization

• Greater organization
• Reduction of redundant data
• Data consistency
• More flexible design
• Better security

5
Drawbacks to Normalization

• Reduced database performance
• CPU usage
• Memory usage
• Input/Output

6
Denormalizing a Database

• Modify a normalized database
• Modify tables to allow controlled redundancies
• Recombine tables to eliminate certain joins

7
Further Considerations

• Decisions should be made during design
• Some normalization is necessary
• Performance vs quality and integrity
• Trade-off decisions get you the big bucks

8
Levels of Normalization

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Others

9
1NF

• Divide data into logical entities or tables
• Eliminate repeating groups
• Identify Primary Keys to make every row (record)
  in a table unique

10
2NF

• Ensure 1NF
• Remove data that is only partly related to the
  Primary Key to a new table
• Create new tables from those in the 1NF

11
3NF

• Ensure 1NF
• Ensure 2NF
• Remove data that is not dependent on the Primary
  Key to a new table

12
Making it work!Referential Integrity

• Assurance of consistent and accurate data
• Values of one column in a table depend upon the
  values of a column in another table
• Controlled through the use of Primary and Foreign
  Keys

13
Example1NF
Employees EmpID EmpName Address City State Zip Pho
ne Position Department Salary HireDate
Customers CustID CustName Address City State Zip P
hone Fax Contact
14
Example2NF
Employees EmpID EmpName Address City State Zip Pho
ne Position Department Salary HireDate
Customers CustID CustName Address City State Zip P
hone Fax Contact
Emplyees EmpID Address City State Zip Phone
EmployeePay EmpID Position Department Salary HireD
ate
15
Example3NF
Employees EmpID EmpName Address City State Zip Pho
ne Position Department Salary HireDate
Customers CustID CustName Address City State Zip P
hone Fax Contact
Emplyees EmpID Address City State Zip Phone
ZipCodes ZipCode City State
EmployeePay EmpID PositionID DeptID Salary HireDat
e
Positions PositionID Position
Departments DeptID Department