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

WORKER Relation

WorkerNo
1111
1287
3917
4467
5179

Name
Morris
Vitale
Nagel
Hart
Grasso

HourlyRate
24.50
20.00
18.00
20.50
22.50

SkillCode
ELE
MAS
ROF
ELE
PLM

SupvId

1111

BUILDING Relation

BuildingID Address Type Status

H245 135 Green Street House S1

H267 212 Tices Road House S4

O123 295 Hillside Avenue Office S2

O156 15 Camner Terrace Office S3

T451 23 Oaks Drive Townhouse S5

SKILL Relation ASSIGNMENT Relation

SkillCode
MAS
FRM
ROF
ELE
PLM

SkillType
Masonry
Framing
Roofing
Electric
Plumbing

WklyHrs
35
40
35
35
40

WorkerNo
1111
1287
5179
4467
1287

BuildingID
H245
O123
T451
O156
H267

StartDate
15-Mar
15-Feb
1-Mar
15-Apr
1-Apr

DaysWorked
10
8
7
15
9

Figure 8-5 Relational tables.

WORKER ( WorkerNo, Name, HourlyRate, SkillCode, SupvID)
Foreign Keys: SkillCode references SKILL

SupvID references WORKER

ASSIGNMENT (WorkerNo, BuildingID, StartDate, DaysWorked)
Foreign Keys: WorkerNo references WORKER

BuildingID references BUILDING

BUILDING ( BuildingID , Address, Type, Status)

SKILL ( SkillCode, SkillType, WklyHrs)
Figure 8-6 Relational data model: notation.

• The foreign key statement includes the name of the foreign key and the name
of the parent relation.
• Note the foreign key SupvID indicating a recursive relationship.

DATA INTEGRITY CONSTRAINTS

It is essential that a database built on any specific data model must ensure the valid-
ity of the data. The data structure must be meaningful and be truly representative
of the information requirements. Constraints are rules that make sure proper