Fourth Normal Form

Consider the BranchStaffOwner relation shown below, which displays the names of members of staff (sName) and property owners (oName) at each branch office (branchNo). In this example, assume that staff number name uniquely identifies each each member of staff and that the owner name uniquely identifies each owner.

BranchStaffOwner
branchNo sName oName
B003 Ann Beech Carol Farrel
B003 David Ford Carol Farrel
B003 Ann Beech Tina Murphy
B003 David Ford Tina Murphy

This strange table could be obtained after normalization from this table:
BranchStaffOwner
branchNo Staff Owners
B003 {Ann Beech, David Ford} {Carol Farrel, Tina Murphy}

In this example, members of staff called Ann Beech and David Ford work at branch B003, and property owners Carol Farrel and Tina Murphy are registered at branch B003, but there is no direct relationship between members of staff and property owners, therefore we must create a tuple for every combination to ensure that the relation is consistent.

Please note that the following nontrivial MVD are hold:

  • branchNo ->> sName
  • branchNo ->> oName
    To transform this relation into 4NF we have to decompose it into two relations:

    BranchStaff
    branchNo sName
    B003 Ann Beech
    B003 David Ford
    BranchOwner
    branchNo oName
    B003 Carol Farrel
    B003 Tina Murphy

    For a detailed discussion on 4NF we refer to

  • Date C.J. An Introduction to Database Systems 7th ed. Reading MA: Addisson-Wesley, 2000.
  • Elmarsi R and Navathe S. Foundamentsls of Database Systems 3rd edn. Addisson-Wesley, 2000.
  • Hawryszkiewyecz I.T. Database Analysis and Design 2nd edn. New York, NY: Macmillan Publishing Company, 1991.