Fifth Normal Form (5NF)

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.

If the pair < s₁,p₁ > appears in SP

And the pair < p₁,j₁ > appears in PJ

And the pair < j₁,s₁ > appears in JS

Then the triple < s₁,p₁,j₁ > 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 (R₁,R₂,R₃….R_n) if and only if R is equivalent to the join of (R₁,R₂,R₃….R_n) where R_i 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

1. If < S₂,P₁,J₁ > inserted. < S₁,P₁,J₁ > must also be inserted.
2. Yet converse is not true.

Note:It is that constraint on SPJ is equivalent to < S₁,P₁,J₂ >, < S₂,P₁,J₁ >, appears in SPJ then < S₁,P₁,J₁ > 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.

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

Relation2: Department Table

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

Employee ⋈ Department

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