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

1, *

Relational Notation

assigned
to

EMPLOYEE

EMPLOYEE (EmpNo, EmpName, EmpAddr)
PROJECT ( ProjID, ProjDesc, ProjStar t)

1, *

ASSIGNMENT ( ProjID, EmpNo )

PROJECT

Foreign Keys: ProjId REFERENCES PROJECT NOT NULL

EmpNo REFERENCES EMPLOYEE NOT NULL

EMPLOYEE relation

ASSIGNMENT relation

EmpNo EmpName EmpAddr

ProjID EmpNo

123 Daniel R. Grady Jamesburg, NJ 08810

1 123

234 Alice M. Knox Iselin, NJ 08834

1 234

345 Matthew Rader Edison, NJ 08817

1 345

456 Ramola Ruskin Metuchen, NJ 08819

2 345

2 456

3 123

PROJECT relation

3 456

ProjID ProjDesc ProjStart

4 234

1 Requirements 2/15/2002

4/22/2002
8/31/2002
8/1/2002
9/15/2002

4 345

2 DB Design

3 DB Implementation 4 456

5 234

4 User Training

5 DB Fine Tuning 5 345

Figure 9-17 Many-to-many relationship: minimum cardinality.

Note the cardinality indicators (1,*) shown next to the PROJECT object set and
(1,*) shown next to the EMPLOYEE object set. What do these cardinality indica-
tors represent? The indicators represent the following conditions:

An employee may be assigned to many projects.
A project may have many employees.
Every employee must be assigned to at least one project. That is, an employee
instance must be associated with a minimum of 1 project instance. In other
words, every employee instance must participate in the relationship. The rela-
tionship as far as the employee instances are concerned is mandatory.
Every project must have at least one employee. That is, a project instance must
be associated with a minimum of 1 employee instance. In other words, every
project instance must participate in the relationship. The relationship as far as
the project instances are concerned is mandatory.

Carefully observe the transformed relationship described in the figure. Look at
the intersection relation and the concatenated primary key of this relation. As you
know, each part of the primary key forms the foreign key. Note the two one-to-many
relationships and the corresponding tables showing attribute values. As discussed
in the previous subsection on one-to-many relationship the foreign keys in the inter-
section table, that is, either of the two parts of the primary key table, cannot be nulls.
You may stipulate the constraints with the words “NOT NULL” in the Foreign Key
statement for the intersection table. However, the two foreign keys are part of the
primary key, and because the primary key attribute cannot have nulls, the explicit
stipulation of “NOT NULL” may be omitted.