Database Reference
In-Depth Information
BUILDING-MATERIAL
MATERIAL-SUPPLIER
BuildingID
B45
B45
B51
B51
B93
Material
Sheet Rock
Ceiling Paint
Sheet Rock
Shower Stall
Material
Sheet Rock
Ceiling Paint
Sheet Rock
Shower Stall
Ceiling Paint
SupplierNo
S67
S72
S72
S67
S75
Ceiling Paint
BUILDING-SUPPLIER
BuildingID
B45
B45
B51
B51
B93
SupplierNo
S67
S72
S72
S67
S75
Figure 10-11
Data model in fifth normal form.
EMPLOYEE
(EmpID, EmpName, SkillType, TrainerID)
TRAINER
(TrainerID, TrainerName, Location, SubjectArea)
Business rule
:
An employee can have many trainers, but only a specific trainer for
each skill type. A trainer can train only in his or her subject area.
Figure 10-12
Relations not in DKNF.
Domain constraints impose rules on the values for attributes—they indicate
restrictions on the data values. In DKNF, every other rule must be expressed clearly
in terms of keys and relationships without any hidden relationships. Consider the
relations shown in Figure 10-12 and also note the accompanying business rule.
How do you know if the relations are in DKNF? You cannot know this until you
are aware of the business rule. From the business rule, you understand that an
employee can have multiple skill types. Therefore, the primary key EmpId of the
EMPLOYEE relation cannot be unique. Furthermore, trainer is related to skill type,
and this is a hidden relationship in the relation. There must also be an explicit rela-
tionship between skill type and subject area.
Figure 10-13 resolves these discrepancies and expresses the business rule and the
relationships correctly. The resultant data model is in DKNF.
NORMALIZATION SUMMARY
Let us go back and review the normalization approach covered so far. Compare this
approach with the method of creating a semantic data model first and then trans-
forming the semantic data model into a relational data model. Consider the merits
and disadvantages of either method. Also, think about the circumstances and con-
Search WWH ::
Custom Search