Explain the difference between normal forms

• Poorly designed databases can suffer from:
  • insertion anomaly
    • you cannot add one fact without adding unrelated data
  • deletion anomaly
    • deleting one record may remove other useful data
  • update anomaly
    • repeated data must be updated in multiple places, causing inconsistency
• Normalization is the process of organizing data to:
  • reduce redundancy
  • improve consistency
  • improve integrity
• Functional dependency
  • one attribute uniquely determines another attribute
  • if A -> B, then knowing A tells you exactly one value of B
• Key terms:
  • Partial dependency
    • a non-key attribute depends on only part of a composite key
    • this violates 2NF
  • Transitive dependency
    • a non-key attribute depends on another non-key attribute
    • this violates 3NF
• Related terms:
  • Referential integrity
    • a foreign key must match an existing primary key value in another table, or be NULL
  • Data redundancy
    • the same data is stored in multiple places

Normal forms

• UNF (Unnormalized Form)
  • repeating groups may exist
  • fields may contain multiple values
  • data is not fully atomic
• 1NF
  • all attributes are atomic
  • each row is uniquely identifiable
  • no repeating groups
• 2NF
  • already in 1NF
  • every non-key attribute depends on the whole primary key
  • removes partial dependency
• 3NF
  • already in 2NF
  • no non-key attribute depends on another non-key attribute
  • removes transitive dependency

Progression from UNF to 3NF

• UNF to 1NF
  • remove repeating groups
  • ensure each field contains one value only
• 1NF to 2NF
  • remove attributes that depend on only part of a composite key
• 2NF to 3NF
  • remove transitive dependencies into separate tables