La interpretación de Burge de los experimentos mentales

4.8.1 VARIOUS FORMS OF DEPENDENCIES

Functional dependencies are an important form of integrity constraints. However, other forms of dependencies also exist and may play an important role in good relational database design. In this section, we discuss a type of dependency called multivalued dependency. Other dependencies also exist. For example, multivalued dependencies can be further generalized into join dependency, which is said to hold over a relation R if R is decomposed into a set of relations R1, R2, …, Rn, which forms a lossless-join decomposition of R. Another example is inclusion dependency, which is a statement of the form that some columns of a relation are contained in other columns (usually of another relation). A foreign key constraint is an example of an inclusion dependency, because the referring columns in one relation must be contained in the primary key columns of the referenced relation. Since join dependencies and inclusion dependencies are not very influential in database design, we will not discuss them any more. Interested readers are referred to [Ramakrishnam 1998] for a little more detailed discussion. In the following we will briefly discuss multivalued dependencies.

4.8.2 MULTIVALUED DEPENDENCIES

4.8.2.1 Comparison between FD and MVD

Multivalued dependencies (MVDs) can be considered as a generalization of functional dependencies (FDs). We can introduce MVD by comparing it with FD:

• For functional dependency α→β: α's value uniquely determines β's value; other attributes are not affected in any way.

• For multivalued dependency α→→β: α's value does not uniquely determine β's value, but α and β form a constraint for other attributes in the relation (let us call these attributes "the third part"). This constraint can be stated as "the relationship between α and β is independent of the relationship between α and R - α - β."

FDs are usually referred to equality-generating dependencies while MVDs are referred to as tuple-generating dependencies, because it requires generation of new tuples if the required property does not hold. The requirement is shown in the formula of the formal definition. In this definition, four tuples are needed; all have same value on α but different on ti[β].

Formal definition of Multivalued dependency (Md) α→→β if and only if we have four tuples which satisfy

t1[α] = t2[α] = t3[α] = t4[α],

t3[β] = t1[β]

t3[R - α - β] = t2[R - α - β]

t4[β] = t2[β]

t4[R - α - β] = t1[R - α - β].

Note that attributes in the relation schema fall in three parts: α, β, and γ = R - α - β (all of other attributes). Intuitively, these conditions indicate that for the same left-hand side α of an MVD, if we have two different values at the right-hand side β in two tuples, then we should be able to find two other tuples, with their γ values swapped. Note that FDs can be considered as a special case of MVDs, because the right-hand side value is unique, the four required tuples are reduced to one.

We also need the concept of trivial MVD: for α→→β, either we have β⊆α or α∪ β. In other words, the third part is empty; this can be compared with the concept of trivial FD.

4.8.2.2 Important properties

An interesting and important property for MVD is that in the three parts, the second part β and the third part γ are "symmetrical" (this is actually complementation rule as to be discussed soon):

Note that now a set of dependencies may include both FDs and MVDs, and will be denoted by D (instead of F). Similarly, we have D+ (instead of F+). We also have the remarks on primary keys and candidate keys: they are still determined by functional dependencies only. For superkey, the definition stays the same. In other words, MVDs do not contribute to the concept of keys.

As an example, consider the Easter gifts given to children by their mothers, as shown in the relation Gift = (MCRGP) in Table 4.8 (a).

Table 4.8(a) An example for multivalued dependency

M(Mother) C(Child) R(Relationship) G(Gift) P(Price)

Jo Ann Mary Mary David Kim Tom son daughter son PC car teddy-bear 1000 10000 20

You may think that Mary is unfair with her children, because price values of gifts differ so much. (Jo Ann has only one child, David, so there is no problem.) To be a good mother, Mary should give her son a car and her daughter a teddy bear as well (that is, the previous table should add two rows). This is shown in Table 4.8(b). This example shows why MVDs are called tuple-generating.

Table 4.8(b) An example for multivalued dependency (cont.)

M(Mother) C(Child) R(Relationship) G(Gift) P(Price)

Jo Ann Mary Mary Mary Mary David Kim Kim Tom Tom son daughter daughter son son PC Car teddy-bear teddy-bear car 1000 10000 20 20 10000

The second table satisfies MVD: M→→CR. Note we also have M →→ GP. As a final remark, we point out that Armstrong's axioms can be extended to MVDs, but they are much more complicated, and the detail is not discussed here.

4.8.3 FOURTH NORMAL FORM (4NF)

We should notice that normal form higher than BCNF is needed, because anomalies may still exist even in BCNF (note the relation schema does not have any nontrivial FDs). Fourth normal form (4NF) is such a normal form, which can be defined in terms of FDs and MVDs.

4.8.3.1 Definition of 4NF

A relation schema R is in 4NF with respect to a set D of FDs and MVDs if, for all MVDs in D+ of the form α→→β where α⊆ R and β⊆ R, at least one of the following holds:

(b) α is a superkey for schema R.

Note that if a relation schema is in 4NF then it is also in BCNF. This is because if a schema R is not in BCNF, then it cannot be in 4NF. Finally, just like the case of BCNF, a database design is in 4NF if all relation schemas are in 4NF.

4.8.3.2 Decomposition into 4NF

The algorithm for lossless-join decomposition to 4NF is similar to BCNF decomposition. In this process we check some α→→β violating 4NF instead of checking some α→β violating BCNF.

As an example, the Relation(MCRGP) in the above example is not in 4NF (Why?). We can decompose it to two relations MCR (containing information about mother and children) and MGP (containing information about mother and gifts available). (What are the tuples in these two tables?) Each small relation has trivial MVDs only, so is in 4NF. (What is the primary key for MGP?)

4.9

REMARK ON OBJECT-ORIENTED LOGICAL DATA

MODELING

Recently object-oriented data modeling approaches have received much attention. The object-oriented data modeling techniques can be considered as out-grown from Non-First Normal Form (sometimes denoted as NF2). Let us re-consider the example presented in the last section. Instead of decomposing the relation into smaller relations in 4NF, we follow the opposite direction by grouping atomic values into sets. This consideration results in Table 4.9.

The NF2 representation serves as a model which is semantically clearer than the one in 1NF, because the related information is grouped together. For example, Table 4.10 clearly indicates Mary has two kids, one is Kim, another is Tom, and each kid has a gift Car priced 10000 and a teddy bear priced 20. In other words, each kid can be viewed as a nested relation contained in the mother. In fact, we can view "mother" as a structured (or complex) data type suitable for object-oriented data modeling. Extensions to SQL have been developed to allow complex types, including nested relations, as well as object-oriented features. Some aspects of object-oriented data modeling will be briefly addressed in Chapter 6.

Table 4.9 Example to illustrate NF2

M(Mother) C(Child) R(Relationship) G(Gift) P(Price)

Jo Ann Mary David Kim Tom son daughter son PC car teddy-bear teddy-bear car 1000 10000 20 20 10000