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

DEPARTMENT Relation

DeptCode DeptName DeptLocation

101 Administration New York

201 Finance Chicago

301 Marketing Atlanta

303 Sales Boston

LOGICAL
LINKS

408 Production Detroit

501 Information Technology San Francisco

EMPLOYEE Relation

SocSecNumber EmployeeName Phone Position DeptCode

214-56-7835
123-44-5546
101-54-3838
213-36-7854
311-33-4520
512-22-8542
111-22-3344
122-65-5378

Robert Moses 516-777-9584 Programmer 501

Kassia Raj

718-312-4488 Analyst

Andrew Rogers 212-313-1267 Manager 408

Samuel Prabhu 212-126-3428 Controller 201

Kaitlin Jones

718-567-4321 Assistant

Carey Smith 732-346-5533 Senior VP 301

Amanda Lupo

908-212-5629 Executive VP

101

Tabitha Williams 215-576-4598 DBA 501

Figure 8-4 Department and employee relations: relationship.

Primary key. One of the candidate keys actually selected as the key for a relation.

Surrogate key. A key that is automatically generated for a relation by the com-
puter system, for example, CustomerNumber, generated in sequence by the system
and assigned to CUSTOMER rows. Surrogate keys ensure that no duplicate values
are present in the relation.

Relationships Through Foreign Keys

You have noted above that the relational model is superior to other conventional
data models because it does not use physical links to establish relationships. The
relational model uses logical links. How is this done? What is a logical link?
Figure 8-4 presents two relations, EMPLOYEE and DEPARTMENT. Obviously,
these two relations are related to each other because there are associations between
employees and departments. One or more employees are assigned to a particular
department; an employee is assigned to a specific department.
Observe the links shown between tuples in the EMPLOYEE relation to corre-
sponding tuples in the DEPARTMENT relation. The DeptCode attribute in the
EMPLOYEE relation is called a foreign key attribute. Note especially the value of
the foreign key attribute and the value of the primary key attribute of the related
row in the other relation.
Let us summarize how relationships are established in the relational data model.

• Relationships in the relational data model are established through foreign keys,
not physical pointers.
• The logical link between a tuple in the first relation and a tuple in a second
relation is established by placing the primary key value in the tuple of the first