What rule specifies that each entity instance of the super type must be a member of some subtype of the relationship?

Enhanced Entity Relationship (EER) Model: The model resulting from extending the original E-R model with new modeling constructs.

The EER model is used to capture important business rules such as constraints in supertype/subtype relationships. Most organization used various rules to guide behavior--but many of these rules cannot be expressed with basic E-R diagrams...

Representing Subtypes and Supertypes

Subtype: A subgroup of the entities in an entity type that is meaningful to the organization and that shares common attributes or relationships distinct from other subgroupings.

Supertype: A generic entity type that has a relationship with one or more subtypes.

Attribute Inheritance: A property by which subtype entities inherit values of all attributes of the supertype. [A subtype is an entity type in its own right; an entity instance of a subtype represents the same entity instance of the supertype.]

When to Use Supertype/Subtype Relationships

We should consider using subtypes when either (or both) of the following conditions are present:

1. There are attributes that apply to some (but not all) of the instances of an entity type;
2. The instance of a subtype participate in a relationship unique to that subtype.

Representing Specialization and Generalization

Generalization: The process of defining a more general entity types from a set of more specialized entity types. For example, we could generalize three entity types CAR, TRUCK and MOTORCYCLE as VEHICLE. This is a bottom-up process.

Specialization: The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships. Each subtype is formed based on some distinguishing characteristic such as attributes or relationships specific to the subtype. For example, we could specialize the entity type PART into MANUFACTURED PART and PURCHASED PART. This is a top-down process.

Specifying Constraints in Supertype/Subtype Relationships

Specifying Completeness Constraints

Completeness Constraint: A type of constraint that addresses the question whether an instance of a supertype must also be a member of at least one subtype. The completeness constraint has two possible rules: total specialization and partial specialization.

Total Specialization Rule: Specifies that each entity instance of the supertype must be a member of some subtype in the relationship. Total specialization is indicated by double lines extending from the supertype to circle from which single lines lead to the subtypes.

Partial Specialization Rule: Specifies that an entity instance of the supertype is allowed not to belong to any subtype.

Specifying Disjointness Constraints

Disjointness Constraint: A constraint that addresses the question whether an instance of a supertype may simultaneously be a member of two (or more) subtypes. The disjointness constraint has two possible rules: the disjoint rule and the overlap rule.

Disjoint Rule: Specifies that if an entity instance (of a supertype) is a member of one subtype, it cannot simultaneously be a member of any other subtype.

Overlap Rule: Specified that an entity instance can simultaneously be a member of two (or more) subtypes.

Defining Subtype Discriminators

Subtype Discriminator: An attribute of the supertype whose values determine the target subtype or subtypes. For an EMPLOYEE entity type for example, we could use a subtype discriminator (an attribute of EMPLOYEE) called Employee_Type which is coded with one of three values H (for Hourly), S (for Salaried), and C (for consultant).

When we have overlapping subtypes, a given instance of the supertype may require that we create an instance in more than one subtype. For instance, if a consider a PART entity th.at can either be manufactured or purchased, the value of the Part_Type attribute would be a composite attribute with manufactured? and purchased?...

Defining Supertype/Subtype Hierarchies

Supertype/Subtype Hierarchy: A hierarchical arrangement of supertypes and subtypes, where each subtype has only one supertype.

Summary of Supertype/Subtype Hierarchies

1. Attributes should be assigned at the highest logical level that is possible in the hierarchy; this assures that they can be shared by as many subtypes as possible
2. Subtypes that are lower in the hierarchy inherit the attributes not only from their immediate supertypes, but from all supertypes higher in the hierarchy (along the same line), up to the root.

Entity Clustering

Entity Cluster: A set of one or more entity types and associated relationships grouped into a single abstract entity type.

An entity cluster is a useful way to present a data model for a large and complex organization. Because an entity cluster behaves like an entity type, entity clusters and entity types can be further grouped to form a higher-level entity cluster. Entity clustering is a hierarchical decomposition of a macro-level view of the data model into finer and finer views, eventually resulting in the full, detailed data model.

Entity clusters could be formed by:

• abstracting a supertype and its subtypes
• combining directly related entities and their relationships
• combining a strong entity and its associated weak entities types

Business Rules Revisited

We have seen that the participation and disjointness rules constraints associated with supertypes and subtypes are an expression of business rules associated with those relationships. There are however other types of business rules in an organization that cannot be expressed with those notations... We now explore a general framework for business rules ... to express some rules that elude the E-R and EER notations.

Classification of Business Rules

We have seen various types of business rules (cardinality values, supertype/subtype relationships, facts about attributes and entity types viz: definitions, ...)

There are three main types of business rules:

Derivation: A statement derived from other knowledge in the business; commonly a mathematical or logical inference involving literals and facts.

Structural Assertion: A statement that expresses some aspect of the static structure of the organization. E-R diagrams are a common way to show structural assertions. A structural assertion is stated as either a term or a fact.

