An Example of Database Normalization (Cont.)

Fifth Normal Form (5NF)
In order to normalize relations, we decompose the relations into multiple relations. Although multiple divided relations optimize transactions and avoid anomalies, it adds a cost for data retrieval as relations have to be rejoined. The biggest risk with rejoining is, producing inaccurate outputs in certain conditions. When we decompose a relation into two relations, the resulting relations have the property called lossless-join that makes sure rejoining two relations produce the original relation.
Lossless-join is a property of decomposition, which ensures that no spurious tuples are generated when relations are reunited through a natural join operation.
Now let’s try to understand fifth normal form. When decomposing a relation into multiple relations for minimizing redundency, it might introduce join dependency, that might create spurious tuples when they are reunited.
For a relation R with subsets of the attributes of R denoted as A, B, ..., Z, a relation R satisfies a join dependency if and only if every legal value of R is equal to the join its projections on A, B, ..., Z.
Considering this, definition of fifth normal form goes as
A relation is in fifth normal form if it is in 4NF, and won’t have join dependency.
Since this is very rare to see in database design, let’s try to understand with an example. See the table that contains how Lecturers teaches Subjects related to Courses.