DB2 (Normalization)

greenspun.com : LUSENET : DBAzine : One Thread

1) Define FOURTH Normal form and FIFTH Normal form ?

2) What is advantages of using FIFTH Normal form ?

-- Rajeeva R (rajeeva_r@rediffmail.com), December 11, 2002


The following answer is summarized from my book, Database administration: The complete guide to practices and procedures (http://www.amazon.com/exec/obidos/ASIN/0201741296/mullinassoci- 20/103-4851251-5332609). The book goes into more details on normalization and walks through examples.

1NF specifies that all underlying domains contain atomic values only. So 1NF eliminates repeating groups. 2NF specifies that every non-key attribute is fully dependent on the key. So 2NF eliminates functional dependencies. 3NF specifies that every non-key attribute is non- transitively dependent on the primary key. So 3NF eliminates functional dependencies. Normalization past 3NF does not occur often in normal practice because most tables in 3NF are usually also in 5NF. The additional normal forms are: * Boyce Codd Normal Form (BCNF) is a further refinement of 3NF. In his later writings Codd refers to BCNF as 3NF. A row is in BCNF if every determinant is a candidate key. Most entities in 3NF are already in BCNF. * An entity is in 4NF if it is in 3NF and has no multiple sets of multi-valued dependencies. In other words, 4NF states that no entity can have more than a single one-to-many relationship within an entity if the one-to-many attributes are independent of each other. * 5NF specifies that every join dependency for the entity must be a consequence of its candidate keys.

A complete definition of 4NF is given here: http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node16.html

A walkthru of normalization thru 5NF is given here: http://syllabus.syr.edu/ECS/ilcoman/cse581/chap6p4mod/sld001.htm

-- Craig S. Mullins (craig_mullins@bmc.com), January 16, 2003.

Moderation questions? read the FAQ