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

TRANSFORMATION OF RELATIONSHIPS

The semantic data modeling techniques have elegant ways for representing rela-
tionships between two object sets. Wherever you perceive direct associations
between instances of two object sets, the two object sets are connected by lines with
a diamond in the middle containing the name of the relationship. How many of
instances of one object set are associated with how many instances of the other?
An indication about the numbers is given by cardinality indicators, especially the
maximum cardinality indicator. The minimum cardinality indicator denotes whether
a relationship is optional or mandatory.
You know that the relational data model establishes relationships between two
relations through foreign keys. Therefore, transformation of relationships as repre-
sented in the semantic model involves mapping of the connections and cardinality
indicators into foreign keys. We will discuss how this is done for one-to-one, one-
to-many, and many-to-many relationships. We will also go over the transformation
of optional and mandatory conditions for relationships. While considering trans-
formation of relationships, we need to review relationships between a superset and
its subsets.

One-to-One Relationships

When one instance of an object set is associated with a maximum of only one
instance of another object set, we call this relationship a one-to-one relationship.
Figure 9-6 shows a one-to-one relationship between the two object sets CLIENT
and CONTACT PERSON.
If a client of the organization has designated a contact person, then the contact
person is represented by the CONTACT PERSON object set. Only one contact
person exists for a client. But some clients may not have contact persons, in which
case there is no corresponding instance in the CONTACT PERSON object set. Now
we can show the relationship by placing the foreign key column in the CLIENT
relation. Figure 9-7 illustrates this transformation.
Observe how the transformation is done. How are the rows of the CLIENT rela-
tion linked to corresponding rows of the CONTACT PERSON relation? The values
in the foreign key columns and primary key columns provide the linkage. Do you
note some foreign key columns in the CLIENT relation with null values? What are
these? For these clients, client contact persons do not exist. If the majority of clients
do not have assigned contact persons, then many of the rows in the CLIENT rela-
tion will contain null values in the foreign key column. This is not a good transfor-
mation. A better transformation would be to place the foreign key column in the

ContactNo

ClntNo

ContactName

ClntName 1 1

assigns

CONTACT
PERSON

CLIENT

ClntAddr

ContactPhone

Figure 9-6 One-to-one relationship.