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

EMPLOYEE Relation

EmpNo
E10
E20
E30
E40

EmpName
Charles
Mary
Eldon
Paul

Phone Ext.
418
236
179
522

DeptNo
D200
D555
D300

DEPARTMENT Relation

DeptNo
D100
D200
D300

DeptName
Engineering
Accounting
Marketing

Location
West
South
East

Figure 8-8 Referential integrity rule.

denote optional relationships. That means that employee Paul is not assigned to any
department.
When one relation is related to another through foreign key values, the refer-
ences of the relationship must be clearly indicated. There should not be any ambi-
guities. A foreign key value must clearly indicate how that row is related to a row
in the other relation. The referential integrity rule addresses the establishment of
clear references between related tables. The referential integrity rule, therefore,
applies to sets of two relations.

Referential integrity rule : The value of a foreign key in a table must either be null or
be one of the values of the primary key in the related table.

Functional Dependencies

Let us use the EMPLOYEE, PROJECT, and ASSIGNMENT relations shown in
Figure 8-7 to examine the concept of functional dependency. Functional dependency
in a relation arises because the value of one attribute in a tuple determines the value
for another attribute. Let us look at some examples.
In the EMPLOYEE relation of Figure 8-7, note the tuple with key value 213-36-
7854. This determines that the tuple represents a distinct employee whose name is
Samuel Kahn and whose position is Analyst. Now, look at the tuple with key
value 311-33-4520. This key value uniquely identifies an employee whose name is
Kaitlin Hayes and whose position also happens to be Analyst. Let us inspect the
dependencies.
Which attribute's values determine values of other attributes? Does the value
of the primary key uniquely and functionally determine the values of other
attributes?

• Key value 213-36-7854 uniquely and functionally determines a specific row
representing Samuel Kahn with position Analyst.
• Key value 311-33-4520 uniquely and functionally determines a specific row
representing Kaitlin Hayes with position Analyst.