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

Multivalued Transform into a column of a separate relation.

Derived Transform into a column of the corresponding relation.

Primary Key

Single attribute Transform into a single-column primary key.

Composite Transform into a multicolumn primary key.

Relationship

One-to-One Establish the relationship through a foreign key attribute in either
of the two participating relations.
One-to-Many Establish the relationship through a foreign key attribute in the
participating relation on the “many” side of the relationship.
Many-to-Many Transform by forming two one-to-many relationships with a
new intersection relation between the participating relations. Establish the
relationship through foreign key attributes in the intersection relation.
Optional and mandatory conditions Set constraint for the foreign key column.
If null values are not allowed in the foreign key column, this represents a
mandatory relationship. Allowing null values denotes an optional relationship.
Mandatory and optional conditions apply only to the participation of the rela-
tion on the “many” side of a one-to-many relationship, that is, to the partici-
pation of rows in the relation that contains the foreign key column.

Summary of Transformation

Recall the example of the semantic data model for the teaching aspects of a uni-
versity given in Chapter 7. Figure 7-22 shows an entity-relationship diagram
(ERD). Let us summarize our discussion on transformation of a semantic data
model by transforming the model represented by that ERD into a relational
data model. Figure 9-22 shows the relational data model, the outcome of the
transformation.
Go through each component of the semantic data model and check out the
corresponding component in the relational data model. Note the columns for each
relation. Observe the establishment of relationships through foreign key columns.

CHAPTER SUMMARY

• Transformation of the semantic data model into the relational data model is an
approach with many merits for creating the relational model.
• The model transformation method applies to large database systems with
complex information requirements.
• However, the model transformation method could be long and tedious and
must be carried out with thoroughness and care.
• Semantic data models created by either the object-based modeling technique
or the entity-relationship modeling technique may be transformed into