Database Reference
In-Depth Information
The use of non-derived uniqueness constraints on derived associations raises normal-
ization concerns, since any uniqueness constraint is equivalent to a functional dependency
(FD). For example, applying ORM's Rmap relational mapping algorithm (Halpin, 2001a)
to the conceptual schema in Figure 2 leads to this relation scheme for Role:
Relation schemes for Attribute, Subtyping, and BinaryAssociation, and various inter-
relation constraints are also created by Rmap, but are omitted here. The primary key is
underlined, optional columns are enclosed in square brackets, attribute domain constraints
are listed in braces, and the subtyping and frequency constraints are expressed by numbered
qualifi cations.
The normalization issue in question is the functional dependency from the attribute
xorConstraintNr to the attribute entityTypeName, depicted by the arrow shown. Since xor-
ConstraintNr is optional, this FD means that each non-null value recorded for xorConstraintNr
determines exactly one value for entityTypeName. Since this embedded, partial FD is not
implied by the primary key constraint, this might be considered to violate normalization
principles. We could enforce the partial FD constraint by materializing a relation scheme
XorConstraint( xorConstraintNr, entityTypeName )
and setting up a pair-equality constraint
between this and the projection
Role[xorConstraintNr, entityTypeName]
. But in practice it is
better to simply enforce the partial FD within the Role table itself. With today's relational
DBMSs, this can be easily and effi ciently done. For example, the following general form
of the constraint can be simplifi ed further for individual inserts or updates (e.g., within an
insert/update trigger):
check
(
not exists
(
select
xorConstraintNr
from
Role
where
xorConstraintNr
is not null
group by
xorConstraintNr
having count
(
distinct
entityTypeName*) > 1 ) )
Since the FD here is partial (applies only to non-null values within an optional attri-
bute), it is not covered by classical normalization theory. Moreover, no redundancy of base
facts is involved, and the derived redundancy for the derived fact type
XorConstraint
is over
EntityType
is controlled by the above constraint, so no update anomaly can occur. Since
such constraints can also be effi ciently implemented, this licenses denormalization arising
from non-derived uniqueness constraints on derived fact types.
BARKER ER
The term “Barker ER notation” denotes the notation for Entity Relationship modeling
discussed in the classic treatment by Richard Barker (1990). Originating at CACI in the
United Kingdom, the notation was later adopted by Oracle Corporation in its CASE design
