What is 5NF in DBMS

The Fifth normal form deals like that 4. Normal form, with multi-valued dependencies.

The prerequisite for the fifth normal form is a relation in the 4th NF all key candidates of the relation also key of the Tbe constants of the relation. This means that the outsourced attributes must be keys to the newly created relations.

Memorandum: In the fifth normal form is it not possible anymore, the Relation types further in To decompose relation types of a lesser degreeso that the original state at any time restored without loss of information can be.

Fifth Codd normal form definition

A relation type is then in the fifth normal form (5NF), if it is in the fourth normal form (4NF) and the following applies to every dependency (R1, R2,…, Rn): - The dependency is trivial or - Every Ri from (R1, R2,…, Rn) is a key candidate of the relation . The fifth normal form (5NF) is also known as the Project Join Normal Form (PJNF).

Fifth normal form example

The table contains information about which product from a manufacturer was purchased by which customer. Each manufacturer offers different products that are bought by different customers.

Manufacturer noProductDescriptionCustomer no
1Pen 1006
1Folder 1007
2Folder 2006
3Copy paper007

None of the customers buy all products from a manufacturer and none of the products are bought by all customers. This means that all attributes of the table are relevant to store the information who bought what from whom.

Lying in relation no multi-valued dependencies because products and customers together represent important information. The Key candidate the table consists of all three attributes. Because of this, three individual relations must be created in order to resolve the polyvalence.

New table: "Manufacturer product"

Manufacturer noProductDec
1Pen 1
1Folder 1
2Folder 2
3Copy paper

New table: "Manufacturer-customer"

ProductDecCustomer no
Pen 1006
Folder 1007
Folder 2006
Copy paper007

New table: "Product-Customer"

Manufacturer noCustomer no

The original relation has been resolved into a trivial dependency. Further dismantling is not possible.

Manufacturer noProductDescriptionCustomer no
1Pen 1006
1Folder 1007
1Folder 1006
1Pen 1007
2Folder 2006
3Copy paper007

At a Join on all tables arise new linesthat did not originally exist in the table. This creates a Loss of information, since it is no longer possible to see what information was included at the beginning.

Goal of the fifth normal form

The Fifth normal form unlike the other normal forms, serves to to discover new information. Correctly applied result new contexts in the data. It is only used when you want to express possible connections from three relationships and do not depict any concrete connections between three tables.

Further articles