Database Reference
In-Depth Information
BUILDING-MATERIAL-SUPPLIER
BuildingID
B45
B45
B51
B51
B93
Material
Sheet Rock
Ceiling Paint
Sheet Rock
Shower Stall
SupplierNo
S67
S72
S72
S67
S75
Ceiling Paint
Figure 10-10
Relation with join dependency.
ceiling paint from supplier S72. Suppose you have a constraint that suppliers may
supply only certain materials to specific buildings even though a supplier may be
able to supply all materials. In this example, supplier S72 can supply sheet rock to
building B45, but to this building B45, only supplier S67 is designated to supply sheet
rock. This constraint imposes a join dependency on the relation. However, the way
the relation is composed, it does support the join dependency constraint. For
example, there is no restriction to adding a row (B45, Sheet Rock, S72). Such a row
would violate the join constraint and not be a true representation of the informa-
tion requirements.
This step in normalization process deals with this type of problem. Once this
type of problem is resolved, the data model is transformed to a data model in fifth
normal form. In other words, the condition for a fifth normal form data model is as
follows:
If a data model is in the fifth normal form, no join dependencies exist.
Here is what must be done to make this transformation.
Transformation to Fifth Normal Form (5NF)
Remove join dependencies.
Figure 10-11 shows the resolution of the join dependencies. The three relations
are in the fifth normal form.
Note something important in the relations shown in the figure. If you join any
two of the three relations, the result will produce incorrect information, not the true
real-world information with the join dependency. To arrive at the correct original
real-world information with the join dependency constraint, you have to join all
three relations.
Domain-Key Normal Form
This normal form is the ultimate goal of good design of a proper relational data
model. If a data model is in the domain-key normal form (DKNF), it satisfies the
conditions of all the normal forms discussed so far. The objective of DKNF is to
make one relation represent just one subject and to have all the business rules
expressed in terms of domain constraints and key relationships. In other words, all
rules could be expressly defined by the relational rules themselves.
Search WWH ::




Custom Search