Fifth normal form
Encyclopedia
Fifth normal form also known as Project-join normal form (PJ/NF) is a level of database normalization
designed to reduce redundancy in relational databases recording multi-valued facts by isolating semantically related multiple relationships. A table
is said to be in the 5NF if and only if
every join dependency
in it is implied by the candidate key
s.
A join dependency
*{A, B, … Z} on R is implied by the candidate key
(s) of R if and only if
each of A, B, …, Z is a superkey
for R.
|+ Travelling Salesman Product Availability By Brand
|-
! Travelling Salesman
! Brand
! Product Type
|-
| Jack Schneider
| Acme
| Vacuum Cleaner
|-
| Jack Schneider
| Acme
| Breadbox
|-
| Willy Loman
| Robusto
| Pruning Shears
|-
| Willy Loman
| Robusto
| Vacuum Cleaner
|-
| Willy Loman
| Robusto
| Breadbox
|-
| Willy Loman
| Robusto
| Umbrella Stand
|-
| Louis Ferguson
| Robusto
| Vacuum Cleaner
|-
| Louis Ferguson
| Robusto
| Telescope
|-
| Louis Ferguson
| Acme
| Vacuum Cleaner
|-
| Louis Ferguson
| Acme
| Lava Lamp
|-
| Louis Ferguson
| Nimbus
| Tie Rack>
The table's predicate is: Products of the type designated by Product Type, made by the brand designated by Brand, are available from the travelling salesman designated by Travelling Salesman.
In the absence of any rules restricting the valid possible combinations of Travelling 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 Travelling Salesman has certain Brands and certain Product Types in his repertoire. If Brand B is in his repertoire, and Product Type P is in his repertoire, then (assuming Brand B makes Product Type P), the Travelling Salesman must offer products of Product Type P made by Brand B.
In that case, it is possible to split the table into three:
|+ Product Types By Travelling Salesman
|-
! Travelling Salesman
! Product Type
|-
| Jack Schneider
| Vacuum Cleaner
|-
| Jack Schneider
| Breadbox
|-
| Willy Loman
| Pruning Shears
|-
| Willy Loman
| Vacuum Cleaner
|-
| Willy Loman
| Breadbox
|-
| Willy Loman
| Umbrella Stand
|-
| Louis Ferguson
| Telescope
|-
| Louis Ferguson
| Vacuum Cleaner
|-
| Louis Ferguson
| Lava Lamp
|-
| Louis Ferguson
| Tie Rack>
|+ Brands By Travelling Salesman
|-
! Travelling Salesman
! Brand
|-
| Jack Schneider
| Acme
|-
| Willy Loman
| Robusto
|-
| Louis Ferguson
| Robusto
|-
| Louis Ferguson
| Acme
|-
| Louis Ferguson
| Nimbus>
|+ Product Types By Brand
|-
! Brand
! Product Type
|-
| Acme
| Vacuum Cleaner
|-
| Acme
| Breadbox
|-
| Acme
| Lava Lamp
|-
| Robusto
| Pruning Shears
|-
| Robusto
| Vacuum Cleaner
|-
| Robusto
| Breadbox
|-
| Robusto
| Umbrella Stand
|-
| Robusto
| Telescope
|-
| Nimbus
| Tie Rack>
Note how this setup helps to remove redundancy. Suppose that Jack Schneider starts selling Robusto's products. In the previous setup we would have to add two new entries since Jack Schneider is able to sell two Product Types covered by Robusto: Breadboxes and Vacuum Cleaners. With the new setup we need only add a single entry (in Brands By Travelling Salesman).
table not conform to 5NF. 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.
Database normalization
In the design of a relational database management system , the process of organizing data to minimize redundancy is called normalization. The goal of database normalization is to decompose relations with anomalies in order to produce smaller, well-structured relations...
designed to reduce redundancy in relational databases recording multi-valued facts by isolating semantically related multiple relationships. A table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
is said to be in the 5NF if and only if
If and only if
In logic and related fields such as mathematics and philosophy, if and only if is a biconditional logical connective between statements....
every join dependency
Join dependency
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 T...
in it is implied by the candidate key
Candidate key
In the relational model of databases, a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that# the relation does not have two distinct tuples In the relational model of databases, a candidate key of a relation is a minimal superkey for that...
s.
A join dependency
Join dependency
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 T...
*{A, B, … Z} on R is implied by the candidate key
Candidate key
In the relational model of databases, a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that# the relation does not have two distinct tuples In the relational model of databases, a candidate key of a relation is a minimal superkey for that...
(s) of R if and only if
If and only if
In logic and related fields such as mathematics and philosophy, if and only if is a biconditional logical connective between statements....
each of A, B, …, Z is a superkey
Superkey
A superkey is defined in the relational model of database organization as a set of attributes of a relation variable for which it holds that in all relations assigned to that variable, there are no two distinct tuples that have the same values for the attributes in this set...
for R.
Example
Consider the following example:|-
! Travelling Salesman
! Brand
! Product Type
|-
| Jack Schneider
| Acme
| Vacuum Cleaner
|-
| Jack Schneider
| Acme
| Breadbox
|-
| Willy Loman
| Robusto
| Pruning Shears
|-
| Willy Loman
| Robusto
| Vacuum Cleaner
|-
| Willy Loman
| Robusto
| Breadbox
|-
| Willy Loman
| Robusto
| Umbrella Stand
|-
| Louis Ferguson
| Robusto
| Vacuum Cleaner
|-
| Louis Ferguson
| Robusto
| Telescope
|-
| Louis Ferguson
| Acme
| Vacuum Cleaner
|-
| Louis Ferguson
| Acme
| Lava Lamp
|-
| Louis Ferguson
| Nimbus
| Tie Rack>
The table's predicate is: Products of the type designated by Product Type, made by the brand designated by Brand, are available from the travelling salesman designated by Travelling Salesman.
In the absence of any rules restricting the valid possible combinations of Travelling 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 Travelling Salesman has certain Brands and certain Product Types in his repertoire. If Brand B is in his repertoire, and Product Type P is in his repertoire, then (assuming Brand B makes Product Type P), the Travelling Salesman must offer products of Product Type P made by Brand B.
In that case, it is possible to split the table into three:
|-
! Travelling Salesman
! Product Type
|-
| Jack Schneider
| Vacuum Cleaner
|-
| Jack Schneider
| Breadbox
|-
| Willy Loman
| Pruning Shears
|-
| Willy Loman
| Vacuum Cleaner
|-
| Willy Loman
| Breadbox
|-
| Willy Loman
| Umbrella Stand
|-
| Louis Ferguson
| Telescope
|-
| Louis Ferguson
| Vacuum Cleaner
|-
| Louis Ferguson
| Lava Lamp
|-
| Louis Ferguson
| Tie Rack>
|-
! Travelling Salesman
! Brand
|-
| Jack Schneider
| Acme
|-
| Willy Loman
| Robusto
|-
| Louis Ferguson
| Robusto
|-
| Louis Ferguson
| Acme
|-
| Louis Ferguson
| Nimbus>
|-
! Brand
! Product Type
|-
| Acme
| Vacuum Cleaner
|-
| Acme
| Breadbox
|-
| Acme
| Lava Lamp
|-
| Robusto
| Pruning Shears
|-
| Robusto
| Vacuum Cleaner
|-
| Robusto
| Breadbox
|-
| Robusto
| Umbrella Stand
|-
| Robusto
| Telescope
|-
| Nimbus
| Tie Rack>
Note how this setup helps to remove redundancy. Suppose that Jack Schneider starts selling Robusto's products. In the previous setup we would have to add two new entries since Jack Schneider is able to sell two Product Types covered by Robusto: Breadboxes and Vacuum Cleaners. With the new setup we need only add a single entry (in Brands By Travelling Salesman).
Usage
Only in rare situations does a 4NFFourth normal form
Fourth normal form is a normal form used in database normalization. Introduced by Ronald Fagin in 1977, 4NF is the next level of normalization after Boyce–Codd normal form . Whereas the second, third, and Boyce–Codd normal forms are concerned with functional dependencies, 4NF is concerned with a...
table not conform to 5NF. 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.
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
- Advanced Normalization