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

EmpNo ProjID

ProjDesc

EmpName * *

assigned
to

EMPLOYEE

PROJECT

EmpAddr

ProjStart

1

123

2

3

234

4

345

5

456
Figure 9-12 Example of many-to-many relationship.

object set is related to one or more instances of the first object set. Figure 9-12
presents an example of a many-to-many relationship.
One employee is assigned to one or more projects simultaneously or over time.
Again, one project is related to one or more employees. Let us try to transform the
object-based data model to a relational data model and establish the many-to-many
relationship. For establishing the relationship, you have to place foreign keys. While
transforming a one-to-many relationship, we placed the foreign key attribute in the
relation on the “many” side of the relationship; that is, we placed the foreign key
attribute in the child relation.
In a many-to-many relationship, which of the two relations is the child relation?
It is not clear. Both relations participate in the relationship in the same way. Look
at the associations shown in Figure 9-12. Transform the object sets into relations
and place the foreign key in the PROJECT relation. Figure 9-13 shows this trans-
formation with foreign key attributes placed in the PROJECT relation.
Note the foreign keys in the transformed relations. In the PROJECT relation,
the rows for projects 1 and 4 need three foreign key columns whereas the rows for
projects 2, 3, and 4 need two foreign key columns each. You get the picture. If some
projects are related to many employees, as many as 50 or so, how many foreign key
columns must the PROJECT relation have? Therefore, it appears that this method
of transformation is not correct.
Let us determine how queries involving data from two related tables work.

Which are the projects related to employee 456? Read the PROJECT table by
values in the foreign key columns. But which foreign key columns? All of the
foreign columns? Right away, you note that finding the result for this query is
going to be extremely difficult.
What are the names of employees assigned to project 1? Read the PROJECT
table by values in the primary key column. Find the row having the value 1
for the primary key attribute. Get the foreign key values of this row,
namely, 123, 234, and 345. Read the EMPLOYEE table by values in the