Lesson 5: Database Normalization

Normalization

• Involves four stages: un-normalized design, first normal form, second normal form, and third normal form
• Most business-related databases must be designed in third normal form
• A technique used to make complex databases more efficient and easier to handle
• Eliminates Redundant Data

Normalization: Standard Notation Format

Designing tables is easier if you use a standard notation format to show a table’s structure, fields, and primary key

Example: NAME (FIELD 1, FIELD 2, FIELD 3)

Normalization: Repeating Groups and Un-normalized Design

• Repeating group
• Often occur in manual documents prepared by users
• Un-normalized design

Example:


Normalization: First Normal Form

• A table is in first normal form (1NF) if it does not contain a repeating group
• To convert, you must expand the table’s primary key to include the primary key of the repeating group

Example:


Normalization: Second Normal Form

• To understand second normal form (2NF), you must understand the concept of functional dependence
• Field X is functionally dependent on field Y if the value of field X depends on the value of field Y
• A standard process exists for converting a table from 1NF to 2NF

1. Create and name a separate table for each field in the existing primary key
2. Create a new table for each possible combination of the original primary key fields
3. Study the three tables and place each field with its appropriate primary key 

• Four kinds of problems are found with 1NF description that do not exist with 2NF

4. Consider the work necessary to change a particular product’s description
5. 1NF tables can contain inconsistent data
6. Adding a new product is a problem
7. Deleting a product is a problem

Normalization: Third Normal Form

• 3NF design avoids redundancy and data integrity problems that still can exist in 2NF designs
• A table design is in third normal form (3NF) if it is in 2NF and if no non-key field is dependent on another non-key field

Example:

To convert the table to 3NF, you must remove all fields from the 2NF table that depend on another non-key field and place them in a new table that uses the non-key field as a primary key

Download PDF File