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

CustNo OrderNo

OrderDate

CustName 1 *

places

CUSTOMER

ORDER

CustAddr

OrderAmount

Relational Notation

CUSTOMER (CustNo, CustName, CustAddr, OrderNo1, ………)
Foreign Key: OrderNo1, OrderNo2, OrderNo3 REFERENCES ORDER

ORDER (OrderNo, OrderDate, OrderAmount)

CustNo

CustName CustAddr OrderNo1 OrderNo2 OrderNo3

CUSTOMER
relation

1111 ABC Industries Jamesburg, NJ 2 4 6

Progressive Systems Iselin, NJ

1112 3 5

Rapid Development

1113 Edison, NJ 1

OrderNo OrderDate OrderAmount

1 10/1/2002 2,122.50

2 10/3/2002 3,025.00

ORDER
relation

3 10/6/02 4,111.25

4 10/17/2002 3,005.50

5 10/19/2002 7,000.00

6

10/25/02 6,540.00

Figure 9-10 Transformation of one-to-many relationship.

The figure also indicates how individual instances of these two object sets are
associated with one another. You see a clear one-to-many relationship—one cus-
tomer can have one or more orders. So how should you transform this relationship?
As you know, the associations are established through the use of a foreign key
column. But in which table do you place the foreign key column? For transforming
the one-to-one relationship, we noted that you might place the foreign key column
in either relation. In the same way, let us try to place the foreign key in the
CUSTOMER relation. Figure 9-10 shows this transformation of a one-to-many
relationship.
What do you observe about the foreign keys in the transformed relations? In the
CUSTOMER relation, the row for customer 1113 needs just one foreign key column
to connect to order 1 in the ORDER relation. But the row for customer 1112 seems
to need two foreign key columns, and the row for customer 1111 seems to require
three foreign key columns. What if there is a customer with 50 orders? How many
foreign key columns are sufficient in the CUSTOMER relation? How will you
search for a particular order from the several foreign keys in the CUSTOMER
relation? Obviously, this transformation is not correct.
We can try another solution by placing the foreign key column in the ORDER
relation instead of including the foreign key column in the other related table.
Figure 9-11 illustrates the correct solution.
Examine this figure. First, you notice that there is no need for multiple foreign
keys to represent one relationship. Multiple rows in the ORDER relation have the
same value in the foreign key column. This indicates the several orders related to