Database Reference
In-Depth Information
relationships correctly, and hence, should not create relations that combine information on
multiple entities. Under this ideal condition, step 3 (normalization) may not be necessary.
However, in practice, all designers may not identify entities correctly. For example, an inex-
perienced designer might view an order as one entity with attributes Order Id, Order Date,
Customer Id, Item Id, and Quantity. Similarly, the attributes Id, Name, Title, Computer_No,
Model and RAM might be viewed as part of a single entity that represents the assignment of
employees to computers. Applying the mapping rules to such entities would result in relations
that represent information on multiple entities, similar to ASSIGNMENT. Normalization
is important in identifying such cases. In the bottom-up approach that typically starts with
a collection of attributes belonging to multiple entities, it is even more likely to produce
relations like ASSIGNMENT. Normalization rules are viewed as a formal framework to
minimize insertion, deletion, and update anomalies. Hence, it would be desirable for these
rules to stand on their own without depending on the ability of the designer to identify the
entities correctly.
How important are one-to-one relationships in the real world? Relationships that are
identifi ed in business applications as one-to-one often may not be “pure” one-to-one rela-
tionships, if all possible current and future exceptions are considered. Many relationships
identifi ed as one-to-one might be one-to-many or many-to-many relationships, in theory.
However, if the number of instances that are exceptions to the one-to-one relationship are
small enough, or the chances of having to store such exceptions in the database is small, it
might be desirable to treat such relationships as one-to-one, to better meet the objectives
of physical design like improving performance and resource requirements. For example,
consider the entities FACULTY and OFFICE in the database for a large university. Only
one or two offi ces have more than one faculty. Similarly, it is very uncommon for a faculty
to have more than one offi ce. If the current situation is expected to continue, treating the
relationship as one-to-one could provide certain benefi ts without making signifi cant sacrifi ces
on data redundancy: 1) Compared to treating the relationship as many-to-many, treating it as
one-to-one doesn't require an associative entity to represent the relationship, 2) Compared
to treating the relationship as one-to-many, treating it as one-to-one gives more fl exibility in
placing the foreign key on FACULTY or OFFICE based on search patterns and/or presence
of nulls in foreign keys. Thus, one-to-one relationships become important in the practice of
database design, though the number of “pure” one-to-one relationships may be small.
TEST FOR BOYCE-CODD
NORMAL FORM (BCNF)
A commonly accepted defi nition is that a relation is in BCNF if and only if every
determinant is a candidate key (Connolly & Begg, 2002; Date, 2000; Hoffer, Prescott &
McFadden, 2002; Kroenke, 2002; Rob & Coronel, 2002; Watson, 1999). The actual word-
ing of the defi nitions presented in this section may vary among different authors, but the
meaning remains the same. The properties of a
relation
are: 1) There are no duplicate tuples,
2) Tuples are unordered, 3) Attributes are unordered, and 4) All attributes are atomic. A
determinant
is any set of attributes on which another set of attributes is fully functionally
determinant
dependent. A set of attributes Y is
fullyfunctionally dependent
on another set of attributes X
functionally dependent
if it is functionally dependent on X and not functionally dependent on any subset of X. A set
