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

Next, let us take up optional conditions on both sides. Suppose the cardinality
indicators (0,*) are shown next to the PROJECT and EMPLOYEE object sets. Then
the indicators will represent the following conditions:
An employee may be assigned to many projects.
A project may have many employees.
Not every employee need be assigned to a project. That is, some employee
instances may not be associated with any project instance at all. At a minimum,
an employee instance may be associated with no project instance or with zero
project instances. In other words, not every employee instance needs to par-
ticipate in the relationship. The relationship as far as the employee instances
are concerned is optional.
Not every project need have an employees. That is, some project instances may
not be associated with any employee instance at all. At a minimum, a project
instance may be associated with no employee instance or with zero employee
instances. In other words, not every project instance needs to participate in
the relationship. The relationship as far as the project instances are concerned
is optional.
It follows, therefore, that in an optional relationship of this sort, null values may
be allowed in the foreign key attributes. However, in the way the transformation is
represented in Figure 9-17, allowing null values in foreign key attributes would
present a problem. You have noted that the foreign key attributes form the primary
key of the intersection relation, and no part of a primary key in a relation can have
null values according to the integrity rule for the relational model. Therefore,
in such cases, you may adopt an alternate transformation approach by assigning a
separate primary key as shown in Figure 9-18.
What do the rows with null foreign key attributes in the ASSIGNMENT rela-
tion represent? These rows represent those employees who are not assigned to a
project or those projects that have no employees. In practice, you may want to
include such rows in the relations to indicate employees already eligible for assign-
ment but not officially assigned and to denote projects that usually have employees
assigned but not yet ready for assignment.
Minimum Cardinality on the “Many” Side In the above figures, we considered
minimum cardinalities shown next to object sets on the “one” side of one-to-many
relationships. For example, Figure 9-16 shows minimum cardinality indicator next
to the PROJECT object set that is on the “one” side of the relationship. Now,
let us consider the minimum cardinality indicator on the “many” side. Assume
cardinality indicators of (1,*) on the “many” side next to the EMPLOYEE object
set. This would indicate the following conditions:
A project may have many employees . Every project must have at least one
employee. That is, a project instance must be associated with a minimum of 1
employee instance. In other words, every project instance must participate in
the relationship. The relationship as far as the project instances are concerned
is mandatory.