Designing Relational Database Systems (Dv-Mps Designing)

All the relationships discussed so far have been binary relationships having two participants. Unary relationships have only one participant—the relation is associated with itself. The classic example of a unary relationship is Employee to Manager. One's manager is, in most cases, also an employee with a manager of his or her own.

Unary relationships are modeled in the same way as binary relationships—the candidate key of the primary relation is added to the foreign relation. The only difference is that the primary and foreign relations are the same. Thus, if the candidate key of the Employee relation is EmployeeID, declared on the EmployeeID domain, then you'd add an attribute to the relation called perhaps ManagerID, also declared on the EmployeeID domain, as shown in Figure 3-14.

Figure 3-14. A unary relationship exists when a relation is linked to itself.

Unary relationships can be of any cardinality. One-to-many unary relationships are used to implement hierarchies, such as the organizational hierarchy implicit in the Employee-Manager relationship. Many-to-many unary relationships, like their binary counterparts, must be modeled with a junction table. Unary relationships can also be optional on the one side, as shown in Figure 3-14. The CEOs of most organizations do not have a manager. (The stockholders don't count unless they're an independent part of the data model.)