Database Reference
In-Depth Information
nonidentifying relationships between strong entities, identifying relationships between ID-
dependent entities, relationships in mixed entity patterns, relationships between a supertype
and its subtypes, and recursive relationships. We conclude this section with a discussion of
special cases of ternary relationships.
Relationships Between Strong entities
As you learned in Chapter 5, nonidentifying relationships between strong entities are char-
acterized by their maximum cardinality. There are three types of these relationships: 1:1, 1:N,
and N:M.
1:1 Relationships Between Strong entities
After the tables corresponding to the strong entities have been designed, a 1:1 relationship
between these entities can be represented in one of two ways. You can place the primary key
of the first table in the second as a foreign key, or you can place the primary key of the second
table in the first as a foreign key. Figure 6-8 shows the representation of the 1:1 nonidentify-
ing relationship between CLUB_MEMBER and LOCKER. In Figure 6-8(a), MemberNumber
is placed in LOCKER as a foreign key. In Figure 6-8(b), LockerNumber is placed in CLUB_
MEMBER as a foreign key.
Either of these designs will work. If you have a club member's number and want his or
her locker, then, using the design in Figure 6-8(a), you can query the LOCKER table for the
given value of MemberNumber. But if you have the LockerNumber and want the club mem-
ber's data, then, using the design in Figure 6-8(a), you can query the LOCKER table for the
LockerNumber, obtain the MemberNumber, and use that value to query the CLUB_MEMBER
table for the rest of the club member's data.
Follow a similar procedure to verify that the design in Figure 6-8(b) works as well. However,
one data constraint applies to both designs. Because the relationship is 1:1, a given value of a
foreign key can appear only once in the table. For example, in the design in Figure 6-8(a), a given
value of MemberNumber can appear just once; each value must be unique in the LOCKER table.
If a value of MemberNumber were to appear in two rows, then a member would be assigned to
two lockers, and the relationship would not be 1:1.
To cause the DBMS to enforce the required uniqueness of the foreign key value, we define
the foreign key column as unique. This can be done either directly in the column definition
of the foreign key (in which case there is no designation in the table diagram) or by defining
the foreign key as an alternate key. This latter technique, though common, is a bit confusing
because, logically, MemberNumber is not an alternate key for LOCKER. We are just using the
fact that alternate keys are unique to document the uniqueness of the foreign key in a 1:1 rela-
tionship. Depending on the database design software being used, the alternate key designation
CLUB_MEMBER
LOCKER
Figure 6-8
The Two Alternatives for
Transformation of a 1:1
Relationship Between
Strong Entities
MemberNumber
LockerNumber
MemberName
Phone
Email
LockerRoom
LockerSize
MemberNumber (FK) (AK1.1)
(a)
With Foreign Key in LOCKER
CLUB_MEMBER
LOCKER
MemberNumber
LockerNumber
MemberName
Phone
Email
LockerNumber (FK) (AK1.1)
LockerRoom
LockerSize
(b)
With Foreign Key in CLUB_MEMBER
Search WWH ::
Custom Search