Fifth normal form (5NF), also known as projection–join normal form (PJ/NF), is a level of
database normalization
Database normalization or database normalisation (see spelling differences) is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. ...
designed to remove redundancy in relational databases recording multi-valued facts by isolating
semantically
Semantics (from grc, σημαντικός ''sēmantikós'', "significant") is the study of reference, meaning, or truth. The term can be used to refer to subfields of several distinct disciplines, including philosophy, linguistics and comput ...
related multiple relationships. A
table
Table may refer to:
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (landform), a flat area of land
* Table (information), a data arrangement with rows and columns
* Table (database), how the table data ...
is said to be in the 5NF
if and only if
In logic and related fields such as mathematics and philosophy, "if and only if" (shortened as "iff") is a biconditional logical connective between statements, where either both statements are true or both are false.
The connective is bicondi ...
every non-trivial
join dependency In database theory, a join dependency is a constraint on the set of legal relations over a database scheme. A table T is subject to a join dependency if T can always be recreated by joining multiple tables each having a subset of the attributes of ...
in that table is implied by the
candidate key A candidate key, or simply a key, of a relational database is a minimal superkey. In other words, it is any set of columns that have a unique combination of values in each row (which makes it a superkey), with the additional constraint that removi ...
s. It is the final normal form as far as removing redundancy is concerned.
A
6NF also exists, but its purpose is not to remove redundancy and it is therefore only adopted by a few
data warehouse
In computing, a data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for Business reporting, reporting and data analysis and is considered a core component of business intelligence. DWs are central Repos ...
s, where it can be useful to make tables irreducible.
A join dependency * on R is implied by the candidate key(s) of R
if and only if
In logic and related fields such as mathematics and philosophy, "if and only if" (shortened as "iff") is a biconditional logical connective between statements, where either both statements are true or both are false.
The connective is bicondi ...
each of A, B, …, Z is a
superkey
In the relational data model a superkey is a set of attributes that uniquely identifies each tuple of a relation. Because superkey values are unique, tuples with the same superkey value must also have the same non-key attribute values. That is, ...
for R.
The fifth normal form was first described by
Ronald Fagin
Ronald Fagin (born 1945) is an American mathematician and computer scientist, and IBM Fellow at the IBM Almaden Research Center. He is known for his work in database theory, finite model theory, and reasoning about knowledge.
Biography
Ron ...
in his 1979 conference paper ''Normal forms and relational database operators''.
Example
Consider the following example:
The table's predicate is: products of the type designated by ''product type'', made by the brand designated by ''brand'', are available from the traveling salesman designated by ''traveling salesman''.
The primary key is the composite of all three columns. Also note that the table is in
4NF, since there are no
multivalued dependencies In database theory, a multivalued dependency is a full constraint between two sets of attributes in a relation.
In contrast to the functional dependency, the multivalued dependency requires that certain tuples be present in a relation. Therefore, ...
(
2-part join dependencies) in the table: no column (which by itself is not a candidate key or a superkey) is a determinant for the other two columns.
In the absence of any rules restricting the valid possible combinations of traveling salesman, brand, and product type, the three-attribute table above is necessary in order to model the situation correctly.
Suppose, however, that the following rule applies: ''A traveling salesman has certain brands and certain product types in their repertoire. If brand B1 and brand B2 are in their repertoire, and product type P is in their repertoire, then (assuming brand B1 and brand B2 both make product type P), the traveling salesman must offer products of product type P those made by brand B1 and those made by brand B2.''
In that case, it is possible to split the table into three:
In this case, it's impossible for Louis Ferguson to refuse to offer vacuum cleaners made by Acme (assuming Acme makes vacuum cleaners) if he sells anything else made by Acme (lava lamp) and he also sells vacuum cleaners made by any other brand (Robusto).
Note how this setup helps to remove redundancy. Suppose that Jack Schneider starts selling Robusto's products breadboxes and vacuum cleaners. In the previous setup we would have to add two new entries one for each product type (
, ). With the new setup we need to add only a single entry () in "brands by traveling salesman".
Usage
Only in rare situations does a 4NF table not conform to 5NF. For instance, when the decomposed tables are cyclic. These are situations in which a complex real-world constraint governing the valid combinations of attribute values in the 4NF table is not implicit in the structure of that table. If such a table is not normalized to 5NF, the burden of maintaining the logical consistency of the data within the table must be carried partly by the application responsible for insertions, deletions, and updates to it; and there is a heightened risk that the data within the table will become inconsistent. In contrast, the 5NF design excludes the possibility of such inconsistencies.
A table T is in fifth normal form (5NF) or projection-join normal form (PJ/NF) if it cannot have a lossless decomposition into any number of smaller tables. The case where all the smaller tables after the decomposition have the same candidate key as the table T is excluded.
See also
* Attribute–value system An attribute–value system is a basic knowledge representation framework comprising a table with columns designating "attributes" (also known as "properties", "predicates", "features", "dimensions", "characteristics", "fields", "headers" or "indepe ...
References
Further reading
* Kent, W. (1983)
A Simple Guide to Five Normal Forms in Relational Database Theory
', Communications of the ACM, vol. 26, pp. 120–125.
* Date, C. J., & Darwen, H., & Pascal, F.
Database Debunkings
'.
*
{{Database normalization
5NF Fifth normal form (5NF), also known as projection–join normal form (PJ/NF), is a level of database normalization designed to remove redundancy in relational databases recording multi-valued facts by isolating semantically related multiple relation ...
de:Normalisierung (Datenbank)#Fünfte Normalform (5NF)