Database Normalization and Normal Forms

Database Normalization

• Restructures the logical data model to eliminate redundancy
• Organizes data efficiently to reduce potential data anomalies
• Simplifies relations (tables) within the database

Data Anomalies

• Data anomalies are inconsistencies in data that arise from update, insertion, or deletion operations
• Inconsistencies occur if a record is stored in multiple locations and not all copies are updated
• Anomalies can be prevented by implementing normalization through Normal Forms (NF)
• There are seven levels of normalization, but only the first three are covered

Overview of Normal Forms

• Normalization requires understanding the requirements for each Normal Form
• Normal Forms are progressive, meaning to achieve a higher NF, all previous NFs must be satisfied
• To have 3rd NF, a database must have 2nd NF and to achieve 2nd NF, a database must have 1st NF

First Normal Form (1NF)

• 1NF disallows multivalued attributes, composite attributes, and combinations of these
• Only atomic (simple) values are permitted

1NF Considerations

• Schemas that violate 1NF need to be updated
• Relations like the Department relation are not in 1NF because Dlocations is not atomic
• Atomic attributes means "single-valued", rather than combinations

Transforming to 1NF

• Violating attributes can be removed and placed in a separate relation with the primary key
• Expanding keys creates a separate tuple for each non-atomic attribute, but it can introduce redundancy
• Non-atomic attributes cab be be replaced with multiple n-attributes, although this could result in null values

Second Normal Form (2NF)

• Typically used in tables containing a multiple-field primary key (composite key)
• If the primary key contains a single attribute, 2NF does not need to be applied
• Each nonprime attribute must be fully dependent on the entire primary key
• Nonprime attributes violate 2NF if they're only partially dependent on the primary key

Normalizing to 2NF

• Relations are normalized to 2NF by creating new relations
• Nonprime attributes are associated only with the part of the primary key on which they are fully dependent
• This involves the decomposition of relations into three new relations

Third Normal Form (3NF)

• 3NF is based on the concept of transitive dependency
• A relation schema R is in 3NF if it satisfies 2NF and no nonprime attribute of R is transitively dependent on the primary key.
• Transitive dependency where Ssn determines Dmgr_ssn through Dnumber in EMP_DEPT indicates a violation of 3NF
• Dnumber by itself also does not constitute a key, meaning it also violates 3NF

Achieving 3NF

• Relation schema, such as EMP_DEPT, is not in 3NF due to transitive dependency of Dmgr_ssn and Dname on Ssn via Dnumber
• Normalize by decomposing into two 3NF relation schemas and ensuring there are independent facts about employees and departments