Database Reference
In-Depth Information
COMPANY
COMPANY
CompanyName
CompanyName
City
Country
Volume
City
Country
Volume
PHONE_CONTACT
CompanyName
Contact
PHONE_CONTACT
CompanyName (FK)
Contact
PhoneNumber
PhoneNumber
Figure 6-14
Transformation of
ID-Dependent Entities that
Store Multivalued Attributes
(a)
Data Model with Multivalued
Attributes from Figure 5-29
(b)
Database Design to
Store Multivalued Attributes
Multivalued attributes
The third use for ID-dependent entities is to represent multivalued entity attributes, as
illustrated in Figure 6-14. Figure 6-14(a) is a copy of Figure 5-29. Here COMPANY has a multi-
valued composite, (Contact, PhoneNumber), that is represented by the ID-dependent entity
PHONE_CONTACT.
As shown in Figure 6-14(b), representing the PHONE_CONTACT entity is straightforward.
Just replace it with a table and replace each of its attributes with a column. In this example, the
CompanyName attribute is both a part of the primary key and a foreign key.
Like all ID-dependent tables, PHONE_CONTACT must have a parent row in COMPANY.
However, a COMPANY row may or may not have a required PHONE_CONTACT, depending on
application requirements.
By The Way
As you can see from these examples, it is not much work to transform an
ID-dependent entity into a table. All that is necessary is to transform the
entity into a table and copy the attributes into columns.
Why is it so simple? There are two reasons. First, all identifying relationships are
1:N. If they were 1:1, there would be no need for the ID-dependent relationship. The
attributes of the child entity could just be placed in the parent entity. Second, given
that the relationship is 1:N, the design principle is to place the key of the parent into the
child. However, the definition of an ID-dependent relationship is that part of its identi-
fier is an identifier of its parent. Thus, by definition, the key of the parent is already in
the child. Hence, it is not necessary to create a foreign key; that work has already been
done during data modeling.
archetype/Instance Pattern
As illustrated in Figure 6-15, the fourth use for ID-dependent entities and identifying rela-
tionships is the archetype/instance pattern (also referred to as the
version/instance
pattern).
Figure 6-15(a), which is a copy of Figure 5-30, shows the CLASS/SECTION archetype/instance
example from Chapter 5, and Figure 6-15(b) shows the relational design.
As noted in the previous chapter, however, sometimes the instances of an archetype/
instance pattern are given identifiers of their own. In that case, the instance entity becomes a
weak, but not ID-dependent, entity. When this occurs, the relationship must be transformed
using the rules of a 1:N relationship between a strong entity and a weak but non-ID-dependent
entity. However, this transformation is the same as a 1:N relationship between two strong
entities. This just means that the primary key of the parent table should be placed in the child
Search WWH ::
Custom Search