4N and the Normalization of Multivalue Dependency

1
4N and the Normalization of Multivalue Dependency

• By
• Laith Alnagem

2
Rules

• Fourth Normal Form - if R is 3N and BCNF
• - given the non-trivial MVD A1A2An Þ
  B1B2Bn A1A2An is a superkey
• A MVD A1A2An Þ B1B2Bn for a Relation R is
  non-trivial if 1. none of the Bs are among
  the As 2. Not all of the attributes of R are
  among the As and Bs
• A MVD is trivial if it contains all the
  variations of A1A2An x B1B2Bn.
• or a?? ß is ß Í a or ß È a

3
Formal Definition

• Let R be a relation schema and let a Í Rand ß Í R
  . The multivalued dependencya ?? ßholds on R
  if, in any legal relation r(R), for all pairs of
  tuples t1 and t2 in r such that t1a t2a,
  there exist tuples t3 and t4 in r such thatt1a
  t2a t3a t4at3ß t1ßt3R - ß
  t2R - ßt4ß t2ßt4R - ß t1R - ß1

4
Multivalued Dependency in 4N

• A table with a multivalued dependency is one
  where the existence of more than one independent
  many-to-many relationships in a table causes
  redundancy and it is this redundancy which is
  removed by fourth normal form.

5
Example
6
Example (cont)

• It is 1N, 2N, 3N and BCNF
• But because the varieties of pizza a restaurant
  offers are independent from the areas to which
  the restaurant delivers, there is redundancy in
  the table
• for example, we are told three times that A1
  Pizza offers Stuffed Crust, and if A1 Pizza start
  producing Cheese Crust pizzas then we will need
  to add multiple records, one for each of A1
  Pizza's delivery areas.

7
The Fix
8
(No Transcript)
9
A little thought

• Q
• What if the pizza varieties offered by a
  restaurant sometimes did vary from one delivery
  area to another?

10

• A
• Then the original three-column table would
  satisfy 4NF

11
(No Transcript)
12

• A.
• course ?? book
• course ?? lecturer

13
Conclusions

• Databases with multivalued dependencies exhibit
  redundancy.
• In database normalization, fourth normal form
  requires that databases have no multivalued
  dependencies.

14
Extra

• Transitivity, Reflexivity, Complementation,
  Replication, Augmentation and a few more are all
  properties of 4th normal form

15
References

• Multivalued dependency
• From Wikipedia, the free encyclopedia
• Revision as of 1159, 24 October 2006 by
  GurchBot (Talk contribs)(diff) ? Older
  revision Current revision Newer revision ?
  (diff)
• DataBase System Concepts (5th Edition) Ch-7
• Silberschatz, Korth, Sudarshan
• NY, 2006