Action Assertion: A statement of a constraint or control on the actions of the organization. An action assertion is the property of some business rule and state under what conditions a particular action can be performed on which business rules (e.g. under what conditions a new customer record can be created).

Another type of action assertion (called a Domain Constraint) states what values a particular attribute may hold

Stating a Structural Assertion

A structural assertion says that something of importance to the organization either exists or exists in relationship with other things of interest. A structural assertion can be as simple as the definition of a term, or a fact, which is a statement of a relationship between terms.

Derived Fact: A fact that is derived from business rules using an algorithm or inference--in contrast to base facts, which are fundamental and cannot be derived from other terms or facts.

Stating an Action Assertion

Whereas a structural assertion deals with the static structure of an organization, an action assertion deals with the dynamic aspects of the organization. Action assertion impose constraints on handling data.

An action assertion is the property of some business rule (called the anchor object). For a data handling action (e.g. create, update, delete, or read) it states how other business rules (called the corresponding objects) act on the anchor object.

Anchor Object: A business rule (a fact) on which actions are limited.

Action: An operation, such as create, update, delete, or read, which may be performed on data objects.

Corresponding Object: A business rule (fact) that influences the ability to perform an action on another business rule.

Examples:

• A course (anchor object) must have a course name (corresponding object) to be added (action)
• A student (anchor object) must have a value of 2.0 or greater for Student_GPA (corresponding object) to graduate (action)

Types of Action Assertions

There are three ways to classify action assertions:

Action assertions can be classified based on the type of result from the assertion

1. Condition Action Assertion--which states that if something is true, then another business rule will apply. ("If a course has a qualified faculty, then students can register for a section of that course.")

2. Integrity Constraint Action Assertion--which states something that must always be true. ("The date a faculty becomes qualified to teach a course cannot be after the semester in which the faculty is assigned to teach a section of that course.")

3. Authorization Action Assertion--which states a privilege. ("Only departmental chairs can qualify a faculty to teach a course.")

Action assertions can be classified based on the form of the assertion

1. Enabler Action Assertion--which, if true, permits or leads to the existence of the corresponding object. ("A faculty can be created once the faculty is qualified to teach at least one course.")

2. Timer Action Assertion--which enables (or disables) or creates (or deletes) an action. ("When a student has a GPA above 2.0 and a total credit hours above 125, then the student may graduate.") Note that the action of graduating does not occur because of the timer assertion, but rather the timer enables the action to occur.

3. Executive Action Assertion--which causes the execution of one or more actions. An executive action assertion can be thought of as a trigger for some action.("When a student has a GPA below 2.0, then the student goes on academic probation.") This executive action might result in a status attribute of the student to be updated to the value of "probation."

Action assertions can be classified based on the rigor of the assertion

1. Controlling Action Assertion--which state that something must or must not happen. All our previous examples fall under this category.

2. Influencing Action Assertion--which are guidelines or items of interest for which a notification must occur. ("When the number of students registered for a section exceeds 90% of the capacity of that section, notify the responsible department chair.") Observe that in this situation, nothing is controlled--students may continue to register for the section and no additional section is created. However, management wants to know that a particular condition has occurred.

Representing and Enforcing Business Rules

Action assertions have traditionally been implemented in procedural logic buried deep within individual application programs--in a form that is virtually unrecognizable, unmanageable, and inconsistent. This approach, evidently, places a heavy burden on the programmer.

The more modern approach is to declare action assertions at a conceptual level without specifying how the rule will be implemented. Hence, there needs to be a specification language for business rules as the E-R and ERR models are limited in their ability to represent complex business rules. An alternative with be a structured grammar model--but whichever the approach chose, two desirable features for a business rule specification language would be:

1. It should be relatively simple so that end users not only can understand the rule statements, but also can define the rules themselves.
2. The language should be sufficiently structured to be automatically convertible to the computer code that enforces the specifications.

Enter SQL.

Identifying and Testing Business Rules

The task of the database analyst is to identify business rules, document the specifications of those rules, develop strategies for enforcing those rules as much as through database technologies, and then testing them before implementing them in technology.

An approach to identifying and testing business rules is scenarios.

A scenario is a story or script that describes how a business reacts to a given situation, and is similar to the concept of a use case, which is an important tool in object modeling.

If a set of business rules have not already been identified, a scenario could be used to identify terms, facts, and action assertions to be included in the data model. If a set of business rules already exist, then statements in scenarios can be used to validate that the business rules handle all possible circumstances.

What rule specifies that an entity instance of a supertype can be a member of more than one subtype at a time?

Which of the following specifies that each entity instance of the super type must be a member of some sub type in the relationship?

What type of constraints in Supertype that an instance of a supertype must also be a member of at least one subtype?

Is a generic entity type that has a relationship with one or more subtypes?