@Spy
102 CHAPTER 5 Transforming the Conceptual Data Model to SQL
transformation rules (and integrity rules) are the same for both the dis-
joint and overlapping subtype generalizations.

Another approach is to have a single table that includes all attributes
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 sub-
types. There are advantages and disadvantages to each of these three
approaches. Several software tools now support all three options [Fowler
2003; Ambler, 2003].
Database practitioners often add a discriminator to the supertype
when they implement generalization. The discriminator is an attribute
that has a separate value for each 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.
The transformation of an aggregation abstraction also produces a
separate table for the supertype entity and each subtype entity. However,
Figure 5.8 UML: generalization and aggregation
Individual
Employee Customer
create table individual (indiv_id char(10),
indiv_name char(20),
indiv_addr char(20),
primary key (indiv_id));
create table employee (emp_id char(10),
job_title char(15),
primary key (emp_id),
foreign key (emp_id) references individual

on delete cascade on update cascade);
create table customer (cust_no char(10),
cust_credit char(12),
primary key (cust_no),
foreign key (cust_no) references individual
on delete cascade on update cascade);
An individual may
be either an employee
or a customer, or both,
or neither.
Teorey.book Page 102 Saturday, July 16, 2005 12:57 PM
@Spy
5.2 Transformation Steps 103
there are no common attributes and no integrity constraints to main-
tain. 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].
5.1.5 Multiple Relationships
Multiple relationships among n entities are always considered to be com-
pletely independent. One-to-one, one-to-many binary, or binary recur-
sive relationships resulting in tables that are either equivalent or differ
only in the addition of a foreign key can simply be merged into a single
table containing all the foreign keys. Many-to-many or ternary relation-
ships that result in SQL tables tend to be unique and cannot be merged.
5.1.6 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.
5.2 Transformation Steps
The following list summarizes the basic transformation steps from an ER
diagram to SQL tables:
• Transform each entity into a table containing the key and non-
key attributes of the entity
• Transform every many-to-many binary or binary recursive rela-
tionship into a table with the keys of the entities and the
attributes of the relationship
• Transform every ternary or higher-level n-ary relationship into a
table
Now let us study each step in turn.
Teorey.book Page 103 Saturday, July 16, 2005 12:57 PM
@Spy
104 CHAPTER 5 Transforming the Conceptual Data Model to SQL
5.2.1 Entity Transformation
If there is a one-to-many relationship between two entities, add the key
of the entity on the “one” side (the parent) into the child table as a for-
eign key. If there is a one-to-one relationship between one entity and
another entity, add the key of one of the entities into the table for the
other entity, thus changing it to a foreign key. The addition of a foreign
key due to a one-to-one relationship can be made in either direction.
One strategy is to maintain the most natural parent-child relationship
by putting the parent key into the child table. Another strategy is based
on efficiency: add the foreign key to the table with fewer rows.
Every entity in a generalization hierarchy is transformed into a table.
Each of these tables contains the key of the supertype entity; in reality,
the subtype primary keys are foreign keys as well. The supertype table
also contains nonkey values that are common to all the relevant entities;

the other tables contain nonkey values specific to each subtype entity.
SQL constructs for these transformations may include constraints for
not null, unique, and foreign key. A primary key must be specified for
each table, either explicitly from among the keys in the ER diagram or
by taking the composite of all attributes as the default key. Note that the
primary key designation implies that the attribute is not null or unique.
It is important to note, however, that not all DBMSs follow the ANSI
standard in this regard—it may be possible in some systems to create a
primary key that can be null. We recommend that you specify “not null”
explicitly for all key attributes.
5.2.2 Many-to-Many Binary Relationship Transformation
In this step, every many-to-many binary relationship is transformed into
a table containing the keys of the entities and the attributes of the rela-
tionship. The resulting table will show the correspondence between spe-
cific instances of one entity and those of another entity. Any attribute of
this correspondence, such as the elected office an engineer has in a pro-
fessional association (Figure 5.1f), is considered intersection data and is
added to the table as a nonkey attribute.
SQL constructs for this transformation may include constraints for
not null. The unique constraint is not used here because all keys are
composites of the participating primary keys of the associated entities in
the relationship. The constraints for primary key and foreign key are
required, because a table is defined as containing a composite of the pri-
mary keys of the associated entities.
Teorey.book Page 104 Saturday, July 16, 2005 12:57 PM
@Spy
5.2 Transformation Steps 105
5.2.3 Ternary Relationship Transformation
In this step, every ternary (or higher n-ary) relationship is transformed
into a table. Ternary or higher n-ary relationships are defined as a collec-

tion of the n primary keys in the associated entities in that relationship,
with possibly some nonkey attributes that are dependent on the key
formed by the composite of those n primary keys.
SQL constructs for this transformation must include constraints for
not null, since optionality is not allowed. The unique constraint is not
used for individual attributes, because all keys are composites of the par-
ticipating primary keys of the associated entities in the relationship. The
constraints for primary key and foreign key are required because a table
is defined as a composite of the primary keys of the associated entities.
The unique clause must also be used to define alternate keys that often
occur with ternary relationships. Note that a table derived from an n-ary
relationship has n foreign keys.
5.2.4 Example of ER-to-SQL Transformation
ER diagrams for the company personnel and project database (Chapter
4) can be transformed to SQL tables. A summary of the transformation
of entities and relationships to SQL tables is illustrated in the following
list.
SQL tables derived directly from entities (see Figure 4.3d):
division secretary project
department engineer location
employee technician prof_assoc
manager skill desktop
SQL tables derived from many-to-many binary or many-to-many
binary recursive relationships:
• belongs_to
SQL tables transformed from ternary relationships:
• skill_used
• assigned_to
Teorey.book Page 105 Saturday, July 16, 2005 12:57 PM
@Spy

106 CHAPTER 5 Transforming the Conceptual Data Model to SQL
5.3 Summary
Entities, attributes, and relationships in the ER model and classes,
attributes, and associations in UML can be transformed directly into SQL
(SQL-99) table definitions with some simple rules. Entities are trans-
formed into tables, with all attributes mapped one-to-one to table
attributes. Tables representing entities that are the child (“many” side) of
a parent-child (one-to-many or one-to-one) relationship must also
include, as a foreign key, the primary key of the parent entity. A many-
to-many relationship is transformed into a table that contains the pri-
mary keys of the associated entities as its composite primary key; the
components of that key are also designated as foreign keys in SQL. A ter-
nary or higher-level n-ary relationship is transformed into a table that
contains the primary keys of the associated entities; these keys are desig-
nated as foreign keys in SQL. A subset of those keys can be designated as
the primary key, depending on the functional dependencies associated
with the relationship. Rules for generalization require the inheritance of
the primary key from the supertype to the subtype entities when trans-
formed into SQL tables. Optionality constraints in the ER or UML dia-
grams translate into nulls allowed in the relational model when applied
to the “one” side of a relationship. In SQL, the lack of an optionality
constraint determines the not null designation in the create table defini-
tion.
5.4 Literature Summary
Definition of the basic transformations from the ER model to tables is
covered in McGee [1974], Wong and Katz [1979], Sakai [1983], Martin
[1983], Hawryszkiewyck [1984], Jajodia and Ng [1984], and for UML in
Muller [1999].
Teorey.book Page 106 Saturday, July 16, 2005 12:57 PM