3.2. Normal Forms

In this lecture we look at...
[Section notes PDF 121Kb]

3.2.01. Orthogonal design

  • Information Principle:
    • The entire information content of the database is represented in one and only one way, namely as explicit values in column positions in tables
  • Implies that two relations cannot have the same meaning
    • unless they explicitly have the same design/attributes (including name)

3.2.02. Normalization

  • Reduced redundancy
  • Organised data efficiently
  • Improves data consistency
    • Reduces chance of update anomalies
    • Data duplicated, then updated in only one location
  • Only duplicate primary key
    • All non-key data stored only once
  • Data spread across multiple tables, instead of one Universal relation R

3.2.03. Good or bad?

  • Depends on Application
  • OLTP (Transaction processing)
    • Lots of small transactions
    • Need to execute updates quickly
  • OLAP (Analytical processing/DSS)
    • Largely Read-only
    • Redundant data copies facilitate Business Intellegence applications, e.g. star schema (later)
  • 3NF considered ‘normalised’
    • save special cases

3.2.04. Normal forms (1NF)

  • First Normal form (1NF)
    • Disallows multivalued attributes
    • Part of the basic relational model
  • Domain must include only atomic values
    • simple, indivisible
  • Value of attribute-tuple in extension of schema
  • t[Ai] ∈ (Ai)

3.2.05. Normalisation (1NF)

  • Remove fields containing comma separated lists
  • Multi-valued attribute (AMV) of Ri
  • Create new relation (RNEW)
    • with FK to Ri[PK]

3.2.06. Normalisation (1NF)

  • On weak entity
  • On strong entity

weak entity example
strong entity example

3.2.07. Normal forms (2NF)

  • A relation Ri is in 2NF if:
    • Every nonprime attribute A in Ri is
    • fully functionally dependent on 1y key of R
  • If all keys are singletons, guaranteed
  • If Ri has composite key are
    • all non-key attributes fully functionally dependent
    • on all attributes of composite key?

3.2.08. Normal forms (2NF)

  • Second normal form (2NF)
    • Full functional dependency X → Y
      • A ∈ X, (X - {A}) ¬→ Y
  • If any attribute A is removed from X
  • Then X → Y no longer holds
    • Partial functional dependency
    • A ∈ X, (X - {A}) → Y

3.2.09. Normal forms (2NF)

  • In context
    • Not 2NF: AB → C, A → C
      • AB → C is not in 2NF, because B can be removed
    • Not 2NF: AB → CDE, B → DE
      • because attributes D&E are dependent on part of the composite key (B of AB), not all of it

3.2.10. Normalisation (2NF)

  • Split attributes not depended on all of the primary key into separate relations

3.2.11. Normal forms (BCNF)

  • Boyce-Codd Normal form (BCNF)
    • Simpler, stricter 3NF
      • BCNF → 3NF
      • 3NF does not imply BCNF
    • nontrivial functional dependency X → Y
    • Then X must be a superkey

3.2.12. Normal forms (3NF)

  • Third Normal form (3NF)
  • Derived/based on transitive dependency
  • For all nontrivial functional dependencies
    X → A
  • Either X must be a superkey
  • Or A is a prime attribute
    (member of a key)

3.2.13. Normal forms in context

  • AB → C, C → D, D → A
  • In context
    • 3NF? Yes
      • Because AB is a superkey and
      • D and A are prime attributes
    • BCNF? No
      • Because C and D are not superkeys
      • (even though AB is)

3.2.14. Normalisation (3NF)

  • CarMakes not in 3NF because:
    • singleton key A
    • non-trivial fd B → C
      • B not superkey, C not prime attribute