Chapter 4: The Enhanced ER Model and Business Rules

Modern Database Management 8th Edition Jeffrey A. Hoffer, Mary B. Prescott, Fred R. McFadden Chapter 4:The Enhanced ER Model and Business Rules By: Aatif Kamal Dated: March 2008

Objectives • Definition of terms • Use of supertype/subtype relationships • Use of generalization and specialization techniques • Specification of completeness and disjointness constraints • Develop supertype/subtype hierarchies for realistic business situations • Develop entity clusters • Explain universal data model • Name categories of business rules • Define operational constraints graphically and in English

Enhanced-ER (EER) Model Concepts • Includes all modeling concepts of basic ER • Additional concepts: subclasses/superclasses, specialization/generalization, categories, attribute inheritance • The resulting model is called the enhanced-ER or Extended ER (E2R or EER) model • It is used to model applications more completely and accurately if needed • It includes some object-oriented concepts, such as inheritance

Supertypes and Subtypes • Subtype:A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings • Supertype:A generic entity type that has a relationship with one or more subtypes • Attribute Inheritance: • Subtype entities inherit values of all attributes of the supertype • An instance of a subtype is also an instance of the supertype

Subclasses and Superclasses (1) • An entity type may have additional meaningful sub-groupings of its entities • Example: • EMPLOYEE may be further grouped into SECRETARY, ENGINEER, MANAGER, TECHNICIAN, SALARIED_EMPLOYEE, HOURLY_EMPLOYEE,… • Each of these groupings is a subset of EMPLOYEE entities • Each is called a subclass of EMPLOYEE • EMPLOYEE is the superclass for each of these subclasses • These are called superclass/subclass relationships. • Example: EMPLOYEE/SECRETARY, EMPLOYEE/TECHNICIAN

Instances of a specialization.

Subclasses and Superclasses (2) • These are also called IS-A relationships(SECRETARY IS-A EMPLOYEE, TECHNICIAN IS-A EMPLOYEE, …). • Note: An entity that is member of a subclass represents the same real-world entity as some member of the superclass • The Subclass member is the same entity in a distinct specific role • An entity cannot exist in the database merely by being a member of a subclass; it must also be a member of the superclass • A member of the superclass can be optionally included as a member of any number of its subclasses • Example: A salaried employee who is also an engineer belongs to the two subclasses ENGINEER and SALARIED_EMPLOYEE • It is not necessary that every entity in a superclass be a member of some subclass

Attribute Inheritance in Superclass / Subclass Relationships • An entity that is member of a subclass inherits all attributes of the entity as a member of the superclass • It also inherits all relationships

Figure 4-1 Basic notation for supertype/subtype notation a) EER notation

Figure 4-1 Basic notation for supertype/subtype notation (cont.) b) Microsoft Visio Notation Different modeling tools may have different notation for the same modeling constructs

Figure 4-2 Employee supertype with three subtypes All employee subtypes will have emp nbr, name, address, and date-hired Each employee subtype will also have its own attributes

Relationships and Subtypes • Relationships at the supertype level indicate that all subtypes will participate in the relationship • The instances of a subtype may participate in a relationship unique to that subtype. In this situation, the relationship is shown at the subtype level

Figure 4-3 Supertype/subtype relationships in a hospital Both outpatients and resident patients are cared for by a responsible physician Only resident patients are assigned to a bed

Generalization and Specialization • Generalization: The process of defining a more general entity type from a set of more specialized entity types. BOTTOM-UP • Specialization: The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships. TOP-DOWN

Figure 4-4 Example of generalization a) Three entity types: CAR, TRUCK, and MOTORCYCLE All these types of vehicles have common attributes

Figure 4-4 Example of generalization (cont.) b) Generalization to VEHICLE supertype So we put the shared attributes in a supertype Note: no subtype for motorcycle, since it has no unique attributes

Generalization. • Two entity types, CAR and TRUCK. • Generalizing CAR and TRUCK into the superclass VEHICLE.

