## Fifth Normal Form (5NF)

A relation R is in 5NF, also known as projection join normal form (PJ/NF)-If and only if each join dependency in R is a consequence of the candidate keys of R.

## Example

Consider a relation SPJ shown below this relation is “all key” and involves no FDs or MVDs and so is 4NF. Figure shows

• The three projections SP, PJ and JS of SPJ and
• The effect of joining SP and PJ over P# and then joining the result and JS over (J#, S#). Note that the result of the first join is to produce a copy of the original SPJ plus one spurious tuple and then the effect of the second. Join is to eliminate that tuple.

The statement SPJ is equal to join of its three projection SP, PJ and JS are equivalent to the statement.

## Then the triple < s1,p1,j1 > appears in SPJ.

This is a constant, just like an FD or an MVD. This constraint is known as a join dependency (J.D). So, in this example, we say that SPJ satisfies the join dependency(“SP,PJ,JS“).

## Definition of Join Dependency

A relation R satisfies join dependency (R1,R2,R3….Rn) if and only if R is equivalent to the join of (R1,R2,R3….Rn) where Ri is the subsets of the collection of attributes of R.

Relation SPJ suffers from several problems over update operations.

## Example

Consider the relation SPJ

## SPJ

S# P# J#
S1 P1 J1
S1 P2 J1
1. If < S2,P1,J1 > inserted. < S1,P1,J1 > must also be inserted.
2. Yet converse is not true.

Note:It is that constraint on SPJ is equivalent to < S1,P1,J2 >, < S2,P1,J1 >, appears in SPJ then < S1,P1,J1 > also appears in SPJ, so it is due to the presence of JD. This problem is removed when it is three decomposed.

## What is Relational Decomposition?

Decomposition is the mechanism of busting the relation into multiple relations. Decompositions of relations must be certain that from the decomposed relations, an original relation can be reconstructed. If decomposition of a relation is treated carelessly, it can lead to information loss.

## Types of Decomposition

Two types of decomposition are as follows.

## 1. Lossless Decomposition

Given the data is not hidden from the relationship because it is decomposed, later the decomposition would be lossless.

The lossless decomposition authenticates that the joining of the relationship will outcome in an equivalence relation as it was decomposed.

A relation is a lossless decomposition because the natural joins of all over the decomposition provide a standard relation.

## Example

Consider the relation schema Employee_Department.

EMP-ID EMP-NAME DESG SALARY DEPT-ID DEPT-NAME DEPT-LOC
111 Rohan Manager 20000 D01 Finance Delhi
112 Kumar Developer 25000 D02 Production Noida
113 Mahesh Tester 20000 D03 Sales Mumbai
114 Anita Professor 30000 D04 Marketing Delhi
115 Raman Manager 22000 D05 Testing Gurugram
116 Gaurav Developer 15000 D06 Sales Mumbai

Decompose the above relation into two relations to check whether decomposition is lossless or lossy.

Therefore, we have decomposed the connection, which is Employee and Department.

## Relation1: Employee Table

EMP-ID EMP-NAME DESG SALARY
111 Rohan Manager 20000
112 Kumar Developer 25000
113 Mahesh Tester 20000
114 Anita Professor 30000
115 Raman Manager 22000
116 Gaurav Developer 15000

## Relation2: Department Table

DEPT-ID EMP-ID DEPT-NAME DEPT-LOC
D01 111 Finance Delhi
D02 112 Production Noida
D03 113 Sales Mumbai
D04 114 Marketing Delhi
D05 115 Testing Gurugram
D06 116 Sales Mumbai

Now, when these two relations are joined on the common field “EMP-ID”, then the resultant relation will show as.

## Employee ⋈ Department

EMP-ID EMP-NAME DESG SALARY DEPT-ID DEPT-NAME DEPT-LOC
111 Rohan Manager 20000 D01 Finance Delhi
112 Kumar Developer 25000 D02 Production Noida
113 Mahesh Tester 20000 D03 Sales Mumbai
114 Anita Professor 30000 D04 Marketing Delhi
115 Raman Manager 22000 D05 Testing Gurugram
116 Gaurav Developer 15000 D06 Sales Mumbai

Therefore, the decomposition is a lossless join decomposition.

Hence, the Employee table includes (EMP-ID, EMP-NAME, DESG, SALARY) and Department table include (DEPT-ID, DEPT-NAME, and DEPT-LOC), then it isn’t always feasible to connect these two tables or relations, because there may be no common field between them. And it develops into lossy join decomposition.

### 2. Dependency Preservation

Dependency is an essential constraint on the information.

Each dependency needs to be fulfilled by always one decomposed table.

If (P → Q) contains, therefore two sets are functional dependent. And, it gets more helpful for inspecting the dependency only if both sets in a similar relation.

This decomposition features can simply completed by supporting the functional dependency.

In this feature, it permits to analysis the redevelops without calculating the natural join of the database mechanism.

Enroll Yourself in Live Training: DBMS Training