StaffBranch vs Staff and Branch

StaffBranch
staffNo sName Position Salary branchNo bAddress
SL21 John White Manager 30000 B005 22 Deer Rd, London
SG37 Ann Beech Assistant 12000 B003 163 Main St, Glasgow
SG14 David Ford Supervisor 18000 B003 163 Main St, Glasgow
SA9 Mary Howe Assistant 9000 B007 16 Argyll St, Aberdeen
SG5 Susan Brand Manager 24000 B003 163 Main St, Glasgow
SL41 Julie Lee Assistant 9000 B005 22 Deer Rd, London

Staff
staffNo sName Position Salary
SL21 John White Manager 30000
SG37 Ann Beech Assistant 12000
SG14 David Ford Supervisor 18000
SA9 Mary Howe Assistant 9000
SG5 Susan Brand Manager 24000
SL41 Julie Lee Assistant 9000
Branch
branchNo bAddress
B005 22 Deer Rd, London
B003 163 Main St, Glasgow
B007 16 Argyll St, Aberdeen


Data Redundancy and Update Anomalies

  • Insertion anomalies
  • Deletion anomalies
  • Modificatiob anomalies

    Identifying a functional dependency

    (a) staffNo -> Position -- true
    Position -> staffNo -- false: Manager -> SL21 and Manager -> SG5

    (b) staffNo -> sName -- true
    sName -> staffNo -- false WHY?

    A set of functional dependencies for the StaffBranch relation:

  • staffNo -> sName, Position, Salary, branchNo, bAddress
  • branchNo -> bAddress
  • bAddress -> branchNo
  • branchNo, Position -> Salary
  • bAddress, Position -> Salary

    The minimal set of functional dependencies of the StaffBranch relation

  • staffNo -> sName
  • staffNo -> Position
  • staffNo -> Salary
  • staffNo -> branchNo
  • staffNo -> bAddress
  • branchNo -> bAddress
  • bAddress -> branchNo
  • branchNo, Position -> Salary