Database Normalization and Normal Forms

Database Normalization

• Normalization structures a database table to prevent redundant information.
• Normalized tables are protected from contradictory data.
• Normalized tables are easier to understand
• Normalized tables are easier to enhance and extend
• Normalized tables are protected from insertion, update, and deletion anomalies.
• Normal forms provide criteria to assess the level of danger regarding redundant data.
• Normal forms include First Normal Form, Second Normal Form, Third Normal Form, and so on.
• Achieving a higher normal form provides a greater "safety guarantee" for the database.

First Normal Form

• First Normal Form addresses issues like row order, mixed data types, missing primary keys, and repeating groups.
• Using row order to convey information violates First Normal Form.
• Mixing data types within the same column violates First Normal Form and is generally not allowed in relational databases.
• Tables without a primary key are not in First Normal Form.
• A primary key uniquely identifies a row in the table, and the database platform uses that row to prevent multiple rows with the same key
• Storing a repeating group of data items on a single row violates First Normal Form.

Second Normal Form

• Second Normal Form focuses on how a table's non-key columns relate to the primary key.
• A non-key attribute must be dependent on the entire primary key.
• Tables not in Second Normal Form may have deletion anomalies.
• Tables not in Second Normal Form may have update anomalies where logical inconsistencies arise due to partial updates
• Tables not in Second Normal Form may have insertion anomalies
• Part-key dependencies violate Second Normal Form.
• A table should be created for each "important concept" in the data
• If every attribute depends on the whole primary key, not just part of it, tables are in Second Normal Form.

Third Normal Form

• Third Normal Form addresses transitive dependencies, where a non-key attribute depends on another non-key attribute.
• Transitive dependencies are dependencies that can "hop" or "skip" to another process
• Third Normal Form forbids the dependency of a non-key attribute on another non-key attribute.
• Third Normal Form helps in determining how a player rating translates to a player skill
• Every non-key attribute in a table should depend on the key, the whole key, and nothing but the key.
• If every attribute in a table should depend on the key, the whole key, and nothing but the key, it is known as Boyce-Codd Normal Form.
• The difference between Third Normal Form and Boyce-Codd Normal Form is small making their chances of differentiating very slim
• Tables with multiple overlapping candidate keys may differ between normal forms

Fourth Normal Form

• Fourth Normal Form handles rare instances where Third Normal Form isn't sufficient.
• Introduction of a third color (green) for the "Prairie" birdhouse model requires adding two rows: one for "green bungalow" and one for "green schoolhouse"
• Failure to add both rows leads to a data inconsistency
• Colors should be independent of styles
• Omitting the "green schoolhouse" row implies green is only available for the bungalow style, which is illogical

Multivalued Dependency

• Each Model has a specific set of available Colors
• Each Model has a specific set of available Styles
• Fourth Normal Form dictates that the only allowed multivalued dependencies are those on the key
• If Model is not the key, the table is not in Fourth Normal Form
• Resolve by splitting the data into multiple tables

Fifth Normal Form Example

• Brands: Frosty’s, Alpine, and Ice Queen
• Frosty’s offers vanilla, chocolate, strawberry, and mint chocolate chip
• Alpine offers vanilla and rum raisin
• Ice Queen offers vanilla, strawberry, and mint chocolate chip

Fifth Normal Form Explanation

• Initial data includes brands offering flavors, people liking brands, and people liking flavors
• Create three tables from this information
• To determine specific products liked, use SQL to join tables together
• A table in Fourth Normal Form is in Fifth Normal Form if it can't be described as the logical result of joining other tables

Review of Normal Forms

• Compliance with a particular normal form requires compliance with all lower normal forms

First Normal Form

• No row order to convey information
• No mixing data types within the same column
• A table must have a primary key
• No repeating groups

Second Normal Form

• Each non-key attribute must be dependent on the entire primary key

Third Normal Form

• Each non-key attribute must depend on the key, the whole key, and nothing but the key
• Boyce-Codd Normal Form: Each attribute must depend on the key, the whole key, and nothing but the key

Fourth Normal Form

• The only allowed multivalued dependencies are those on the key

Fifth Normal Form

• The table cannot be described as the logical result of joining other tables together