ENHANCED E-R MODEL
and BUSINESS RULES
HIERARCHY -- Subtypes and Supertypes.
E-R Model incorporates extensions to Chen's original E-R model. There
have been literally dozens of proposed enhancements. We cover the
most significant ones in these notes.
This is the concept of categorizing
or generalizing between supertypes
and subtypes of entities.
As an example, a CAR
entity can have several subtypes, e.g. CONVERTIBLE,
The basic notation used in McFadden,
Hoffer and Prescott is shown in Figure 1.
entity is connected to the Subtype
entities through lines and a circle. The U-symbol
indicates the Subtype is a subset of the Supertype.
All of attributes of the Supertype
are also common to the Subtype.
Each Subtype entity has attributes
that are unique to that Subtype - that
is what makes the entity a subtype.
All entities share a common
Consider The EMPLOYEE
supertype entity shown in Figure 2.
This entity can have several
different subtype entities (HOURLY and SALARIED), each with
distinct properties not shared by other subtypes.
The Supertype EMPLOYEE
stores all properties that subtypes have in common.
employees have the unique attribute Wage
(hourly wage rate), while SALARIED
employees have two unique attributes, StockOption
and Primary Keys for Generalization Hierarchies.
When to Use a Subtype.
inherit all of the attributes of the Supertype.
Note that an occurrence of a Subtype entity represents an occurrence of
the same Supertype entity.
key of the supertype and subtype are always identical.
cardinality between the supertype and subtype are always 1:0. This
means that an occurrence of the supertype, EMPLOYEE
need not have an occurrence that is SALARIED
nor HOURLY (the
employee might be a CONSULTANT
and have no unique attributes as a subtype), but an occurrence of
a SALARIED employee entity (or HOURLY)
MUST HAVE an occurrence of the EMPLOYEE
Use this approach if an occurrence
of a Subtype participates in a relationship.
Example: The subtype PHYSICIAN
(of the supertype EMPLOYEE) participates
in the relationship called Treats which
links to the entity named PATIENT.
Completeness Constraint addresses the issue of whether or not
an occurrence of a Supertype must also have a corresponding Subtype occurrence.
Specialization Rule specifies that this is the case. This
is diagrammed with a double line
from the Supertype to the circle as shown here.
Specialization Rule is the one we have followed thus far in
the notes (single line to the circle) -- no such constraint exists.
You can have a Supertype named VEHICLE
and a Subtype named AUTO, but an occurrence
of a vehicle need not be an automobile.
This type of business rule deals
with the situation where an occurrence of a Supertype may also have more
than one Subtype occurrence.
Rule says that this cannot occur. Example: a PATIENT
can either be an IN_PATIENT or an OUT_PATIENT,
but not the both at the same time.
Rule says that this can happen. Example: a PART
can be either a MANUFACTURED_PART or
a PURCHASED_PART. This means
that a Ford 8-cylinder Mustang engine could either be manufactured at the
factory or purchased from a vendor, but it would still be a Ford 8-cylinder
The subtypes are usually (but
may not be) mutually exclusive with no overlap.
This is diagrammed by placing
either the letter "d" or "o"
inside the circle on the Generalization Hierarchy portion of the E-R diagram.
Discriminator is an attribute of the Supertype that is used
to "code" or denote which Subtype an entity is.
Example: We might code
EMPLOYEE supertypes as either "H"
or "S" for HOURLY
or SALARIED employee subtypes.
This attribute may enhance system
performance during program coding of maintenance programs for the database
tables during the physical design phase of a project.
While your text discusses various
ways to use Subtype Discriminators for Disjoint or Overlapping subtypes,
coding of the attribute in the Supertype will handle all possibilities.
It is possible for a Subtype
entity to be a Supertype entity in another relationship.
This creates a hierarchy
of Supertypes and Subtypes.
While this doesn't occur very
often in reality, the possibility does exist and is noted here for your
Entity Integrity (Uniqueness
This topic deals with modeling
additional information that is generally not amenable to diagramming
tool used may allow storage of business rules expressed as simple logical
statements and may enforce such rules during system execution.
Rule is a constraint that defines some aspect of how a firm
Example: A student may
register for a course only if all course prerequisites have been completed.
Business rules are also called
We are only concerned with modeling
rules that affect the firm's database.
Each instance of an entity type
must have a unique identifier that
is not null.
This may also be termed Primary
Key Integrity or Uniqueness Integrity.
Integrity rules that exist between
occurrences of entities where relationships exist, for example, existence
dependency is a type of referential integrity.
Example: An occurrence
of a CUSTOMER entity may or may not
have an associated occurrence of an CUSTOMER_ORDER
entity; but each occurrence of an CUSTOMER_ORDER
entity must have one and only one associated occurrence of a CUSTOMER
is a defined list or range of valid values for a specific attribute or
set of associated attributes.
This constraint on valid values
for attributes is termed Domain Integrity.
Examples include data type,
length, format, range, allowable values, meaning, uniqueness, and null/not-null.
CREATE ASSERTION Faculty_Load
These are constraints or rules
that govern ongoing operations of the firm.
Example: A faculty member
may only be assigned to teach no more than 12 credit hours per semester.
These rules can be implemented
in the SQL language through the CREATE ASSERTION command. An example
is given here and is covered in more detail in the portion of the course
focusing on SQL
CHECK (SELECT Count(*)
Faculty_ID = 'BOCK') <= 12;
Operations is a broad class of other business rules that protect
the validity of attribute values. These are a type of Operational
These usually apply to the insertion,
deletion, and update operations operations that manipulate data.
For example, it may be desirable
to have the database automatically delete (termed a cascade delete) all
active CUSTOMER_ORDER occurrences whenever
the associated CUSTOMER entity is deleted.
Triggering Operations are covered
in detail later in the SQL portion of the course.
END OF NOTES.