SEMANTIC DATA MODEL TO RELATIONAL DATA MODEL - Database Design and Development: An Essential Guide for IT Professionals - page 286

• Create a separate relation, called the intersection table. Use both primary keys
of the participating relations as the concatenated primary key for the inter-
section table. The primary intersection table contains two attributes—one
attribute establishing the relationship to one of the two relations and the other
attribute linking the other relation.
• Each part of the primary key of the intersection table serves as a foreign key.
• Each foreign key attribute has the same data type, length, and domain values
as the corresponding primary key attribute in the related table.
• The relationship of the first relation to the intersection relation is one-to-many;
the relationship of the second relation to the intersection relation is also one-
to-many. In effect, transformation of the many-to-many relationship is reduced
to creating two one-to-many relationships.
Mandatory and Optional Conditions
The semantic model is able to represent whether a relationship is optional or
mandatory. As you know, the minimum cardinality indicator denotes mandatory and
optional conditions. Let us explore the implications of mandatory and optional con-
ditions for relationships in a relational model. In our discussions so far, we have
examined the relationships in terms of maximum cardinalities. If the maximum car-
dinalities are 1 and 1, then the relationship is implemented by placing the foreign
key attribute in either of the participating relations. If the maximum cardinalities
are 1 and *, then the relationship is established by placing the foreign key attribute
in the relation on the “many” side of the relationship. Finally, if the maximum car-
dinalities are * and *, then the relationship is broken down into two one-to-many
relationships by introducing an intersection relation. Let us consider a few exam-
ples with minimum cardinalities and determine the effect on the transformation.
Minimum Cardinality in One-to-Many Relationship Figure 9-16 shows and
example of a one-to-many relationship between the two object sets PROJECT and
EMPLOYEE.
Note the cardinality indicators (1,1) shown next to the PROJECT object set.
Intentionally, the figure does not show the minimum cardinality indicator next to
EMPLOYEE. We will discuss the reason for this very shortly. What is the meaning
of the cardinality indicators next to the PROJECT object set? The indicators
represent the following conditions:
An employee can be assigned to a maximum of only one project . Every
employee must be assigned to a project. That is, an employee instance must
be associated with a minimum of 1 project instance. In other words, every
employee instance must participate in the relationship. The relationship as far
as the employee instances are concerned is mandatory.
Now look at the foreign key column in the EMPLOYEE table. If every employee
is assigned to a project, then every EMPLOYEE row must have a value in the
foreign key column. You know that this value must be the value of the primary key
of the related row in the PROJECT table. What does this tell you about the foreign