RELATIONAL DATA MODEL FUNDAMENTALS - Database Design and Development: An Essential Guide for IT Professionals - page 240

relation as the foreign key value in the corresponding tuple of the second rela-
tion. The first relation may be referred to as the parent relation and the second
as a child.
• If tuples of a relation are related to some other tuples of the same relation,
then the foreign key attribute is included in the same relation. This is a
recursive relationship. For example, in an EMPLOYEE relation, some tuples
representing employees may be related to other tuples in the same relation rep-
resenting supervisors.
• Foreign key attributes need not have the same names as the corresponding
primary key attributes.
• However, a foreign key attribute must be of the same data type and length of
the related primary key attribute.
In the above figure, you notice that some tuples show null values for the foreign
key attributes. What is the significance of the null values?
Optional Relationship Consider a tuple in the EMPLOYEE relation with a null
value in the foreign key column. This shows that the specific tuple is not linked to any
tuple in the DEPARTMENT relation. This means that this particular employee is
not assigned to any department. He or she could be a new employee not yet assigned
to a department or an employee on special assignment not tied to a specific depart-
ment. A null value in the foreign key column indicates the nature of the relationship.
Not all employees need be associated with a department. Null values in the foreign
key column indicate an optional relationship between the two relations.
Mandatory Relationship In the EMPLOYEE relation, suppose that null values
are not allowed in the foreign key. This requires specific discrete values to be present
in all the tuples of this relation. Every tuple in the EMPLOYEE relation, therefore,
points to a related tuple in the DEPARTMENT relation. In other words, every
employee must be related to a department. If null values are not allowed in the
foreign key, the relationship between the two relations is a mandatory relationship.
Relational Model Notation
Figure 8-5 gives an example of relational tables. Figure 8-6 presents a standard nota-
tion used to represent this relational data model.
Note the following description of the notation:
• The notation for each relation begins with the name of the relation. Exam-
ples—WORKER, ASSIGNMENT, BUILDING, SKILL.
• For each relation, the column names are enclosed within parenthesis. These are
the attributes for the relation.
• Primary key attributes are indicated with underscores. Examples— BuildingID ,
SkillCode .
• Statements immediately following the notation of a relation indicate the
foreign keys within that relation. Example—Foreign keys: SkillCode references
SKILL.