ELEMENTS USED IN LOGICAL DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 43

F IGURE 2.7

Simple subtype cluster

own entities, because we offer cable broadband to residential and com-
mercial customers, and we offer DSL only to residential customers. Both
cable and DSL could be stand-alone entities, but we wouldn't be seeing
the entire relationship. There are attributes in the BroadBand entity that
we don't track in each of the child entities, and attributes in the child en-
tities that we don't track in the BroadBand entity. And we need to leave the
design open to add more broadband types in the future without having to
alter existing records.
To solve this problem, we designate BroadBand as a supertype, and the
Cable and DSL entities as subtypes. To do this, first we create the child en-
tities with their specific attributes, without a primary key. Then we create
a required identifying relationship between the parent entity and each
child entity; this relationship designates that the primary key from
BroadBand be the primary key for each child. Finally, we choose a dis-
criminator, which is an attribute in the parent entity whose value deter-
mines which subtype a given record belongs to; the discriminator can be a
key or non-key attribute. In this case, our discriminator is Type, which con-
tains a string value of either “DSL” or “Cable.”
If a subtype cluster contains all possible subtypes for the supertype for
which they are defined, the subtype cluster is said to be complete.
Alternatively, if it includes only some of the possible subtypes, the cluster
is incomplete. The designation is mostly a documentation concern, but as