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

0, ∗

Relational Notation

assigned
to

EMPLOYEE

EMPLOYEE (EmpNo, EmpName, EmpAddr)

0, ∗

PROJECT ( ProjID, ProjDesc, ProjStart )

ASSIGNMENT ( AsmntNo, ProjID, EmpNo )

PROJECT

Foreign Keys: ProjId REFERENCES PROJECT

EmpNo REFERENCES EMPLOYEE

EMPLOYEE relation

EmpNo EmpName EmpAddr

ASSIGNMENT relation

123 Daniel R. Grady Jamesburg, NJ 08810

AsmntNo ProjiD

1
1
1
2
3
4
4

EmpNo
123
234
345
345
123
234
345
456

234 Alice M. Knox Iselin, NJ 08834

10
11
12
13
14
15
16
17
18

345 Matthew Rader Edison, NJ 08817

456 Ramola Ruskin Metuchen, NJ 08819

PROJECT relation

ProjID ProjDesc ProjStart

1 Requirements 2/15/2002

2 DB Design 4/22/2002

3 DB Implementation 8/31/2002

5

4 User Training 8/1/2002

5 DB Fine Tuning 9/15/2002

Figure 9-18 Many-to-many relationship: alternative approach.

Let us interpret the mandatory nature of this relationship. The mandatory con-
dition says that, after transformation, for every primary key value in the PROJECT
relation, there must be at least one row in the EMPLOYEE relation with the same
value for the foreign key attribute. Such a condition is not needed in a relational
model to satisfy any integrity constraints. Therefore, it is not generally shown in the
relational model. Any enforcement of this mandatory condition must be imple-
mented by other means in the database system.
Before we end this discussion, let us also consider the corresponding optional
condition. Assume cardinality indicators of (0,*) next to the EMPLOYEE object
set. What are the implications in the transformation to a relational data model?
What is the meaning of the cardinality indicators (0,*) next to the EMPLOYEE
object set? The indicators represent the following conditions:

A project may have many employees. Not every project need have employees.
That is, some project instances may not be associated with any employee
instance at all. At a minimum, a project instance may be associated with no
employee instance or with zero employee instances. In other words, not every
project instance needs to participate in the relationship. The relationship as
far as the project instances are concerned is optional.

What is the interpretation of the optional nature of this relationship? The
optional condition says that, after transformation, there may be rows in the