Specialization • Is the process of defining a set of subclasses of a superclass • The set of subclasses is based upon some distinguishing characteristics of the entities in the superclass • Example: {SECRETARY, ENGINEER, TECHNICIAN} is a specialization of EMPLOYEE based upon job type. • May have several specializations of the same superclass • Example: Another specialization of EMPLOYEE based in method of pay is {SALARIED_EMPLOYEE, HOURLY_EMPLOYEE}. • Superclass/subclass relationships and specialization can be diagrammatically represented in EER diagrams • Attributes of a subclass are called specific attributes. For example, TypingSpeed of SECRETARY • The subclass can participate in specific relationship types. For example, BELONGS_TO of HOURLY_EMPLOYEE

Example of a Specialization

Figure 4-5 Example of specialization Only applies to manufactured parts Applies only to purchased parts a) Entity type PART

Note: multivalued attribute was replaced by an associative entity relationship to another entity Figure 4-5 Example of specialization (cont.) b) Specialization to MANUFACTURED PART and PURCHASED PART Created 2 subtypes

Work For Employee Department d Secretary Engineer Technician Belongs To Professional Organization Notation for Specialization

Constraints on Specialization & Generalization • Several specializations can be defined on an entity type. • Entities may belong to subclasses in each of the specializations. • The specialization may also consist of a single subclass, such as the manager specialization, in this case we don’t use the circle notation. • Types of Specializations • Predicate-defined or Condition-defined specialization • Attribute-defined specialization • User-defined specialization

Types of Specializations: Predicate-defined • If we can determine exactly those entities that will become members of each subclass by a condition, the subclasses are calledpredicate-defined (or condition-defined) subclasses • Example: Income > 0 • The condition is called the defining predicate of the sub class. • The condition is a constraint specifying exactly those entities of the Employee entity type whose attribute value for Job Type is Secretary belong to the subclass. • Predicate defined subclasses are displayed by writing the predicate condition next to the line that connects the subclass to the specialization circle

Types of Specializations: Attribute-defined • If all subclasses in a specialization have membership condition on same attribute of the superclass, specialization is called an attribute defined-specialization • Attribute is called the defining attribute of the specialization • Example: JobType is the defining attribute of the specialization {SECRETARY, TECHNICIAN, ENGINEER} of EMPLOYEE • Attribute-defined specializations are displayed by placing the defining attribute name next to the arc from the circle to the super class.

EER diagram notation for an attribute-defined specialization on JobType.

Types of Specializations: User-defined • If no condition determines membership, the subclass is called user-defined • Membership in a subclass is determined by the database users by applying an operation to add an entity to the subclass • Membership in the subclass is specified individually for each entity in the superclass by the user

Constraints on Specialization and Generalization • Two other conditions apply to a specialization/generalization: • Disjointness Constraint: • Specifies that the subclasses of the specialization must bedisjointed (an entity can be a member of at most one of the subclasses of the specialization) • Specified by ‘d’ in EER diagram • If not disjointed, overlap; that is the same entity may be a member of more than one subclass of the specialization • Specified by ‘o’ in EER diagram

EER diagram notation for an overlapping (nondisjoint) specialization. O

Constraints on Specialization and Generalization • Completeness Constraint: • Total specifies that every entity in the superclassmust be a member of some subclass in the specialization/ generalization • Shown in EER diagrams by a double line • Partial allows an entity not to (may or may not) belong to any of the subclasses • Shown in EER diagrams by a single line

Constraints on Specialization and Generalization • Hence, we have four types of specialization / generalization constraints: • Disjoint, total • Disjoint, partial • Overlapping, total • Overlapping, partial • Note: Generalization usually is total because the superclass is derived from the subclasses.Disjointness vs. Completeness • Disjoint constraints and completeness constraints are independent. • The following possible constraints on specializations are possible:

Employee Department d d Academic Administrative Secretary Analyst Engineer Disjointness vs. Completeness • Disjoint, total • Disjoint, partial

Movie o Children Comedy Drama Part o Manufactured Purchased Disjointness vs. Completeness • Overlapping, total • Overlapping, partial

