Lecture 10 Structuring Data Requirements (Part 2) (EERD).pdf

Systems Analysis and Design LECTURE 10 STRUCTURING DATA REQUIREMENTS (PART 2) EXTENDED/ENHANCED ENTITY RELATIONSHIP (EER) MODELING  2 Source 1: Rob and Coronel, Database Systems: Design, Implementation and...

Systems Analysis and Design LECTURE 10 STRUCTURING DATA REQUIREMENTS (PART 2) EXTENDED/ENHANCED ENTITY RELATIONSHIP (EER) MODELING  2 Source 1: Rob and Coronel, Database Systems: Design, Implementation and Management (Crow’s Foot notation) EXTENDED/ENHANCED ENTITY RELATIONSHIP (EER) MODELING 3 ❑ Result of adding more semantic constructs to the original entity relationship (ER) model ❑ Diagram using this model is called an Extended Entity Relationship Diagram (EERD) ❑ Sometimes referred to as the Enhanced Entity Relationship Diagram (EERD)  ENTITY SUPERTYPES AND SUBTYPES 4 ❑ Entity supertype: o Generic entity type related to one or more entity subtypes o Contains common characteristics ❑ Entity subtype: o Contains unique characteristics of each entity subtype ENTITY SUPERTYPES AND SUBTYPES (CONT..) 5  SPECIALIZATION HIERARCHY 6 ❑ Depicts arrangement of higher-level entity supertypes (parent entities) and lower-level entity subtypes (child entities) ❑ Relationships sometimes described in terms of “IS-A” relationships ❑ Subtype can exist only within context of supertype ❑ Every subtype can have only one supertype to which it is directly related ❑ Supertype can have many subtypes ❑ Can have many levels of supertype/subtype relationships  SPECIALIZATION HIERARCHY (CONT..) 7 ❑ Provides the means to: o Support attribute inheritance o Define a special supertype attribute known as the subtype discriminator o Define disjoint/overlapping constraints and complete/partial constraints  SPECIALIZATION HIERARCHY (CONT..) 8 Figure 6-2 Specialization hiearchy example (Crow’s Foot notation)  INHERITANCE 9 ❑ Enables an entity subtype to inherit attributes and relationships of the supertype ❑ All entity subtypes inherit their primary key attribute from their supertype ❑ At the implementation level, supertype and its subtype(s) depicted in specialization hierarchy maintain a 1:1 relationship ❑ Entity subtypes inherit all relationships in which supertype entity participates ❑ Lower-level subtypes inherit all attributes and relationships from its upper- level supertypes INHERITANCE (CONT..) 10  SUBTYPE DISCRIMINATOR 11 Subtype discriminator: ❑ The attribute in supertype entity that determines to which entity subtype each supertype occurrence is related ❑ The default comparison condition for subtype discriminator attribute is equality comparison  DISJOINT AND OVERLAPPING CONSTRAINTS 12 ❑ Disjoint subtypes: o Subtypes that contain a unique subset of the supertype entity set o Also known as non-overlapping subtypes o Implementation is based on the value of the subtype discriminator attribute in the supertype ❑ Overlapping subtypes: o Subtypes that contain non-unique subsets of the supertype entity set o Implementation requires the use of one discriminator attribute for each subtype DISJOINT AND OVERLAPPING CONSTRAINTS (CONT..) 13 Figure 6-4 Specialization hiearchy example (Crow’s Foot notation) DISJOINT AND OVERLAPPING CONSTRAINTS (CONT..) 14 Table 6-1 Discriminator attributes with overlapping subtypes  COMPLETENESS CONSTRAINTS 15 ❑ Specifies whether each entity supertype occurrence must also be a member of at least one subtype ❑ Types: o Partial completeness: Not every supertype occurrence is a member of a subtype o Total completeness: Every supertype occurrence must be a member of any COMPLETENESS CONSTRAINTS (CONT..) 16 Table 6-2 Specialization hierarchy constraint scenario  17 Source 2: Kenneth E. Kendall and Julie E. Kendall, System Analysis and Design, Pearson (Chen notation)  REPRESENTING SUPERTYPES AND SUBTYPES 18  Often two or more entity types seem very similar (maybe almost the same name) but there are few differences. These entity types share common attributes but also have one or more distinct attributes or relationships. To address this, E-R model use subtype and supertype.  Subtype: a subgrouping of the entities in an entity type  Is meaningful to the organization  Shares common attributes or relationships distinct from other subgroupings  Supertype: a generic entity type that has a relationship with one or more subtypes.  See Figure 8-18 for example  REPRESENTING SUPERTYPES AND SUBTYPES (CONT..) 19 Business Rules for Supertype/subtype Relationships: 1) Total specialization specifies that each entity instance of the supertype must be a member of some subtype in the relationship. 2) Partial specialization specifies that an entity instance of the supertype does not have to belong to any subtype, and may or may not be an instance of one of the subtypes. 3) Disjoint rule specifies that if an entity instance of the supertype is a member of one subtype, it cannot simultaneously be a member of any other subtype. 4) Overlap rule specifies that an entity instance can simultaneously be a member of two (or more) subtypes. REPRESENTING SUPERTYPES AND SUBTYPES (CONT..) 20 Figure 8-18 Supertype / subtype relationship in a hospital (Chen Notation) REPRESENTING SUPERTYPES AND SUBTYPES (CONT..) 21 Figure 8-19 Example of supertype/subtype hierarchy (Chen notation) 22