Multivalued Dependencies Fourth Normal Form

Multivalued DependenciesFourth Normal Form Tony Palladino 157B

Multivalued Dependencies • Multivalued dependencies exit when the relation contains unnecessary duplication of data. • BCNF is required in order to be in Fourth Normal Form • Also referred to as equality-generating dependencies

Multivalued Dependencies cont. • Given the “non-trivial definition” MVD: A1A2…An => B1B2…Bn then {A1A2…An} is the superkey • A MVD: for A1A2…An => B1B2…Bn for a Relation R is “non-trivial” if1. none of the Bs are among the As2. none of the attributes of R are among the As and Bs • A MVD is “trivial” if it contains all the variations of A1A2…An x B1B2…Bn • A relation cannot be decomposed any further (under 4NF) if it has a trivial MVD

Fourth Normal Fourth:definition • Fourth normal form (or 4NF) requires that there are no non-trivial multi-valued dependencies of attribute sets on something other than a superset of a candidate key. A table is said to be in 4NF if and only if it is in the BCNF and multi-valued dependencies are functional dependencies. The 4NF removes unwanted data structures: multi-valued dependencies • www.wikipedia.org

Fourth Normal Fourth • There is no multivalued dependency in the relation • There are multivalued dependency but the attributes are dependent between themselves • Either of these conditions must hold true in order to be fourth normal form • The relation must also be in BCNF • Fourth normal form differs from BCNF only in that it uses multivalued dependencies

Why was fourth normal form developed? • It was necessary to develop a way to eliminate non-trivial multivalued dependencies. • It was needed in order to be applied to many to many relationships • Without being adjusted for fourth normal form, there may result update anomalies

How to achieve fourth normal form • In order to do so, all unnecessary tuples must be eliminated • This includes those with null values • All MVD’s must be changed from non-trivial to trivial

An Example This relation has extraneous tuples Must be removed to be in 4NF These are both now in fourth normal form

Checking for Multivalued Dependencies • You must check for all like tuples • For example: A->>B In this case you have to calculate for the tuples shown in each chart.

Checking for Multivalued Dependencies, Cont. • There is an easy way to check! First, write the tuples to be compared

Checking for Multivalued Dependencies, Cont. • Next, write the attributes in the multivalued dependency on the chart, underneath their current place in the same order

Next, write the extra attributes below the originals, but swap the order as shown Checking for Multivalued Dependencies, Cont.

Checking for Multivalued Dependencies, Cont. • Then check relation to see if all the dependencies exist Not all exist, so this is not a proper multivalued dependency

Another Example

Armstrong’s Axioms • Reflexivity: if A is a set of attributes, and B belongs to A, then A->B holds true. • Augmentation: if A->B holds, and C is a set of attributes, the AC ->BC holds true. • Complementation: if A->B holds, then A->R-b-a holds true • Transitivity: if A->b holds, and b->c holds, then a->c holds

Multivalued Axioms • Multivalued transitivity: if A->B holds, and B->C holds, then A->CB holds • Replication: if A->B holds, then A->>B • Multivalued Augmentation: if A->B holds, and C belongs to R and D belongs to C, then AC->:BD holds • Coalescence: if A->>B holds,, and C belongs to B, and there is a D such that D belongs to R and D^B = 0, and d->C, then A->C holds

Multivalued Dependencies Fourth Normal Form