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

EmpNo ProjID

ProjDesc

EmpName * *

assigned
to

EMPLOYEE

PROJECT

EmpAddr

ProjStart

EMPLOYEE relation

EmpNo

EmpName EmpAddr ProjID1 ProjID2 ProjID3 ProjID4

123 Daniel R. Grady Jamesburg, NJ 08810 1 3

234 Alice M. Knox Iselin, NJ 08834 1 4 5

Matthew Rader Edison, NJ 08817

345 4 2 1 5

456 Ramola Ruskin Metuchen, NJ 08819 2 3 4

ProjID ProjDesc ProjStart

1 Requirements 2/15/2002

2 DB Design 4/22/2002

PROJECT
relation

3 DB Implementation 8/31/2002

4 User Training 8/1/2002

5 DB Fine Tuning 9/15/2002

Related employee-project instance pairs : (123, 1), (123, 3), (234, 1), (234, 4), (234, 5),
(345, 4), (345, 2), (345, 1), (345, 5), (456, 2), (456, 3), (456, 4)

Figure 9-14 Transformation of many-to-many relationship: second method.

It is clear that the second method of transformation also does not work. We seem
to be in a quandary. Where should you place the foreign key column—in which of
the two related tables? Placing foreign key columns in either table does not seem
to work. So this second method of transformation is also not correct.
Note the pairs of related primary key values shown the above figures. Each pair
represents a set of a project and a corresponding employee. Look at the pairs (1,123)
and (1,234). Each pair indicates a set of related rows from the two tables. For
example, the pair (1,123) indicates that the row for project 1 is related to employee
123, the pair (1,234) indicates that the row for project 1 is related to employee 234,
and so on. In fact, you note that the complete set of pairs represents all the associ-
ations between rows in the two tables. In other words, the set of pairs establishes
the many-to-many relationship. However, the values in the pairs are not present as
foreign keys in either of the two tables. In our above two attempts at transforma-
tion, the real problem is that we do not know where to place the foreign keys—in
the PROJECT relation or in the EMPLOYEE relation. What if you make a sepa-
rate table out of these pairs of related values and use the values in the pairs as
foreign key values? Then this new table can establish the many-to-many relation-
ship. This elegant method is the standard method for representing many-to-many
relationships in the relational data model.
Figure 9-15 illustrates the correct method of transforming a many-to-many rela-
tionship. The table containing the pairs of related values of primary keys is known
as the intersection table.
Note the primary key for the intersection table. The primary key consists of
two parts—one part is the primary key of the PROJECT table and the other part
is the primary key of the EMPLOYEE table. The two parts act as the foreign keys