14.4. Other Transformations

The previous two sections covered the most useful conceptual schema transformations. In this section, several other transformations of lesser importance are considered briefly.

Recall PSG2, our second predicate specialization/generalization theorem (Figure 14.16). This may be generalized further by removing constraints. First note that removing the UC on A’s role in R corresponds to removing the UC’s on A’s roles in each of the S_i predicates (see Figure 14.36). In the absence of additional internal UCs, the R and S_i binaries will be many:many. As an example, let A = Car, B - Status {’company’, ‘private’}, C = Employee, T = has, R = is used by, S_i = is provided for, and S₂ = is privately used by. Allow the same employee to use many company cars and many private cars, and vice versa.

Now suppose the equality constraint is weakened to an upward subset constraint. The T predicate must be retained since there may be instances of A that do not play R, and hence T will not in general be derivable from the S_i. Moreover, subtypes must now be introduced for the specialized predicates (Figure 14.37). The exclusion constraint over the subtype roles is omitted since it is implied by the subtype definitions.

For example, consider the car UoD just discussed, where some cars might not yet be used. In this case the subtypes are CompanyCar and PrivateCar. Cases that require one of these more general forms of PSG2 usually invoke some of the corollaries stated for the original version of the theorem (Figure 14.16).

For example, suppose each employee uses at most one company car and at most one private car. An external UC is now required between the Status and Employee roles in the left-hand version, and a simple UC must be applied to each role of Employee in the right-hand version. As an exercise, draw both schema versions for this UoD and prove that a frequency constraint of ≤2 is implied on the role played by Employee in the left-hand version.

If an object type in an m:n binary has two values, the fact type can be converted to a functional one by converting the object type to one that allows concatenated values. A simple example is shown in Figure 14.38. The transformation from left to right is called value concatenation, and the inverse transform is value separation. In this example, a physician may be trained in medicine, acupuncture, or both. Allowing the both-case to be recorded as a concatenated value (med-acu) is a “quick and dirty” way of effectively creating a set object type (in this case, DisciplineSet).

Appropriate translation rules are needed to license the transformation (e.g., Physician is trained in Discipline ‘med’ iff Physician is trained in DisciplineSet in {’med’, ‘med-acu’}). Moreover, the open/closed-world approach adopted should be the same for both. For example, if the left-hand version has a closed-world interpretation, then the code ‘med’ in the right-hand version means ‘medicine only’.

Although sometimes used as a quick fix, this kind of transformation rapidly becomes unwieldy as the number of atomic values increases, since a list of n atomic values leads to 2” -1 concatenated values (e.g., if we added herbalism and physiotherapy to give four atomic disciplines, we now have 15 concatenated disciplines). In such cases, if functional predicates are required, it is better to replace the binary with n unaries, one for each value, before mapping. Alternatively, if the target system supports set-valued fields (e.g., a nested relational or an object-oriented database), a mapping to a set-valued structure is possible.

In the presence of a pair-subset constraint, a binary may be contracted to a unary; conversely, the unary may be expanded to a binary (see Figure 14.39). Note that the UC on the first role of R is implied by the other constraints (as an exercise, prove this).

As an example of the left-hand schema, consider the fact types (S listed first): Politician is a member of Party; Politician is a minister in Party. Since one can be a minister only in one’s own party, a pair-subset constraint runs from the minister to the member fact type. In the right-hand version, the minister fact type is contracted to the unary: Politician is a minister. Here a minister’s party can be derived using the rule: Politician is a minister in Party iff Politician is a member of Party and is a minister.

In this case, contraction is usually preferred to expansion. However, if the binary to be contracted is 1:1, its second uniqueness constraint may be captured as a textual constraint in the unary version. For example, suppose the following fact types appear in the position of S and R in Figure 14.39: Politician is a member of / includes Party; Politician leads Party. Since each party has at most one leader, there is an extra UC on the lower binary, making it 1:1. Now suppose we contract this binary to the unary: Politician is a leader. The extra UC in the binary version appears in the unary version as a textual constraint: Each Party includes at most one Politician who is a leader. At the time of writing, ORM is being extended to capture such textual constraints graphically using a “unique-where-true” constraint (see the chapter notes).

Previous sections have shown how object types can be absorbed in order to specialize predicates and how nested and coreferenced object types may be removed by flattening. Apart from these cases, modelers sometimes eliminate an entity type from a fact type, phrasing the association in terms of the object type(s) formerly used to reference the entity type (usually however, it is more convenient not to do this).

Figure 14.40 provides an example with a simple reference scheme. Transforming from the left-hand to the right-hand version eliminates the entity type Degree. This weakens the schema, since the entity type and its reference scheme are lost. The translation defines the right-hand predicate in terms of the left. This is an implication rather than an equivalence. The reverse transformation from right to left introduces an entity type. In almost all cases, the left-hand version is preferred because of its richer formal semantics.

This transformation is sometimes performed with a coreferenced object type to which another object type is functionally related. Figure 14.41 provides an example. Here a room is identified by combining its local room number and building. Note the equality constraint; this is implied if the office roles of Employee are mandatory.

Figure 14.41(a) is generally preferable to Figure 14.41(b) for two reasons. First, it facilitates schema changes that add other facts about rooms (e.g., Room has Sizefm²:)). Second, it is natural to think in terms of rooms, and the composite identification scheme is clearly displayed. In general, don’t eliminate an entity type unless you feel that no further details will be recorded for it and that no clarity is lost by its removal.

If you do decide to eliminate a compositely identified entity type, ensure that it is the target of only functional predicates. For example, Figure 14.41(a) includes the fact type Employee works In Room. Suppose we now make this association m:n to cater for some unusual case where an employee may have more than one office. For example, if an employee has to move his/her office from one building to another, he/she may be assigned an office in each building to facilitate working efficiently in both places until the move is completed. It would now be wrong to eliminate Room as in Figure 14.41(b), since the two associations are now m:n, losing the information about which room number goes with which building.

That covers the main conceptual schema transformations of practical relevance in information modeling. Other transformations exist, but we have no space to discuss them here. Some transformations that deal with compound (nonelementary) fact types are discussed later in the context of denormalization and nonrelational mappings.

Exercise 14.4