Database Reference
In-Depth Information
trivial functional dependency, X->Y, the left-hand side X includes ID or COMPUTER_NO.
But, ID and COMPUTER_NO also are candidate keys. Thus X is a superkey, leading again
to the conclusion that ASSIGNMENT is in BCNF though it suffers from insertion and dele-
tion anomalies that can be removed by decomposition. Third normal form (3NF) and BCNF
were introduced to eliminate such anomalies.
To determine whether ASSIGNMENT is also in 4 th and 5 th normal forms (4NF and
5NF), we will use simple criteria developed by Date and Fagin (1992). These state that a
relation is in 4NFif it is in BCNF and it contains some simple keys, and it is in 5NF if it is
in BCNF and every key is simple. Using these criteria, ASSIGNMENT is in 5NF since it
is in BCNF and every key is simple.
THE SOURCE OF THE PROBLEM
A root cause for the insertion and deletion problems is that ASSIGNMENT violates
one of the necessary conditions to satisfy the entity integrity rule, which specifi es that no
component of the primary key should have nulls. A necessary condition to meet this require-
ment is that at least one candidate key of a relation should not have nulls. However, the
defi nitions of BCNF, relation, functional dependency, determinant, or candidate key do not
require the database designer to apply the principle of the entity integrity rule in determining
whether a relation is in BCNF.
This example is not an isolated case. A suffi cient condition under which a relation in
BCNF suffers from insertion/deletion anomalies can be stated as follows:
A relation, R, that is in BCNF would suffer from insertion/deletion anomalies if the relation
contains information on two entities, E 1 and E 2
and E 2
and E , including their identifi ers, and their relation-
ship when the relationship between the two entities is (zero-or-one)-to-(zero-or-one).
The reasoning presented earlier using ASSIGNMENT can be summarized for the
general case. Let {A 1 ,A 2 ,…,A n , B 1 ,B 2 ,…,B n } be the schema of R where {A 1 ,A 2 ,…,A n }
represents attributes of E 1 , and {B 1 ,B 2 ,…,B n } represents attributes of E 2 . Let X be the
identifi er of E 1 . Let Y be the identifi er of E 2 . Since the relationship between E 1 and E 2 is
(zero-or-one)-to-(zero-or-one), the functional dependencies X->{ A 1 ,A 2 ,…,A n , B 1 ,B 2 ,…,B n
} and Y->{A 1 ,A 2 ,…,A n , B 1 ,B 2 ,…,B n } hold. Thus, X and Y are candidate keys and there are
no other candidate keys. The (zero-or-one)-to-(zero-or-one) relationship between E 1 and E 2
means that both X and Y may have nulls, resulting in insertion/deletion anomalies irrespec-
tive of whether X, Y, or the combination of X and Y is selected as the primary key. However,
relation R would be in BCNF when there are no additional functional dependencies among
the non-key attributes of R, since X and Y are the only determinants. Thus, whether R is in
BCNF or not in BCNF, it suffers from insertion/deletion anomalies when the relationship
between E 1 and E 2 is (zero-or-one)-to-(zero-or-one). The above reasoning holds true even
when entities E 1 and E 2 may have more than one identifi er. The problem also exists in the
more general case when a relation contains information on two or more entities, including
their identifi ers and their relationships when the relationships between each pair of entities
is (zero-or-one)-to-(zero-or-one).
Search WWH ::




Custom Search