Introduction.

• The Enhanced 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, COMPACT, SEDAN, etc.
• The basic notation used in McFadden, Hoffer and Prescott is shown in Figure 1.
• The Supertype 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 identifier attribute.

• 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.
• HOURLY employees have the unique attribute Wage (hourly wage rate), while SALARIED employees have two unique attributes, StockOption and Salary.

Attribute Inheritance and Primary Keys for Generalization Hierarchies.

• Subtypes inherit all of the attributes of the Supertype.  Note that an occurrence of a Subtype entity represents an occurrence of the same Supertype entity.
• The primary key of the supertype and subtype are always identical.
• The Maximum:Minimum 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 supertype entity.

• 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.

• The  Completeness Constraint addresses the issue of whether or not an occurrence of a Supertype must also have a corresponding Subtype occurrence.
• The Total Specialization Rule specifies that this is the case.  This is diagrammed with a double line from the Supertype to the circle as shown here.

• The Partial 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.
• The Disjoint 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.
• The Overlap 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 Mustang engine!
• 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.

• A Subtype 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 consideration.

• This topic deals with modeling additional information that is generally not amenable to diagramming techniques.
• The CASE tool used may allow storage of business rules expressed as simple logical statements and may enforce such rules during system execution.
• A Business Rule is a constraint that defines some aspect of how a firm does business.
• Example:  A student may register for a course only if all course prerequisites have been completed.
• Business rules are also called Integrity Constraints.
• 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 entity.

• A Domain 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.

• 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

• Triggering Operations is a broad class of other business rules that protect the validity of attribute values.  These are a type of Operational Constraint.
• 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.