4NF

1
4NF
2
Project (fragment)
Auth.
PTypes
Employees
MWorks
HasType
Assignment
Dates
Planes
AppliedOn
MServices
States
PTypes(model, capacity,) Planes(regno,
model) Employees(sin,) MWorks(workcode,) Authori
zed(sin,model,workcode) MServices(servno,regno,)
States(state) Dates(datereached) Assignment(servno
, workcode, sin, state, datereached)
3
Problem

• Assignment(servno, workcode, sin, state,
  datereached)

4
Problem

• Assignment(servno, workcode, sin, state,
  datereached)
• We might assert one FD
• servno workcode state ? datereached (functional
  dependency)
• However, this FD doesnt really help here!
• Problem
• In this table, employee's sin numbers are
  independent of the states.
• Thus, for any existing servno, workcode pair, say
  (s,w), the sin numbers of employees assigned to
  (s,w) appear with each of the states that (s,w)
  reaches in all combinations.
• This repetition is unlike redundancy due to FDs,
  of which servno workcode state ? datereached is
  the only one.

5
Tuples Implied by Independence
If we have the blue tuples
Then the red tuples must also be in the relation.
6
Definition of MVD
A multivalued dependency (MVD), denoted by
X -gt-gtY is an assertion
that If two tuples agree on all the attributes
of X, then their Y-components may be swapped, and
the result will be two tuples that are also in
the relation.
7
Example

• Example servno, workcode, sin, state illustrated
  MVD
• servno workcode -gt-gt sin
• and the MVD
• servno workcode -gt-gt state

8
Picture of MVD X -gt-gtY
X Y others equal exchange
9
FDs vs. MVDs

• Every FD is an MVD.
• If X -gtY, then swapping Y s between two tuples
  that agree on X doesnt change the tuples.
• Therefore, the new tuples are surely in the
  relation, and we know X -gt-gtY.

10
Fourth Normal Form

• The redundancy that comes from MVDs is not
  removable by putting the database schema in BCNF.
• There is a stronger normal form, called 4NF, that
  (intuitively) treats MVDs as FDs when it comes
  to decomposition, but not when determining keys
  of the relation.

11
4NF Definition

• A relation R is in 4NF if whenever X
  -gt-gtY is a nontrivial MVD, then X is a
  superkey.
• Nontrivial means that
• Y is not a subset of X, and
• X and Y are not, together, all the attributes.
• Note that the definition of superkey still
  depends on FDs only.

12
BCNF Versus 4NF

• Remember that every FD X -gtY is also an MVD, X
  -gt-gtY.
• Thus, if R is in 4NF, it is certainly in BCNF.
• Because any BCNF violation is a 4NF violation.
• But R could be in BCNF and not 4NF, because
  MVDs are invisible to BCNF.

13
Decomposition and 4NF

• If X -gt-gtY is a 4NF violation for relation R, we
  can decompose R using the same technique as for
  BCNF.
• XY is one of the decomposed relations.
• The other relation has X and all the other
  attributes of R that are not in X or Y.

14
Example

• Assignment(servno, workcode, sin, state,
  datereached)
• FD
• servno workcode state ? datereached
• MVDs
• servno, workcode -gt-gt sin
• servno, workcode -gt-gt state
• Key is servno, workcode, sin, state
• All dependencies violate 4NF.

15
Example, Continued

• Decompose using
• servno workcode state ? datereached
• Assignment1(servno,workcode,state,datereached)
• In 4NF.
• Assignment2(servno,workcode,sin,state)
• Not in 4NF. MVDs
• servno, workcode -gt-gt sin
• servno, workcode -gt-gt state apply.
• No FDs, so all four attributes form the key.

16
Example Decompose Assignment2

• Either MVD
• servno, workcode -gt-gt sin
• servno, workcode -gt-gt state
• tells us to decompose to
• Assignment3(servno,workcode,sin)
• Assignment4(servno,workcode,state)
• The last one (Assignment4) is completely
  contained in Assignment1, and thus, not needed.