Page 120 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 7: Relational Database Design

Intuitively R is in 4NF if all dependencies are a result of keys. When multivalued dependencies Notes
exist, a relation should not contain two or more independent multivalued attributes. The
decomposition of a relation to achieve 4NF would normally result in not only reduction of
redundancies but also avoidance of anomalies.

7.6 Join Dependencies

Based on the discussion above, we know that the normal forms require that the given relation R
if not in the given normal form should be decomposed in two relations to meet the requirements
of the normal form. However, in some rare cases, a relation can have problems like redundant
information and update anomalies, yet it cannot be decomposed in two relations without loss of
information. In such cases, it may be possible to decompose the relation in three or more
relations using the 5NF. But when does such a situation arise? Such cases normally happen when
a relation has at least three attributes such that all those values are totally independent of each
other.
The fifth normal form deals with join-dependencies, which is a generalisation of the MVD. The
aim of fifth normal form is to have relations that cannot be decomposed further. A relation in
5NF cannot be constructed from several smaller relations.
A relation R satisfies join dependency *(R , R , ..., R ) if and only if R is equal to the join of R , R ,
1 2 n 1 2
..., R where R are subsets of the set of attributes of R.
n i
A relation R is in 5NF if for all join dependencies at least one of the following holds:
1. (R , R , ..., R ) is a trivial join-dependency (that is, one of R is R)
1 2 n i
2. Every R is a candidate key for R.
i
An example of 5NF can be provided by the same above example that deals with emp_name,
Projects and Programming languages with some modifications:

emp_name Projects Languages
DEV Proj_A C
RAM Proj_A JAVA
DEV Proj_B C
RAM Proj_B C++

The relation above assumes that any employee can work on any project and knows any of the
three languages. The relation also says that any employee can work on projects Proj_A, Proj_B,
Proj_C and may be using a different programming languages in their projects. No employee
takes all the projects and no project uses all the programming languages and therefore all three
fields are needed to represent the information. Thus, all the three attributes are independent of
each other.

The relation above does not have any FDs and MVDs since the attributes emp_name, project and
languages are independent; they are related to each other only by the pairings that have
significant information in them. For example, DEV is working on Project A using C languague.
Thus, the key to the relation is (emp_name, project, languague). The relation is in 4NF, but still
suffers from the insertion, deletion, and update anomalies as discussed for the previous form of
this relation. However, the relation therefore cannot be decomposed in two relations.
(emp_name, project), and

(emp_name, language)

LOVELY PROFESSIONAL UNIVERSITY 113