Normalization Problems and Solutions › Unification

Unification

In the following example, the “employee-id” attribute migrates to the CHILD entity through two relationships: one with EMPLOYEE and the other with SPOUSE. You might expect that the foreign key attribute would appear twice in the CHILD entity as a result. Since the attribute “employee-id” was already present in the key area of CHILD, it is not repeated in the entity even though it is part of the key of SPOUSE.

This combining of two identical foreign key attributes migrated from the same base attribute through two or more relationships is called unification. In the example, “employee-id”was part of the primary key of CHILD (contributed by the “has” relationship from EMPLOYEE) and was also a non-key attribute of CHILD (contributed by the “has” relationship from SPOUSE). Since both foreign key attributes are the identifiers of the same EMPLOYEE, it is better that the attribute appears only once. Unification is implemented automatically when this situation occurs.

The rules used to implement unification include:

• If the same foreign key is contributed to an entity more than once, without the assignment of rolenames, then all occurrences unify.
• If the occurrences of the foreign key are given different rolenames, then unification does not occur.
• If different foreign keys are assigned the same rolename, and these foreign keys are rolenamed back to the same base attribute, then unification occurs. If they are not rolenamed back to the same base attribute, there is an error in the diagram.
• If any of the foreign keys that unify are part of the primary key of the entity, then the unified attribute remains as part of the primary key.
• If none of the foreign keys that unify are part of the primary key, then the unified attribute is not part of the primary key.

Accordingly, you can override the unification of foreign keys, when necessary, by assigning rolenames. If you want the same foreign key to appear two or more times in a child entity, you can add a rolename to each foreign key attribute.