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

ALLDOCTOR Relation

DrID
10
14
23
37
39
45
36
13
25

DrName Specialty DrPhone

918-756-0831
609-238-3377
732-613-9313
609-246-0331
918-613-1988
609-924-9248
908-535-5228
908-322-5566
609-238-5100

SuprDrID
25
45
36
13
45
25
25
25

Kim Maggio Family Practice

Monika Isaac Neurology

Marcus Kulper Internal Medicine

Ira Goldman Psychiatry

Lawrence Stark Radiology

Robert Martin Surgery General

Elaine Hsu Urology

Leroy Richardson Oncology

Roger Smarts Neurology

10

14
23

45
36 13

Explanation of
UNION operation

37 25

39

Figure 8-12 UNION operation.

Relational algebra solution
ALLDOCTOR: = SUPVRDOCTOR » SUBDOCTOR

Note the syntax for the operation in relational algebra and the symbol U indi-
cating the union operation. The union operation combines the data from the
two relations, eliminating duplication. Note that some doctors, who report to
others and also have doctors reporting to themselves, will be in both relations.
The union operation in relational algebra is similar to the union function in
mathematical set theory. Note the solution shown in Figure 8-12.

Intersection

Features and function

• Operates on two relations.
• Identifies common rows in two relations.
• Both relations must be union-compatible; that is, they must have the same
columns.
• The result is a single relation containing rows and columns as indicated below:
• Rows—only those rows that are common to both relations
• Columns—same columns as in either of the original relations

Example

Refer to Figure 8-11 for the discussion of this operation.

Data request List all the attributes of only those doctors who have doctors
reporting to them and who themselves report to other doctors. These are the
doctors who are both subordinates as well as having supervisory responsibil-
ities. Call the result relation DOCTOR.