MIS 385/MBA 664 Systems Implementation with DBMS/ Database ManagementEvolution of the E-R ModelSupertype/Subtype RelationshipsSupertype/Subtype NotationSlide 5Two Rules for When to Use Supertype/SubtypesAttribute InheritanceSupertype/Subtype ExampleSupertype/Subtype Relationships in a HospitalSlide 10Two Processes to Develop Supertype/SubtypesSupertype/Subtype ConstraintsCompleteness Constraint: Two Possible RulesCompleteness constraint – total specializationCompleteness constraint – partial specializationDisjointed constraint – member of only one subtype at a timeDisjointed constraint – member of more than one subtype at a timeSubtype discriminatorsSubtype discriminator (disjoint)Slide 20Supertype/subtype hierarchyAnother example….MIS 385/MBA 664Systems Implementation with DBMS/Database ManagementDave [email protected] (email)http://www.davesalisbury.com/ (web site)Evolution of the E-R ModelBasic E-R Modelover 25 years oldcomplex data relationships and new database technology have outgrown it in some respectsEnhanced E-R Modela response to the shortcomings of the basic E-R modelnot universally agreed upon in some respectsintroduced the supertype/subtype relationshipSupertype/Subtype RelationshipsSupertype (example: Employee)a generic entity that has a relationship with one or more subtypesSubtype (example: Manager)a subgrouping of a supertype entity that is meaningful to an organizationshares all attributes of its supertype, but also has unique attributes of its own and/or :has relationships with other entities distinct from those of other subtypesSupertype/Subtype NotationSupertype/Subtype NotationTwo Rules for When to Use Supertype/SubtypesUse this type of relationship when either (or both) of the following are present:When there are attributes that apply to some (but not all) of the instances of an entity typeWhen the instances of a subtype participate in a relationship unique to that subtypeAttribute InheritanceThe property by which subtype entities inherit values of all attributes of the supertype.This important property makes it unnecessary to include supertype attributes redundantly with the subtypes.Supertype/Subtype ExampleSupertype/Subtype Relationships in a HospitalSupertype/Subtype Relationships in a HospitalTwo Processes to Develop Supertype/SubtypesGeneralizationThe process of defining a more general entity type from a set of more specialized entity typesA “bottom-up” approachSpecializationThe process of defining one or more subtypes of a general entity based on distinguishing attri-butes or relationshipsA “top-down” approachBoth approaches can be used togetherSupertype/Subtype Constraints CompletenessAddresses the question of whether an instance of a supertype must also be a member of at least one subtype DisjointnessAddresses the question of whether an instance of a supertype may simultaneously be a member of two (or more) subtypesCompleteness Constraint: Two Possible RulesTotal Specialization Rule (Double-Line)Specifies that each entity instance of the supertype must be a member of some subtype in the relationship (Example: all STUDENTS are either UNDERGRADUATE or GRADUATE students)Partial Specialization Rule (Single-line)Specifies that an entity instance of the supertype is allowed to not belong to any subtype (Example: FACULTY and STAFF are not the only possible members of the entity EMPLOYEE)Completeness constraint – total specializationA patient must be either an outpatient or a resident patientCompleteness constraint – partial specializationA vehicle could be a car, a truck, or neitherDisjointed constraint – member of only one subtype at a timeA patient can either be outpatient or resident, but not bothDisjointed constraint – member of more than one subtype at a timeA part may be both purchased and manufacturedSubtype discriminatorsAttribute of the supertype whose value determines to which subtype an instance belongsA simple attribute with different possible values indicating the subtypeSubtype discriminator (disjoint)A composite attribute with sub-attributes indicating “yes” or “no” to determine whether it is of each subtypeSubtype discriminator (disjoint)Supertype/subtype hierarchyAnother