Database Normalization - Fourth Normal Form (4NF) and Fifth Normal Form (5NF) (
Page 5 of 6 )
Fourth Normal Form (4NF) and Fifth Normal Form (5NF)
Fourth normal form requires that a table be BCNF and contain no multi-valued dependencies. 4NF solves the problem of multivalued dependencies, which we would encounter with our above Sales table when a customer purchased multiple products with a single order - we would have numerous rows that were dependent on one another without any clear definition of that dependency in the table.
Fifth normal form, also known as join-projection normal form (JPNF), states that no non-trivial join dependencies exist. 5NF states that any fact should be able to be reconstructed without any anomalous results in any case, regardless of the number of tables being joined. A 5NF table should have only candidate keys and it's primary key should consist of only a single column.
The problem with these forms is the size of the joins that are required to reconstruct any non-trivial data.We can rely on views and procedures to simplify them but the underlying data still ends up very complex. There are also performance issues to consider - which is why 4NF and 5NF are often academic. In most cases, 3NF (or BCNF) are ideal.