Multivalued Dependencies Salman Azhar

1
Multi-valued Dependencies Salman Azhar

• Multi-valued Dependencies
• Fourth Normal Form

These slides use some figures, definitions, and
explanations from Elmasri-Navathes Fundamentals
of Database Systemsand Molina-Ullman-Widoms
Database Systems
2
A New Form of Redundancy

• Multi-valued dependencies (MVDs) express a
  condition among tuples of a relation that exists
  when the table (relation) is trying to represent
• more than one many-many relationship.
• then certain columns (attributes) become
  independent of one another
• and their values must appear in all combinations.

3
Example

• Customers(name, addr, phones, sodasLiked)
• A customers phones are independent of the sodas
  they like.
• Many-many relations
• Customers ? Phones Customers ?
  Sodas
• Each phone appear with each soda in all
  combinations.
• E.g., For 3 phones (Home, Work, Cell) and 10
  sodas, we need 30 tuples
• Repetition is unlike FD redundancy.
• There is only one FD name ? addr

4
Tuples Implied by Independence
If we have pink tuples, then Then the blue tuples
must also be in the relation
name addr phones sodasLiked sue a p1
s1 sue a p2 s2
name-phone and name-soda relations are
independentThe green relationship implies
existence of blue relations
5
Definition of MVD

• A multi-valued dependency (MVD) X ?? Y
• if an assertion that if two rows of a table agree
  on all the attributes of X,
• then their components may be swapped in the set
  of attributes Y,
• and the result will be two tuples that are also
  in the relation.

6
Example

• The name-addr-phones-sodasLiked example
  illustrated two MVDs
• name ?? phones
• name ?? sodasLiked.

7
Picture of MVD X ?? Y
X Y others equal exchange
Other attributes get copied remain the same
We must exchange all components of Ys (not just
some)
If there is an FD X ? Y, then swapping Ys
components doesnt change anything.
Every FD is an MVD.
8
MVD Rules

• Every FD is an MVD.
• If X ?Y, 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 ??Y.
• Complementation
• If X ??Y, and Z is all the other attributes
  (complement of X and Y) then X ??Z.
• Reason Swapping Ys that agree on X is the same
  effect as swapping Zs. ?

9
Splitting Doesnt Hold

• Recall in FDs we could split right side, but
  could NOT split left side
• Recall FD A, B ? C does not imply A?C and B?C
• Recall FD A? B, C does imply A?B and A?C
• MVDs, we cannot generally split the left side
• BUT, we cannot split the right side EITHER!!!
• E.g., name ?? areaCode phone
• in [phone] and (812)-555-1111, you cant
  swap area codes
• sometimes you have to deal with several
  attributes on the right side.

10
Example

• Consider phone has been split into area code and
  number and adding manf giving the following
  relation
• Customers(name, areaCode, phone, sodasLiked,
  manf)
• We can claim
• A customer can have several phones,
• the number divided between areaCode and phone
• A customer can like several sodas,
• each with its own manufacturer.
• Phones are sodasLiked are independent, so we get
• all possible combinations of areaCode-phone pairs
  sodasLiked-manf pairs

11
Example, Continued

• Since the areaCode-phone pairs for a customer are
  independent of the sodasLiked-manf pairs, we
  expect the following MVDs
• name ?? areaCode phone
• name ?? sodasLiked manf
• Observe that we cant split the right hand sides
• areaCode and phone are linked
• sodasLiked and manf are linked
• in [phone] and (812)-555-1111, you cant
  swap area codes!!!

12
Example Data
Here is possible data satisfying these
MVDs name areaCode phone sodasLiked manf Sue 812
555-1111 Pepsi PepsiCo Sue 812 555-1111 Sprite
CocaCola Sue 408 555-9999 Pepsi PepsiCo Sue 40
8 555-9999 Sprite CocaCola
We cannot swap area codes or phones by
themselves. Swapping (812) And (408) gives us
incorrect phone numbers Note neither name ??
areaCode nor name ?? phone holds for
this relation.
Now all possible combinations are
represented. Swapping any pairs does not yield
new rows.
13
Fourth Normal Form

• The redundancy caused by MVDs cant be removed
  by transforming the database schema to BCNF.
• There is a stronger normal form, called 4th
  Normal for (4NF), that (intuitively)
• treats MVDs as FDs when it comes to decomposition
• but not when determining keys of the relation.

14
4NF Definition

• A relation R is in 4NF if whenever X ??Y is a
  nontrivial MVD, then X is a superkey.
• Nontrivial means that
• Y is not a subset of X
• (swapping components does not change tuples)
• X and Y are not, together, all the attributes.
• (swapping components yields the same tuples)
• Note that the definition of superkey still
  depends on FDs only.

15
BCNF Versus 4NF

• Remember that
• Every FD X ? Y is also an MVD, X ?? Y.
• Thus, if R is in 4NF, it is certainly in BCNF.
• Because any BCNF violation is a 4NF violation.
• However, R could be in BCNF and not 4NF, because
  MVDs are invisible to BCNF.

16
Decomposition and 4NF

• If X ?? Y 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.
• All but (Y X) is the other.

17
Example

• Customers(name, addr, phones, sodasLiked)
• FD name ? addr
• MVDs name ?? phones
• name ?? sodasLiked
• Key is name, phones, sodasLiked.
• All dependencies violate 4NF because name is not
  a superkey.

18
Example, Continued

• Decompose using name ? addr
• Customers1(name, addr)
• In 4NF, only dependency is name ? addr.
• Customers2(name, phones, sodasLiked)
• Not in 4NF because MVDs
• name ?? phones and name ?? sodasLiked apply.
• No FDs, so all three attributes form the key.

19
Example Decompose Customers2

• Recall
• Customers2(name, phones, sodasLiked)
• Either MVD
• name ?? phones
• name ?? sodasLiked
• tells us to decompose to
• Customers3(name, phones)
• Customers4(name, sodasLiked)

20
Example Decompose Customers2

• 4th Normal Form Consists of
• Customers1(name, addr)
• Contact
• Customers3(name, phones)
• Phone
• Customers4(name, sodasLiked)
• Likes
• The following FD and MVDs are satisfied
• FD name ? addr
• MVDs name ?? phones
• name ?? sodasLiked

21
Continue Normal Forms

• 5th Normal Form
• 6th Normal Form
• 7th Normal Form

22
Salman Teaching 4th Normal Form

• How would I look teaching 937th Normal Form?

23
Salman Teaching 957th Normal Form