Database Normalization

Normalization
A logical design method which minimizes data redundancy and reduces design flaws.
Consists of applying various “normal” forms to the database design.
The normal forms break down large tables into smaller subsets.

First Normal Form (1NF)
Each attribute must be atomic • No repeating columns within a row. • No multi-valued columns.
1NF simplifies attributes • Queries become easier. 1NF Employee (unnormalized)

Employee (1NF)

Second Normal Form (2NF)

Each attribute must be functionally dependent on the primary key. • Functional dependence - the property of one or more attributes that uniquely determines the value of other attributes. • Any non-dependent attributes are moved into a smaller (subset) table.
2NF improves data integrity. Prevents update, insert, and delete anomalies. Functional Dependence

Employee (1NF)

Name, dept_no, and dept_name are functionally dependent on emp_no. (emp_no -> name, dept_no, dept_name)

Skills is not functionally dependent on emp_no since it is not unique to each emp_no.

Employee (1NF)

Employee (2NF) Skills (2NF)

Third Normal Form (3NF)

Remove transitive dependencies. • Transitive dependence - two separate entities exist within one table. • Any transitive dependencies are moved into a smaller (subset) table. 3NF further improves data integrity. Prevents update, insert, and delete anomalies.

Transitive Dependence Employee (2NF)

Dept_no and dept_name are functionally dependent on emp_no however, department can be