|Lesson 5||One-to-one relationships, part 2 |
|Objective|| Identify one-to-one Relationships|
Relationship types are determined by interpreting business rules and common-sense rules that characterize entities in relationships. Consider this business rule: Employees in the company are each assigned their own office.
This rule means that for each
employee there exists a unique
office, and for each
office there exists a unique
employee. The entities in the example are EMPLOYEE and OFFICE, with EMPLOYEE:OFFICE forming a
, and OFFICE:EMPLOYEE forming a 1:1 relationship.
One-to-one relationships should be analyzed carefully. For example, what if the organization grows and more employees need to be hired? Is the organization willing to abandon its business rule and assign more than one employee to an office?
The designer should verify the intent behind all one-to-one relationships with the organization's managers.
The next lesson defines one-to-many relationships.
Example of One-to-One Relationships
Consider, for a moment, an airport in a small town, where both the airport and the town are described in a database of small-town airports. Each of these might be represented by an instance of a different type of entity.
The relationship between the two instances can be expressed as "The airport is located in one and only one town, and the town has one and only one airport."
This is a true one-to-one relationship because at no time can a single airport be related to more than one town, and no town can be related to more than one airport. (Although there are municipalities that have more than one airport, the towns in the database are too small for that to ever happen.)
If we have instances of two entities (A and B) called Ai and Bi, then a one-to-one relationship exists if at all times Ai is related to no instances of entity B or one instance of entity B, and Bi is related to no instances of entity A or one instance of entity A.
True one-to-one relationships are very rare in business. For example, assume that DistributedNetworks decides to start dealing with a new distributor of DVDs.
At first, the company orders only one specialty title from the new distributor. If we peered inside the database, we would see that the instance of the distributor entity was related to just the one merchandise item instance.
This would then appear to be a one-to-one relationship. However, over time, DistributedNetworks may choose to order more titles from the new distributor, which would violate the rule that the distributor must be related to no more than one merchandise item.
one To one Relationships - Exercise
Six-Step Relational Database Design