@Spy
5.1 Transformation Rules and SQL Constructs 97
Figure 5.6 UML: ternary and n-ary relationships
Technician
Project
emp_id project_name notebook_no
35 alpha 5001
35 gamma 2008
42 delta 1004
42 epsilon 3005
81 gamma 1007
93 alpha 1009
93 beta 5001
Notebook
11
1
uses-notebook
A technician uses exactly one notebook
for each project. Each notebook belongs
to one technician for each project. Note
that a technician may still work on many
projects and maintain different note-
books for different projects.
create table technician (emp_id char(10),
primary key (emp_id));
create table project (project_name char(20),
primary key (project_name));
create table notebook (notebook_no integer,
primary key (notebook_no));
create table uses_notebook (emp_id char(10),
project_name char(20),

notebook_no integer not null,
primary key (emp_id, project_name),
foreign key (emp_id) references technician
on delete cascade on update cascade,
foreign key (project_name) references project
on delete cascade on update cascade,
foreign key (notebook_no) references notebook
on delete cascade on update cascade,
unique (emp_id, notebook_no),
unique (project_name, notebook_no));
uses_notebook
emp_id, project_name → notebook_no
emp_id, notebook_no → project_name
project_name, notebook_no → emp_id
(a) one-to-one-to-one ternary association
Functional dependencies
Teorey.book Page 97 Saturday, July 16, 2005 12:57 PM
@Spy
98 CHAPTER 5 Transforming the Conceptual Data Model to SQL
Figure 5.6 (continued)
emp_id project_name loc_name
48101 forest B66
48101 ocean E71
20702 ocean A12
20702 river D54
51266 river G14
51266 ocean A12
76323 hills B66
Each employee assigned to a project works
at only one location for that project, but

can be at a different location for a different
project. At a given location, an employee
works on only one project. At a particular
location there can be many employees
assigned to a given project.
create table employee (emp_id char(10),
emp_name char(20),
primary key (emp_id));
create table project (project_name char(20),
primary key (project_name));
create table location (loc_name char(15),
primary key (loc_name));
create table assigned_to (emp_id char(10),
project_name char(20),
loc_name char(15) not null,
primary key (emp_id, project_name),
foreign key (emp_id) references employee
on delete cascade on update cascade,
foreign key (project_name) references project
on delete cascade on update cascade,
foreign key (loc_name) references location
on delete cascade on update cascade,
unique (emp_id, loc_name));
assigned_to
(b) one-to-one-to-many ternary associations
emp_id, loc_name → project_name
emp_id, project_name → loc_name
Functional dependencies
Employee
Project Location

11
*
assigned-to
Teorey.book Page 98 Saturday, July 16, 2005 12:57 PM
@Spy
5.1 Transformation Rules and SQL Constructs 99
Figure 5.6 (continued)
emp_idproject_name mgr_id
4106alpha 27
4200alpha 27
7033beta 32
4200beta 14
4106gamma 71
7033delta 55
4106delta 39
4106iota 27
Each engineer working on a particular
project has exactly one manager, but
a project can have many managers and
an engineer may have many managers
and many projects. A manager may
manage several projects.
create table project (project_name char(20),
primary key (project_name));
(c) one-to-many-to-many ternary association
create table manager (mgr_id char(10),
primary key (mgr_id));
create table engineer (emp_id char(10),
primary key (emp_id));
create table manages (project_name char(20),

mgr_id char(10) not null,
emp_id char(10),
primary key (project_name, emp_id),
foreign key (project_name) references project
on delete cascade on update cascade,
foreign key (mgr_id) references manager
on delete cascade on update cascade,
foreign key (emp_id) references engineer
on delete cascade on update cascade);
manages
project_name, emp_id → mgr_id
Functional dependency
Project
Manager Engineer
1*
*
manages
Teorey.book Page 99 Saturday, July 16, 2005 12:57 PM
@Spy
100 CHAPTER 5 Transforming the Conceptual Data Model to SQL
Figure 5.6 (continued)
emp_id project_nameskill_type
101 electronicsalgebra
101 electronicscalculus
101 mechanicsalgebra
101 mechanicsgeometry
102 electronicsalgebra
102 electronicsset-theory
102 mechanicsgeometry
105 mechanicstopology

Employees can use different skills
on any one of many projects, and
each project has many employees
with various skills.
create table employee (emp_id char(10),
emp_name char(20),
primary key (emp_id));
create table skill (skill_type char(15),
primary key (skill_type));
create table project (project_name char(20),
primary key (project_name));
create table skill_used (emp_id char(10),
skill_type char(15),
project_name char(20),
primary key (emp_id, skill_type, project_name),
foreign key (emp_id) references employee
on delete cascade on update cascade,
foreign key (skill_type) references skill
on delete cascade on update cascade,
foreign key (project_name) references project
on delete cascade on update cascade);
skill_used
(d) many-to-many-to-many ternary association
None
Functional dependencies
Employee
Skill Project
**
*
skill-used

Teorey.book Page 100 Saturday, July 16, 2005 12:57 PM
@Spy
5.1 Transformation Rules and SQL Constructs 101
5.1.4 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 Figure 5.8 for UML). The table derived
from the supertype entity contains the supertype entity key and all com-
mon attributes. Each table derived from subtype entities contains the
supertype entity key and only the attributes that are specific to that sub-
type. Update integrity is maintained by requiring all insertions and dele-
tions 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 supertype table, then all subtype tables, as well
as the supertype table, must be updated. An update to a nonkey attribute
affects either the supertype or one subtype table, but not both. The
Figure 5.7 ER model: generalization and aggregation
create table (indiv_id char(10),individual
indiv_name char(20),
indiv_addr char(20),
primary key (indiv_id));
create table (emp_id char(10),employee
job_title char(15),
primary key (emp_id),
foreign key (emp_id) references individual
on delete cascade on update cascade);
create table (cust_no char(10),customer
cust_credit char(12),
primary key (cust_no),
foreign key (cust_no) references individual

on delete cascade on u
p
date cascade);
Individual
An individual may be either an
employee or a customer, or both,
or neither.
Employee
Customer
Teorey.book Page 101 Saturday, July 16, 2005 12:57 PM