Teorey J., Lightstone S., Nadeau T. Database Modeling and Design: Logical Design

Подождите немного. Документ загружается.

which represents the child entity, with nulls allowed for for-

eign keys only in the optional “one” case. Foreign key con-

straints are set according to the specific meaning of the

relationship and may vary from one relationship to another.

(d)

(e)

(f)

Figure 5.2, cont’d (d) one-to-many, both entities mandatory, (e) one-to-many, one entity mandatory, one

optional, and (f) many-to-many, both entities optional.

Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL 91

The many-to-many relationship, shown in Figure 5.1(f)

as optional for both entities, requires a new table con-

taining the primary keys of both entities. The same trans-

formation applies to either the optional or mandatory

case, including the fact that the “not null” clause must

appear for the foreign keys in both cases. Note also that

an optional entity means that the SQL table derived from

it may have zero rows for that particular relationship. This

does not affect “null” or “not null” in the table definition.

Binary Recursive Relat ionships

A single entity with a one-to-one relationship implies

some form of entity occurrence pairing, as indicated by

the relationship name. This pairing may be completely

optional, completely mandatory, or neither. In all of these

cases (Figure 5.3a for ER and Figure

5.4a for UML), the

pairing entity key appears as a foreign key in the resulting

table. The two key attributes are taken from the same

domain but are given different names to designate their

unique use. The one-to-many relationship requires a

foreign key in the resulting table (Figure 5.3b). The foreign

key constraints can vary with the particular relationship.

The many-to-many binary recursive relationship is shown

s opt

ional (Figure 5.3c) and r

esults in a new table; it could

also be defined as mandatory (using the word “must” instead

of “may”). Both cases have the foreign keys defined as “not

null.” Inmany-to-manyrelationships,foreign k ey co nstraint s

on delete and update must always be cascaded because

each entry in the SQL table depends on the current value

or existence of the referenced pr imary key.

Ternary and n-ary Relationships

An n-ary relationship has (n þ 1) possible variations of

connectivity: all n sides with connectivity “one”; (n  1)

sides with connectivity “one” and one side with connectivity

“many”; (n  2) sides with connectivity “one” and two sides

with “many”; and so on until all sides are “many.”

The four possible varieties o f a ternary relationship

are shown in Figure 5.5 for the ER

model and Figure 5.6

for UML. All variations are transformed by creating an

92 Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL

SQL table containing the primary keys of all entities;

however, in each case the meaning of the keys is differ-

ent. When all three relationships are “one” (Figure 5.5a),

the resulting SQL table consists of three possible distinct

keys. This represents the fact that there are three

(a)

(b)

(

)

Figure 5.3 ER model: binary recursive relationship: (a) one-to-one, both sides optional, (b) one-to-many,

“one” side mandatory, “many” side optional, and (c) many-to-many, both sides optional.

Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL 93

functional dependencies (FDs) that are needed to

describe this relationship. The optionality constraint is

not used here because all n entities must participate in

every instance of t he relationship to satisfy t he FD con-

straints. (See Chapter 6 for more discussion of functional

dependencies.)

In general, the number of entities with connectivity

“one” determines the lower bound on the number of FDs.

Thus, in Figure 5.5(b), which is one-to-one-to-many, there

are two FDs; in Figure 5.5(c), which is one-to-many-to-

(a)

(b)

(c)

Figure 5.4 UML: binary

recursive relationship: (a)

one-to-one, both sides

optional, (b) one-to-many,

“one” side mandatory,

“many” side optional, and

sides optional.

94 Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL

many, there is only one FD. When all relationships are

“many” (Figure 5.5d), the relationship table is all one com-

posite key unless the relationship has its own attributes. In

that case, the key is the composite of all three keys from

the three associated entities.

Foreign key constraints on delete and update for ter-

nary re

lationships transformed to SQL tables must always

be cascade because each entry in the S QL table depend s

on the current value of, or existence of, the referenced

primary key.

Generalization and Aggregation

The transformation of a generalization abstraction can

produce separate SQL tables for the generic or supertype

entity and each of the subtypes (Figure 5.7 for the ER

model and Figur

e 5.8 for UML). The table derived from

the supertype entity contains the supertype entity key and

all common attributes. Each table derived from subtype

entities contains the supertype entity key and only the

attributes that are specific to that subtype. Update integrity

is maintained by requiring all i nsertions and deletions

to occur in both the supertype table and relevant subtype

table—that is, the foreign key constraint cascade must

be used. If the update is to the primary key of the super-

type table, then all subtype tables as well as the supertype

table m ust be updat ed. An update to a non ke y attribute

affects either the supertype or one subtype table, but not

both. The transformation rules (and integrity rules) are

thesameforboththedisjointandoverlappingsubtype

generalizations.

Another approach is to have a single table that includes

l attr

ibutes from the supertype and subtypes (the whole

hierarchy in one table) with nulls used when necessary.

A third possibility is one table for each subtype, pushing

down the common attributes into the specific subtypes.

There are advantages and disadvantages to each of these

three approaches. Several software tools are now supporting

all three options (Fowler, 2003; Ambler, 2003).

Database practitioners often add a discriminator to the

supertype when they implement generalization. The dis-

criminator is an attribute that has a separate value for each

Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL 95

subtype and indicates which subtype to use to get further

information. This approach works up to a point. However,

there are situations requiring multiple levels of supertypes

and subtypes, where more than one discriminator may be

required.

Figure 5.5 ER model: ternary and n-ary relationships: (a) one-to-one-to-one,

96 Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL

Figure 5.5, cont’d (b) one-to-one-to-many,

Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL 97

The transformation of an aggregation abstraction also

produces a separate table for the supertype entity and each

subtype entity. However, there are no common attributes

and no integrity constraints to maintain. The main function

of aggregation is to provide an abstraction to aid the view

integration process. In UML, aggregation is a composition

relationship, not a type relationship, which corresponds to

a weak entity (Muller, 1999).

Figure 5.5, cont’d (c) one-to-many-to-many, and

98 Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL

Multiple Relationships

Multiple relationships among n entities are always con-

sidered to be completely independent. One-to-one, one-

to-many binary, or binary recursive relationships resulting

in tables that are either equivalent or differ only in the

addition of a foreign key can simply be merged into a sin-

gle table containing all the foreign keys. Many-to-many or

ternary relationships that result in SQL tables tend to be

unique and cannot be merged.

(d)

Figure 5.5, cont’d (d) many-to-many-to-many ternary relationships.

Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL 99

Weak Entities

Weak entities differ from entities only in their need for keys

from other entities to establish their uniqueness. Otherwise,

they have the same transformation properties as entities, and

no special rules are needed. When a weak entity is already

derived from two or more entities in the ER diagram, it can

be directly transformed into a table without further change.

Figure 5.6 UML: ternary and n-ary relationships: (a) one-to-one-to-one,

100 Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL