Eliminating Anomalies from Multivalued Dependencies (4NF, 5NF)

• Multi-Valued Dependencies.
  • This work was research by Fagen in 1977
  • In 1NF, a attribute cannot have a set of values.
  • Consider:

  • BranchNo	StaffName	OwnerName
    B0003	Ann Beech	Carol Farrell
    	David Ford	Tina Murphay

  • Probably becomes

  • BranchNo	StaffName	OwnerName
    B0003	Ann Beech	Carol Farrell
    B0003	David Ford	Carol Farrell
    B0003	Ann Beech	Tina Murphy
    B0003	David Ford	Tina Murphy

  • BRANCH_STAFF(BranchNo,StaffName, OwnerName)
  • Since there is no direct relationship between the Staff member and the Owner.
  • This represents a multi-valued dependency.
  • BranchNo →→ StaffName
  • BranchNo →→ OwnerName
  • A Multi-valued dependency (MVD) represents a dependency between attributes(for example, A, B and C) in a relation, such that for each value of A there is a set of values for B and a set of values for C. However, the set of values for B and C are independent of each other.
  • A relation A →→B is trivial if R=(A,B)
  • A relation A →→B is trivial if B ⊂ A
  • A relation in BCNF and containing no nontrivial multi-valued dependencies is in fourth normal form (4NF)
  • Is the above table in 4NF?
  • How could an insertion Anomaly occur?
  • We can place this into 4NF by moving all mult-value relations to a new table.
    • BRANCH_STAFF(BranchNo, StaffName)
    • BRANCH_OWNER(BranchNo, OwnerName)
  • Is this still subject to update anomalies?
  • BookExample:
    • EMPLOYEE_DEGREE_SIBLING(EmployeeName, EmployeeDegree, EmployeeSibling)

    • EmployeeName	EmployeeDegree	EmployeeSibling
      Chau	BS	Eileen
      Chau	BS	Jonathan
      Green	BS	Niki
      Green	MS	Niki
      Green	PhD	Niki
      Jones	AA	Frank
      Jones	AA	Fred
      Jones	AA	Sally
      Jones	BA	Frank
      Jones	BA	Fred
      Jones	BA	Sally

• Fifth normal form deals with table joins
  • Sometimes there is a problem rejoining a table that has been split.
  • If a table is in 4NF it is probably in 5NF (our author)
  • 5NF is beyond the scope of the book.
• The book states that Domain/Key normal form is equivalent to their definition of BCNF.