Normalization IINormalization II

Boyce–Codd Normal Form Boyce–Codd Normal Form (BCNF)(BCNF)

Based on functional dependencies that Based on functional dependencies that take into account all candidate keys in a take into account all candidate keys in a relation, however BCNF also has relation, however BCNF also has additional constraints compared with additional constraints compared with general definition of 3NF.general definition of 3NF.

BCNF - A relation is in BCNF if and only BCNF - A relation is in BCNF if and only if every determinant is a candidate key.if every determinant is a candidate key.

Boyce–Codd normal form Boyce–Codd normal form (BCNF)(BCNF)

Difference between 3NF and BCNF is that for a Difference between 3NF and BCNF is that for a functional dependency A functional dependency A B, 3NF allows this B, 3NF allows this dependency in a relation if B is a primary-key dependency in a relation if B is a primary-key attribute and A is not a candidate key. attribute and A is not a candidate key.

Whereas, BCNF insists that for this dependency to Whereas, BCNF insists that for this dependency to remain in a relation, A must be a candidate key. remain in a relation, A must be a candidate key.

Every relation in BCNF is also in 3NF. However, Every relation in BCNF is also in 3NF. However, relation in 3NF may not be in BCNF.relation in 3NF may not be in BCNF.

Boyce–Codd normal form Boyce–Codd normal form (BCNF)(BCNF)

Violation of BCNF is quite rare. Violation of BCNF is quite rare.

Potential to violate BCNF may occur in a Potential to violate BCNF may occur in a relation that:relation that: contains two (or more) composite candidate contains two (or more) composite candidate

keys;keys; the candidate keys overlap (ie. have at least the candidate keys overlap (ie. have at least

one attribute in common).one attribute in common).

Review of Normalization (UNF Review of Normalization (UNF to BCNF)to BCNF)

Review of Normalization (UNF Review of Normalization (UNF to BCNF)to BCNF)

Review of Normalization Review of Normalization (UNF to BCNF)(UNF to BCNF)

Review of Normalization (UNF Review of Normalization (UNF to BCNF)to BCNF)

Fourth Normal Form (4NF)Fourth Normal Form (4NF)

Although BCNF removes anomalies due to Although BCNF removes anomalies due to functional dependencies, another type of functional dependencies, another type of dependency called a multi-valued dependency called a multi-valued dependency (MVD) can also cause data dependency (MVD) can also cause data redundancy. redundancy.

Possible existence of MVDs in a relation is Possible existence of MVDs in a relation is due to 1NF and can result in data due to 1NF and can result in data redundancy.redundancy.

Fourth Normal Form (4NF) - Fourth Normal Form (4NF) - MVDMVD

Dependency between attributes (for Dependency between attributes (for example, A, B, and C) in a relation, such example, A, B, and C) in a relation, such that for each value of A there is a set of that for each value of A there is a set of values for B and a set of values for C. values for B and a set of values for C. However, set of values for B and C are However, set of values for B and C are independent of each other.independent of each other.

Fourth Normal Form (4NF)Fourth Normal Form (4NF)

MVD between attributes A, B, and C in a MVD between attributes A, B, and C in a relation using the following notation:relation using the following notation:

A A B B

A A C C

Fourth Normal Form (4NF)Fourth Normal Form (4NF)

MVD can be further defined as being trivial or MVD can be further defined as being trivial or nontrivial. nontrivial.

– MVD A MVD A B in relation R is defined as B in relation R is defined as being trivial if (a) B is a subset of A being trivial if (a) B is a subset of A oror (b) A (b) A B = R. B = R.

– MVD is defined as being nontrivial if neither MVD is defined as being nontrivial if neither (a) nor (b) are satisfied. (a) nor (b) are satisfied.

– Trivial MVD does not specify a constraint on a Trivial MVD does not specify a constraint on a relation, while a nontrivial MVD does specify a relation, while a nontrivial MVD does specify a constraint.constraint.

Fourth Normal Form (4NF)Fourth Normal Form (4NF)

Defined as a relation that is in BCNF and Defined as a relation that is in BCNF and contains no nontrivial MVDs.contains no nontrivial MVDs.

4NF - Example4NF - Example

Fifth Normal FormFifth Normal Form (5NF) (5NF)

A relation decomposed into two relations must A relation decomposed into two relations must have lossless-join property, which ensures that no have lossless-join property, which ensures that no spurious tuples are generated when relations are spurious tuples are generated when relations are reunited through a natural join.reunited through a natural join.

However, there are requirements to decompose a However, there are requirements to decompose a relation into more than two relations. relation into more than two relations.

Although rare, these cases are managed by join Although rare, these cases are managed by join dependency and fifth normal form (5NF). dependency and fifth normal form (5NF).

Fifth Normal FormFifth Normal Form (5NF) (5NF)

A relation that has no join dependency.A relation that has no join dependency.

5NF - Example5NF - Example

SummarySummary

First normal form- 1NF -All values of the columns are atomic.

Second normal form- 2NF -Must in 1NF. -Each attribute is fully functionally dependent on the entire primary key.

Third normal form- 3NF -Already in 2NF.-Every non-key column is non transitively dependent upon primary key.

Boyce-Codd normal form- BCNF -No non-key dependencies

Forth normal form- 4NF -No multivalued dependencies

Fifth normal form- 5NF -To have relations that cannot be decomposed further