Fourth Normal Form (4NF) and Fifth Normal Form (5NF) - Administration
Put simply, normalization is an attempt to make sure you do not destroy true data or create false data in your database. Errors are avoided by representing a fact in the database one way, one time, and in one place. Duplicate data is a problem as old as data processing. Efficient and accurate data processing relies on the minimizing redundant data and maximizing data integrity. Normalization and the Normal Forms (NF) are efforts to achieve these two core objectives of data processing. This article will examine the concept of normalization in-depth.
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.