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

ClntNo ContactNo

ContactName

ClntName 1 1

assigns CONTACT

PERSON

CLIENT

ClntAddr

ContactPhone

Relational Notation

CLIENT (ClntNo, ClntName, ClntAddr, ContactNo)

Foreign Key: ContactNo REFERENCES CONTACT PERSON

CONTACT PERSON ( ContactNo ,ContactName, ContactPhone)

CLIENT relation

ClntNo ClntName ClntAddr ContactNo

11111 ABC Industries

6 William Street, Jamesburg, NJ 08810 234

Progressive Systems

22222 1 Bradford Place, Iselin, NJ 08834 123

Rapid Development 47 Woods Drive, Edison, NJ 08817

33333

44444 Richard Associates 55 Walker Lane, Metuchen, NJ 08819

Quality Consulting

55555 35 Rues Ave., E. Brunswick, NJ 08821 345

ContactNo ContactName

ContactPhone

CONTACT PERSON
relation

123 Mary Williams 732-345-8100

234

Winston Poyser
Lisa Moore

732-555-4000

345 732-767-5300

Figure 9-7 Transformation of one-to-one relationship.

CONTACT PERSON relation, not in the CLIENT relation. Figure 9-8 presents this
better transformation.
Foreign keys links two relations. If so, you must be able to get answers to queries
involving data from two related tables by using the values in foreign key columns.
From Figure 9-8, examine how results for the following queries are obtained.

Who is the contact person for client number 22222? Read the CONTACT
PERSON table by values in the foreign key column. Find the row having the
value 22222 for the foreign key attribute.
Who is the client for contact person number 345? Read the CONTACT PERSON
table by values in the primary key column. Find the row having the value 345
for the primary key attribute. Get the foreign key value of this row, namely,
55555. Read the CLIENT table by values in the primary key column. Find the
row having the value 5555 for the primary key attribute.

Let us summarize the points on transformation of one-to-one relationships.

• When two relations are in a one-to-one relationship, place a foreign key column
in either one of the two relations. Values in the foreign key column for rows in
this table match with primary key values in corresponding rows of the related
table.
• The foreign key attribute has the same data type, length, and domain values as
the corresponding primary key attribute in the other table.