Data Modeling with the Entity-Relationship Model - Database Processing: Fundamentals, Design, and Implementation - page 192

STUDENT
StudentID
LastName
FirstName
isGradStudent

STUDENT
StudentID
LastName
FirstName

isGradStudent

UNDERGRADUATE
StudentID
HighSchoolGPA
ScoreOnSAT

HIKING_CLUB
StudentID
DateDuesPaid
AmountPaid

GRADUATE
StudentID
UndergraduateGPA
ScoreOnGMAT

SAILING_CLUB
StudentID
DateDuesPaid
AmountPaid

Figure 5-13
Examples of Subtype
Entities

(a) Exclusive Subtypes with Discriminator (b) Inclusive Subtypes

Alternatively, a student could be classified as a freshman, sophomore, junior, or senior. In
that case, STUDENT is the supertype, and FRESHMAN, SOPHOMORE, JUNIOR, and SENIOR
are the subtypes.
As illustrated in Figure 5-13, in our E-R models we use a circle with a line under it as a
subtype symbol to indicate a supertype-subtype relationship. Think of this as a symbol for an
optional (the circle), 1:1 (the line) relationship. In addition, we use a solid line to represent an
ID-dependent subtype entity because each subtype is ID-dependent on the supertype. Also
note that none of the line end symbols shown in Figure 5-8 are used on the connecting lines.
In some cases, an attribute of the supertype indicates which of the subtypes is appropri-
ate for a given instance. An attribute that determines which subtype is appropriate is called
a discriminator . In Figure 5-13(a), the attribute named isGradStudent (which has only the
values Yes and No) is the discriminator. In our E-R diagrams, the discriminator is shown next
to the subtype symbol, as illustrated in Figure 5-13(a). Not all supertypes have a discriminator.
Where a supertype does not have a discriminator, application code must be written to create
the appropriate subtype.
Subtypes can be exclusive or inclusive. With exclusive subtypes , a supertype instance is
related to at most one subtype. With inclusive subtypes , a supertype instance can relate to one
or more subtypes. In Figure 5-13(a), the X in the circle means that the UNDERGRADUATE and
GRADUATE subtypes are exclusive. Thus, a STUDENT can be either an UNDERGRADUATE
or a GRADUATE, but not both. Figure 5-13(b) shows that a STUDENT can join either the
HIKING_CLUB or the SAILING_CLUB or both. These subtypes are inclusive (note there is no X
in the circle). Because a supertype may relate to more than one subtype, inclusive subtypes do
not have a discriminator.
The most important (some would say the only) reason for creating subtypes in a data
model is to avoid value-inappropriate nulls. Undergraduate students take the SAT exam and
report that score, whereas graduate students take the GMAT and report their score on that
exam. Thus, the SAT score would be NULL in all STUDENT entities for graduates, and the
GMAT score would be NULL for all undergraduates. Such null values can be avoided by creat-
ing subtypes.

By ThE Way The relationships that connect supertypes and subtypes are called IS-a
relationships because a subtype is the same entity as the supertype.
Because this is so, the identifier of a supertype and all its subtypes must be the same;
they all represent different aspects of the same entity. Contrast this with HAS-A rela-
tionships, in which an entity has a relationship to another entity, but the identifiers of
the two entities are different.

The elements of the entity-relationship model and their IE Crow's Foot representation are
summarized in Figure 5-14. The identifier and attributes are shown only in the first example.