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

ProjID

EmpNo

ProjDesc

EmpName * *

assigned
to

EMPLOYEE

PROJECT

EmpAddr

ProjStart

EmpNo

EmpName EmpAddr

Jamesburg, NJ 08810

123 Daniel R. Grady

EMPLOYEE
relation

234 Alice M. Knox Iselin, NJ 08834

345 Matthew Rader Edison, NJ 08817

456 Ramola Ruskin Metuchen, NJ 08819

ProjID ProjDesc ProjStart EmpNo1 EmpNo1 EmpNo1

Requirements 2/15/2002

1 123 234 345

2 DB Design 4/22/2002 345 456

PROJECT
relation

3 DB Implementation 8/31/2002 123 456

4 User Training 8/1/2002 234 345 456

DB Fine Tuning 9/15/2002

5 234 345

Related project-employee instance pairs : (1, 123), (1, 234), (1, 345), (2, 345), (2, 456),
(3, 123),(3, 456), (4, 234), (4, 345), (4, 456), (5, 234), (5, 345)
Figure 9-13 Transformation of many-to-many relationship: first method.

primary key column. Find the rows having the values 123, 234, and 345 for
its primary key attribute. Getting the result for this query seems to be
workable.

Because the transformation from the first method does not work, let us try
another solution by placing the foreign key columns in the EMPLOYEE relation
instead of including the foreign key column in the other related table. Figure 9-14
illustrates this method of transformation.
Where are the foreign keys in the transformed relations? In the EMPLOYEE
relation, the row for employee 123 needs two foreign key columns whereas the rows
for employees 234 and 456 need three foreign key columns each and the row for
employee 345 needs four foreign key columns. By reasoning similar to that for the
first method, if an employee is related to 25 projects over time, then you need to
have that many foreign key columns in the EMPLOYEE relation.
Let us examine how queries involving data from two related tables work.

Which are the projects related to employee 456? Read the EMPLOYEE table by
values in the primary key column. Find the row having the value 456 for the
primary key attribute. Get the foreign key values of this row, namely, 2, 3, and
4. Read the PROJECT table by values in the primary key column. Find the
rows having the values 2, 3, and 4 for its primary key attribute. Getting the
result for this query seems to be workable.
What are the names of employees assigned to project 1? Read the EMPLOYEE
table by values in the foreign key columns. But which foreign columns? All of
them? Right away, you note that finding the result for this query is going to
be very difficult.