Constraints in Supertype/ Completeness Constraint (ONCE AGAIN) • Completeness Constraints: Whether an instance of a supertypemust also be a member of at least one subtype • Total Specialization Rule: Yes (double line) • Partial Specialization Rule: No (single line)

Figure 4-6 Examples of completeness constraints A patient must be either an outpatient or a resident patient a) Total specialization rule

A vehicle could be a car, a truck, or neither Figure 4-6 Examples of completeness constraints (cont.) b) Partial specialization rule

Constraints in Supertype/ Disjointnessconstraint (ONCE AGAIN) • Disjointness Constraints: Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes • Disjoint Rule: An instance of the supertype can be only ONE of the subtypes • Overlap Rule: An instance of the supertype could be more than one of the subtypes

Figure 4-7 Examples of disjointness constraints A patient can either be outpatient or resident, but not both a) Disjoint rule

A part may be both purchased and manufactured Figure 4-7 Examples of disjointness constraints (cont.) b) Overlap rule

Insertion & Deletion rules • Deleting an entity from a superclass implies that it is automatically deleted from all the subclasses to which it belongs. • Inserting an entity in a superclass means that the entity is mandatorily inserted in all predicate-defined (attribute defined) subclasses for which the entity satisfies the defining predicate. • Inserting an entity in a superclass of total specialization implies that the entity is mandatorily inserted in at least one of the subclasses of the specialization. • Number of other rules can be inferred for insertions & deletion can be derived from various types of specializations.

Example of __________ Specialization Disjoint Partial

Specialization / Generalization Hierarchies, Lattices and Shared Subclasses (01) • A subclass may itself have further subclasses specified on it • Forms a hierarchy or a lattice • Hierarchy has a constraint that every subclass has only one superclass (called single inheritance) • Lattice, a subclass can be subclass of more than one superclass (called multiple inheritance) • In a lattice or hierarchy, a subclass inherits attributes not only of its direct superclass, but also of all its predecessor superclasses • A subclass with more than one superclass is called a shared subclass

Specialization / Generalization Hierarchies, Lattices and Shared Subclasses (02) • Can have specialization hierarchies or lattices, or generalization hierarchies or lattices • In specialization, start with an entity type and then define subclasses of the entity type by successive specialization (top down conceptual refinement process) • In generalization, start with many entity types and generalize those that have common properties (bottom up conceptual synthesis process) • In practice, the combination of two processes is employed

Employee d d Secretary Technician Engineer Manager Salaried_Emp Hourly Emp Engineering Manager Specialization/Generalization Lattices and Hierarchies (Example) • In a lattice, when a superclass inherits attributes from more than one superclass, and some attributes are inherited more than once via different paths (i.e. Engineer, Manager and Salaried Employee all inherit from Employee, that are then inherited by Engineering Manager. • In this situation, the attributes are included only once in the subclass

Figure 4-8 Introducing a subtype discriminator (disjoint rule) A simple attribute with different possible values indicating the subtype attribute defined-specialization

A composite attribute with sub-attributes indicating “yes” or “no” to determine whether it is of each subtype Figure 4-9 Subtype discriminator (overlap rule) Predicate-defined

Figure 4-10 Example of supertype/subtype hierarchy

Specialization / Generalization Lattice Example (UNIVERSITY)

Categories (UNION TYPES) • All of the superclass/subclass relationships we have seen thus far have a single superclass • A shared subclass is subclass in more than one distinct superclass/subclass relationships, where each relationships has a single superclass (multiple inheritance) • In some cases, need to model a single superclass/subclass relationship with more than one superclass • Superclasses represent different entity types • Such a subclass is called a category or UNION TYPE

Example of categories (UNION TYPES) • Database for vehicle registration, vehicle owner can be a person, a bank (holding a lien on a vehicle) or a company. • Category (subclass) OWNER is a subset of the union of the three superclassesCOMPANY, BANK, and PERSON • A category member must exist in at least one of its superclasses • Note: The difference from shared subclass, which is subset of the intersection of its superclasses (shared subclass member must exist in all of its superclasses).

Chapter 4: The Enhanced ER Model and Business Rules