# Fifth Normal Form (5NF) | DBMS

**DBMS 5NF**: In this tutorial, we are going to learn about the **fifth normal form (4NF) in Database Management System**.

Submitted by Anushree Goswami, on August 21, 2019

**Fifth normal form (5NF)** is also known as **project-join normal form (PJ/NF)**. It is designed to minimize redundancy in relational databases by separating semantically connected relationships in multiple formats to store multi-valued facts.

A relation **R** is in **5NF** if and only if every non-trivial join dependency in **R** is implied by the candidate keys of **R**. A relation break up into two relations must contain lossless join Property, which makes certain that no invalid or extra tuples of attributes are created when relations are again joined together through a natural join.

**Properties:**

A relation **R** with attributes, its values and tuples is in **5NF** if and only if the following conditions are satisfied,

- The relation
**R**should be already in 4NF. - The relation
**R**cannot be additionally non loss decomposed (join dependency).

If the relation or table can further decompose to remove redundancy and anomaly, and when the process of rejoining followed the decomposed tables through the means of candidate keys, we should not be losing the original data or any new record set should not arise. Understandably, joining two or more decomposed table should not lose records or create new records.

**Join dependency **

A table or relation can be recreated or re-designed by joining multiple tables and each table of this contain a subset of the attributes and values of the table, then the table is in Join Dependency. It is a generalization of Multivalued Dependency.

If the join of **R1** and **R2** over **S** is equal to relation **R** then we can say that a join dependency exists, where **R1** and **R2** are the decomposition **R1 (P, Q, S)** and **R2 (Q, T)** of a given relation **R (P, Q, S, T)**. As a possibility, **R1** and **R2** are a lossless decomposition of **R**.

Over a relation **R** a Join Dependency **⋈ {R1, R2, ..., Rn}** is said to hold if **R1, R2, ..., Rn** is lossless-join decomposition. The ***(P, Q, S, T), (S, T)** will be a Join Dependency of R if the join of join's attribute is equal to the relation **R**. Here, ***(R1, R2, R3)** is used to specify that relation **R1, R2, R3** and so on is a Join Dependency of **R**.

Join Dependency can be associated to **5NF**, wherein a relation is in **5NF**, only if it is already in 4NF and it cannot be decomposed further.

Contrarily, in the case of functional dependencies, there is no sound and complete axiomatization for join dependencies, however, axiomatization exists for more expressive dependency languages such as full typed dependencies.

On the other hand, the implication of join dependencies is decidable.

**Example:**

Consider a table which contains a record of **Subject**, **Professor** and **Semester** in three columns.

The primary key is the combination of all three columns. Also take an account that the table is in 4NF, as long as there are no multivalued dependencies (2-part join dependencies) in the table: no column (which by itself is not a candidate key or a super key) is a determinant for the other two columns.

If the presence of any properly structured rule is not there to organize the three attributes of the table, it is important to structure them correctly by handling constraint.

In the table, Data structure is taught by Madhurima and Sravan in semester 3, Automata by Abhijeet and Sushmita. In this case, the combination of all these fields required to identify valid data.

So to make the table into 5NF, we can decompose it into three relations,

Subject | Professor | Semester |
---|---|---|

Maths 2 | Saurav | 2 |

Data structure | Madhurima | 3 |

Automata | Abhijeet | 5 |

Data structure | Sravan | 3 |

Automata | Sushmita | 5 |

Computer network | Karan | 2 |

Digital logical design | Abhijeet | 5 |

Semester | Subject |
---|---|

2 | Maths 2 |

3 | Data structure |

5 | Automata |

2 | Computer network |

5 | Digital logical design |

Subject | Professor |
---|---|

Maths 2 | Saurav |

Data structure | Madhurima |

Automata | Abhijeet |

Data structure | Sravan |

Automata | Sushmita |

Computer network | Karan |

Digital logical design | Abhijeet |

Semester | Professor |
---|---|

2 | Saurav |

3 | Madhurima |

5 | Abhijeet |

3 | Sravan |

5 | Sushmita |

2 | Karan |

5 | Abhijeet |

TOP Interview Coding Problems/Challenges

- Run-length encoding (find/print frequency of letters in a string)
- Sort an array of 0's, 1's and 2's in linear time complexity
- Checking Anagrams (check whether two string is anagrams or not)
- Relative sorting algorithm
- Finding subarray with given sum
- Find the level in a binary tree with given sum K
- Check whether a Binary Tree is BST (Binary Search Tree) or not
- 1[0]1 Pattern Count
- Capitalize first and last letter of each word in a line
- Print vertical sum of a binary tree
- Print Boundary Sum of a Binary Tree
- Reverse a single linked list
- Greedy Strategy to solve major algorithm problems
- Job sequencing problem
- Root to leaf Path Sum
- Exit Point in a Matrix
- Find length of loop in a linked list
- Toppers of Class
- Print All Nodes that don't have Sibling
- Transform to Sum Tree
- Shortest Source to Destination Path

Comments and Discussions