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

Relational Notation

EMPLOYEE ( EmpNo , EmpName, EmpAddr)

PROJECT ( ProjID, ProjDesc, ProjStart )
ASSIGNMENT ( ProjID, EmpNo )

Foreign Keys: ProjId REFERENCES PROJECT

EmpNo REFERENCES EMPLOYEE

ASSIGNMENT relation

EMPLOYEE relation

ProjID EmpNo

EmpNo EmpName EmpAddr

1 123

123 Daniel R. Grady Jamesburg, NJ 08810

1 234

234 Alice M. Knox Iselin, NJ 08834

1 345

345 Matthew Rader Edison, NJ 08817

2 345

456 Ramola Ruskin Metuchen, NJ 08819

2 456

3 123

PROJECT relation

3 456

ProjID ProjDesc ProjStart

4 234

1 Requirements 2/15/2002

4 345

2 DB Design 4/22/2002

4 456

3 DB Implementation 8/31/2002

5 234

4 User Training 8/1/2002

5 DB Fine Tuning 9/15/2002 5 345

Figure 9-15 Transformation of many-to-many relationship: correct method.

to establish both sides of the many-to-many relationship. Also, observe that each of
the two relations PROJECT and EMPLOYEE is in a one-to-many relationship with
the intersection relation ASSIGNMENT.
Now, let us review how queries involving data from the two related tables work.

Which are the projects related to employee 456? Read the intersection table
by values in part of the primary key columns, namely, EmpNo attribute
showing values for employee key numbers. Find the rows having the value
456 for this part of the primary key. Read the PROJECT table by values in
its primary key column. Find the rows having the values 2, 3, and 4 for
the 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 intersection
table by values in part of the primary key columns, namely, ProjID attribute
showing values for project key numbers. Find the rows having the value 1 for
this part of the primary key. Read the EMPLOYEE table by values in its
primary key column. Find the rows having the values 123, 234, and 345 for the
primary key attribute. Getting the result for this query is straightforward and
easy.

To end our discussion of transformation of many-to-many relationships, let us
summarize the main points.