Chapter 5: Advanced Data
Modeling
Exam Study Guide Latest
Updated 2024/2025
The Best Study Notes
,Chapter 5: Advanced Data Modeling
Exam Study Guide Latest Updated
2024/2025
Discussion Focus
Your discussion can be divided into three parts to reflect the chapter coverage:
The first part of the discussion covers the Extended Entity Relationship Model.
a. Start by exploring the use of entity supertypes and subtypes.
b. Use the specialization hierarchy example in Figure 5.2 to illustrate the main constructs.
c. Illustrate the benefits of attribute inheritance and relationship inheritance.
d. Remember that an entity supertype and an entity subtype are related in a 1:1 relationship.
e. Emphasize the use of the subtype discriminator and then explain the concept of
overlapping and disjoint constraints in relation to entity subtypes.
f. The completeness constraint indicates whether all entity supertypes must have at least
one subtype.
g. Explore the specialization and generalization hierarchies.
h. Finally, explain the use of entity clusters as an alternative method to simplify crowded
data models.
The second part of the discussion covers the importance of proper primary key selection.
a. Start by clearly stating the function of a PK -- identification -- and how that function
differs from the descriptive nature of the other attributes in an entity. Explain the use of
PKs to uniquely identify each entity instance.
b. Discuss natural keys, primary keys, and surrogate keys.
c. Examine the primary key guidelines that specify the PK characteristics. PKs must be
unique, non-intelligent, they do not change over time, they are ideally composed of a
single attribute, they are numeric, and they are security compliant.
d. Finally, contrast the use of surrogate and composite primary keys. Remind students that
composite primary keys are useful in composite entities where each primary key
combination is allowed only once in the M:N relationship.
The third part of the discussion covers four special design cases:
a. Implementing 1:1 relationships.
b. Maintaining the history of time-variant data.
c. Fan traps.
d. Redundant relationships.
152
,Chapter 5: Advanced Data Modeling
Exam Study Guide Latest Updated
2024/2025
Answers to Review Questions
1. What is an entity supertype, and why is it used?
An entity supertype is a generic entity type that is related to one or more entity subtypes, where the
entity supertype contains the common characteristics and the entity subtypes contain the unique
characteristics of each entity subtype. The reason for using supertypes is to minimize the number of
nulls and to minimize the likelihood of redundant relationships.
2. What kinds of data would you store in an entity subtype?
An entity subtype is a more specific entity type that is related to an entity supertype, where the entity
supertype contains the common characteristics and the entity subtypes contain the unique
characteristics of each entity subtype. The entity subtype will store the data that is specific to the
entity; that is, attributes that are unique the subtype.
3. What is a specialization hierarchy?
A specialization hierarchy depicts the arrangement of higher-level entity supertypes (parent
entities) and lower-level entity subtypes (child entities). To answer the question precisely, we have
used the text’s Figure 5.2. (We have reproduced the figure on the next page for your convenience.)
Figure 5.2 shows the specialization hierarchy formed by an EMPLOYEE supertype and three entity
subtypes—PILOT, MECHANIC, and ACCOUNTANT.
153
, Chapter 5: Advanced Data Modeling
Exam Study Guide Latest Updated
2024/2025
(Text) FIGURE 5.2 A Specialization Hierarchy
The specialization hierarchy shown in Figure 5.2 reflects the 1:1 relationship between EMPLOYEE
and its subtypes. For example, a PILOT subtype occurrence is related to one instance of the
EMPLOYEE supertype and a MECHANIC subtype occurrence is related to one instance of the
EMPLOYEE supertype.
4. What is a subtype discriminator? Given an example of its use.
A subtype discriminator is the attribute in the supertype entity that is used to determine to which
entity subtype the supertype occurrence is related. For any given supertype occurrence, the value of
the subtype discriminator will determine which subtype the supertype occurrence is related to. For
example, an EMPLOYEE supertype may include the EMP_TYPE value “P” to indicate the
PROFESSOR subtype.
5. What is an overlapping subtype? Give an example.
Overlapping subtypes are subtypes that contain non-unique subsets of the supertype entity set; that
is, each entity instance of the supertype may appear in more than one subtype. For example, in a
154
Modeling
Exam Study Guide Latest
Updated 2024/2025
The Best Study Notes
,Chapter 5: Advanced Data Modeling
Exam Study Guide Latest Updated
2024/2025
Discussion Focus
Your discussion can be divided into three parts to reflect the chapter coverage:
The first part of the discussion covers the Extended Entity Relationship Model.
a. Start by exploring the use of entity supertypes and subtypes.
b. Use the specialization hierarchy example in Figure 5.2 to illustrate the main constructs.
c. Illustrate the benefits of attribute inheritance and relationship inheritance.
d. Remember that an entity supertype and an entity subtype are related in a 1:1 relationship.
e. Emphasize the use of the subtype discriminator and then explain the concept of
overlapping and disjoint constraints in relation to entity subtypes.
f. The completeness constraint indicates whether all entity supertypes must have at least
one subtype.
g. Explore the specialization and generalization hierarchies.
h. Finally, explain the use of entity clusters as an alternative method to simplify crowded
data models.
The second part of the discussion covers the importance of proper primary key selection.
a. Start by clearly stating the function of a PK -- identification -- and how that function
differs from the descriptive nature of the other attributes in an entity. Explain the use of
PKs to uniquely identify each entity instance.
b. Discuss natural keys, primary keys, and surrogate keys.
c. Examine the primary key guidelines that specify the PK characteristics. PKs must be
unique, non-intelligent, they do not change over time, they are ideally composed of a
single attribute, they are numeric, and they are security compliant.
d. Finally, contrast the use of surrogate and composite primary keys. Remind students that
composite primary keys are useful in composite entities where each primary key
combination is allowed only once in the M:N relationship.
The third part of the discussion covers four special design cases:
a. Implementing 1:1 relationships.
b. Maintaining the history of time-variant data.
c. Fan traps.
d. Redundant relationships.
152
,Chapter 5: Advanced Data Modeling
Exam Study Guide Latest Updated
2024/2025
Answers to Review Questions
1. What is an entity supertype, and why is it used?
An entity supertype is a generic entity type that is related to one or more entity subtypes, where the
entity supertype contains the common characteristics and the entity subtypes contain the unique
characteristics of each entity subtype. The reason for using supertypes is to minimize the number of
nulls and to minimize the likelihood of redundant relationships.
2. What kinds of data would you store in an entity subtype?
An entity subtype is a more specific entity type that is related to an entity supertype, where the entity
supertype contains the common characteristics and the entity subtypes contain the unique
characteristics of each entity subtype. The entity subtype will store the data that is specific to the
entity; that is, attributes that are unique the subtype.
3. What is a specialization hierarchy?
A specialization hierarchy depicts the arrangement of higher-level entity supertypes (parent
entities) and lower-level entity subtypes (child entities). To answer the question precisely, we have
used the text’s Figure 5.2. (We have reproduced the figure on the next page for your convenience.)
Figure 5.2 shows the specialization hierarchy formed by an EMPLOYEE supertype and three entity
subtypes—PILOT, MECHANIC, and ACCOUNTANT.
153
, Chapter 5: Advanced Data Modeling
Exam Study Guide Latest Updated
2024/2025
(Text) FIGURE 5.2 A Specialization Hierarchy
The specialization hierarchy shown in Figure 5.2 reflects the 1:1 relationship between EMPLOYEE
and its subtypes. For example, a PILOT subtype occurrence is related to one instance of the
EMPLOYEE supertype and a MECHANIC subtype occurrence is related to one instance of the
EMPLOYEE supertype.
4. What is a subtype discriminator? Given an example of its use.
A subtype discriminator is the attribute in the supertype entity that is used to determine to which
entity subtype the supertype occurrence is related. For any given supertype occurrence, the value of
the subtype discriminator will determine which subtype the supertype occurrence is related to. For
example, an EMPLOYEE supertype may include the EMP_TYPE value “P” to indicate the
PROFESSOR subtype.
5. What is an overlapping subtype? Give an example.
Overlapping subtypes are subtypes that contain non-unique subsets of the supertype entity set; that
is, each entity instance of the supertype may appear in more than one subtype. For example, in a
154
