Database Reference
In-Depth Information
In the current example,
R9
is not in 4NF. This is so because although it is in BCNF,
an MVD exists. Using Fagin's theorem, we may decompose it as follows:
R10
{Course, Text} PK[Course, Text]
R11
{Course, Teacher} PK[Course, Teacher]
Note
: Fagin's theorem prescribes a method of decomposing a relation containing
an MVD that is slightlydifferent from the decomposition of an FD as prescribed by Heath's
theorem: Figure
4-4
clarifies this.
Figure 4-4.
Treating MVDs
4.10 The Fifth Normal Form
So far we have been treating relations that are decomposable into two other relations.
In fact, there are relations which cannot be so decomposed, but can be decomposed
into n other relations where n > 2. They are said to be
n-decomposable
relations (n > 2).
The fifth normal form (5NF) is also commonly referred to as the
projection-join normal
form
(PJNF) because it relates to these (
n
> 2) projections (of a relation not in 5NF) into
decompositions that can be rejoined to yield the original relation.
Recall the
SupplierSchedule
relationship (linking suppliers, inventory items and
projects) mentioned in section 3.5; it is represented here as outlined below:
SupplierSchedule
{Suppl#, Item#, Proj#} PK[Suppl#,
Item#, Proj#]
The relation represents a M:M relationship involving
Suppliers
,
Items
, and
Projects
.
Observe the following features about the relation:
1.
SupplierSchedule
is keyed on all attributes and therefore by
definition, is in BCNF. By inspection, it is also in 4NF.
2.
It is not possible to decompose this relation into two other
relations.
3.
If there are
S
suppliers, N items and J projects, then
theoretically, there may be up to S*N*J records. Not all of these
may be valid.
