EER DATA MODELING.pdf

Database  Design   EER  Model    ER Data Modeling ER modeling concept is sufficient for representing many database schemas for traditional data processing applications Additional semantic data modeling is required for complex databases for – CA...

Database  Design   EER  Model    ER Data Modeling ER modeling concept is sufficient for representing many database schemas for traditional data processing applications Additional semantic data modeling is required for complex databases for – CAD/CAM – Telecommunications – Complex s/w systems – Geographic Information System (GIS) EER  Model   1    EER Data Modeling EER model includes all the modeling concepts of the ER model. In addition, it includes concepts of: – Subclass and superclass – Specialization and generalization – Category or union These extension makes EER model semantically similar to OO data modeling EER  Model   2    Subclass, Superclass, Inheritance In many cases an entity type has numerous subgroupings of its entities that are meaningful and need to be represented explicitly – E.g. EMPLOYEE entity type may be grouped into SECRETARY, ENGINEER, MANAGER, etc. – Each of these subgroupings is called a subclass of the EMPLOYEE entity type Every entity that is a member of one of these subclasses is also an employee EER  Model   3    Subclass, Superclass, Inheritance – The EMPLOYEE entity type is called the superclass for each subclass – The relationship is called a superclass/ subclass or class/subclass relationship Also often called an IS-A relationship You have 3 choices: – Define only one EMPLOYEE entity type Some attributes would be null or not used EER  Model   4   Subclass, Superclass, Inheritance – Define a separate entity type for each subgroup Failed to exploit the common properties of employees Users would have to select the correct entity type when using the system – Superclass/subclass Exploit common properties of all employees, yet recognises the distinct properties of each type – Attributes that are specific to each subclass are included with that subclass only EER  Model   5   Subclass, Superclass, Inheritance EER  Model   6    Subclass, Superclass, Inheritance An entity cannot exist in the db merely by being a member of a subclass – It must also be a member of the superclass An entity can be a member of any number of subclasses It is not necessary that every entity in a superclass is a member of some subclass EER  Model   7    Subclass, Superclass, Inheritance The type of an entity is defined by – The attributes it possesses and – The relationship types in which it participates Thus, one should consider using subclass when – There are attributes that apply to some, but not all, of the entities of an entity type – The entities of a subclass participate in a relationship unique to the subclass E.g. MANAGER manages PROJECT EER  Model   8    Subclass, Superclass, Inheritance An entity of a subclass inherits – All attributes of the superclass – All relationships in which the superclass participates A subclass can be considered an entity type in its own right EER  Model   9    Specialization Specialization – The process of defining a set of subclasses of an entity type (a superclass) – A top-down process – The set of subclasses is based upon some distinguishing characteristics of the entities in the superclass E.g. {SECRETARY, ENGINEER, TECHNICIAN} is a specialization of EMPLOYEE based upon job type. EER  Model   10    Specialization May have several specializations of the same superclass – E.g. Specialization of EMPLOYEE based on method of pay is {SALARIED_EMPLOYEE, HOURLY_EMPLOYEE} Attributes of a subclass are called specific or local attributes. – E.g. TypingSpeed of SECRETARY EER  Model   11    Specialization The subclass can also participate in specific relationship types. E.g. BELONGS_TO of HOURLY_EMPLOYEE The subset symbol indicates the direction of superclass/subclass relationship – Specific attributes are attached to the rectangle subclass EER  Model   12    Specialization In summary, specialization process allows us to: – Define a set of subclasses of an entity type – Establish additional specific attributes with each subclass – Establish additional specific relationship types between each subclass and other entity types or other subclasses EER  Model   13    Generalization Generalization is the reverse of the specialization process Several classes with common features are generalized into a superclass – Original classes become its subclasses – E.g. CAR, TRUCK generalized into VEHICLE Both CAR, TRUCK become subclasses of the superclass VEHICLE. We can view {CAR, TRUCK} as a specialization of VEHICLE EER  Model   14    Generalization EER  Model   15    Generalization Some design methodologies use a specific diagrammatic notation to distinguish between generalization and specialization – We will use the subset symbol EER  Model   16   Constraints on specialization & generalization A specialization may consist of a single subclass only – E.g. {MANAGER} specialization – In such a case, we do not use the circle notation If we can determine exactly those entities that will become members of each subclass by a condition, – The subclasses are called predicate-defined (or condition-defined) subclasses EER  Model   17   Constraints on specialization & generalization – Condition is a constraint that determines subclass members – If all subclasses have their membership condition on the same attribute of the superclass The specialization itself is called an attribute- defined specialization The attribute is called defining attribute If we do not have a condition for determining membership in a subclass, the subclass is called user-defined EER  Model   18   Constraints on specialization & generalization EER  Model   19   Constraints on specialization & generalization 2 other constraints can be applied to a specialization/generalization: – Disjointness Constraint Specifies that the subclasses of the specialization must be disjoint – The entity can be a member of at most one subclass Attribute-defined specialization implies the disjointness constraint – if the attribute used to define the membership predicate is single-valued d in the circle stands for disjoint EER  Model   20   Constraints on specialization & generalization EER  Model   21   Constraints on specialization & generalization Also used d notation to specify the constraint for user-defined subclasses that must be disjoint – E.g. {HOURLY_EMPLOYEE, SALARIED _EMPLOYEE} If subclasses are not disjoint, their set of entities may overlap – i.e. the same entity may be a member of more than one subclass – A o in the circle – Completeness Constraint May be total or partial Total specialization constraint – Specifies that every entity in the superclass must be a member of at least one subclass EER  Model   22   Constraints on specialization & generalization E.g. if every EMPLOYEE must be either an HOURLY_EMPLOYEE or SALARIED_EMPLOYEE – Then the specialization {HOURLY_EMPLOYEE, SALARIED_EMPLOYEE} is a total specialization of EMPLOYEE – Shown in EER diagrams by a double line A partial specialization allows an entity not to belong to any subclasses – E.g. some EMPLOYEE entities do not belong to subclasses {SECRETARY, ENGINEER, TECHNICIAN} – A single line is used to indicate partial specialization EER  Model   23   Constraints on specialization & generalization EER  Model   24   Constraints on specialization & generalization Hence, we have four types of specialization/generalization: – Disjoint, total – Disjoint, partial – Overlapping, total – Overlapping, partial Note: – Generalization usually is total because the superclass is derived from the subclasses Hence contains only entities in the subclasses EER  Model   25   Constraints on specialization & generalization Insertion and deletion rules apply to specialization/generalization as a consequence of constraints. Some e.g.: – Deleting an entity from a superclass will automatically deleted it from all subclasses – Inserting an entity in a superclass implies mandatory insertion in all satisfying predicate- or attribute-defined subclasses Total specialization implies mandatory insertion in at least one subclass EER  Model   26    Hierarchies and lattices A subclass may itself have further subclasses specified on it – Forms a hierarchy or a lattice of specialization Hierarchy has a constraint – Every subclass has only one superclass (called single inheritance) – Each subclass has only one parent, results in a tree structure In a lattice – A subclass can be subclass of more than one superclass (called multiple inheritance) EER  Model   27    Hierarchies and lattices EER  Model   28    Hierarchies and lattices 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 – Leads to multiple inheritance E.g. ENGINEERING_MANAGER inherits from ENGINEER, MANAGER and SALARIED_EMPLOYEE EER  Model   29    Hierarchies and lattices Some models and languages do not allow multiple inheritance – In this case, create all possible combination of subclasses – Hence, overlapping specialization require multiple subclasses – E.g. it would be necessary to create {E, A, S, E_A, E_S, A_S, E_A_S} for PERSON Some do not allow inheritance from multiple types – An entity can be a member of only one class EER  Model   30    Hierarchies and lattices Can have: – Specialization hierarchies or lattices Start with an entity type and then define subclasses of the entity type by successive specialization – Called a top down conceptual refinement process – Generalization hierarchies or lattices Start with many entity types and generalize those that have common properties – Called a bottom up conceptual synthesis process EER  Model   31    Hierarchies and lattices In structural terms, hierarchies and lattices may be identical – The only difference is the manner or order in which the schema superclasses and subclasses are specified In practice, a combination of both processes is usually employed – For simplicity, we use specialization To stand for the end result of either specialization or generalization EER  Model   32    Hierarchies and lattices EER  Model   33    Modeling of UNION types Not all superclass/subclass relationships have a single superclass – Need to model a single superclass/subclass relationships with more than one superclass each representing different entity types – The subclass will represent a collection of objects Such a subclass is called a category or UNION TYPE EER  Model   34    Modeling of UNION types – E.g. A vehicle owner can be a PERSON, a BANK or a COMPANY. A UNION type called OWNER is created to represent a subset of the union of the superclasses COMPANY, BANK, and PERSON A UNION type member must exist in at least one of its superclasses EER  Model   35   Modeling of UNION types EER  Model   36    Modeling of UNION types Shared subclass vs. category – A shared subclass is a Subset of the intersection of its superclasses – Shared subclass member must exist in all of its superclasses E.g. An engineering manager must be an ENGINEER, a MANAGER and a SALARIED_EMPLOYEE – A category is a Subset of the union of its superclasses – Category member must exist in only one of the superclasses EER  Model   37    Modeling of UNION types OWNER may be a COMPANY, a BANK or a PERSON – Attribute inheritance works more selectively in categories E.g. OWNER entity inherits attributes of a COMPANY, a PERSON or a BANK – Depending on the superclass the entity belongs A shared subclass inherits all the attributes of its superclasses – REGISTERED_VEHICLE vs. VEHICLE REGISTERED_VEHICLE includes some cars and some trucks, but not necessarily all EER  Model   38   Modeling of UNION types If VEHICLE is partial, VEHICLE will not preclude other types of entities such as motorcycles However, a category implies only cars and trucks, but not other types of entities EER  Model   39    Modeling of UNION types A category can be total or partial – A total category holds the union of all entities in its superclasses All the superclass instances belong to the category – A partial category can hold a subset of the union Some superclass instances not in category Superclasses of category may have – Different key attributes, or – Same key attributes EER  Model   40   Formal definitions of ERR model A class is a set or collection of entities, that may includes – Entity type, subclass, superclass, or category Subclass S is a class whose: – Type inherits all the attributes and relationship of a class C – Set of entities must always be a subset of the set of entities of the other class C S⊆C – C is called the superclass of S EER  Model   41   Formal definitions of ERR model – A superclass/subclass relationship exists between S and C Specialization Z – Z = {S1, S2,…, Sn} is a set of subclasses with same superclass G Hence, G/Si is a superclass/subclass relationship for i = 1, …., n. – G is called a Generalized entity type Superclass of the specialization Generalization of the subclasses {S1, S2,…, Sn} EER  Model   42   Formal definitions of ERR model – Z is total if we always have: S1 ∪ S2 ∪ … ∪ Sn = G Otherwise, Z is partial. – Z is disjoint if we always have: Si ∩ Sj = { }, for i ≠ j; Otherwise, Z is overlapping. Subclass S of C is predicate defined – If predicate (condition) p on attributes of C is used to specify membership in S – That is, S = C[p], where C[p] is the set of entities in C that satisfy condition p EER  Model   43   Formal definitions of ERR model – A subclass not defined by a predicate is called user-defined – Attribute-defined specialization If a predicate A = ci is used to specify membership in each subclass Si in Z, where – A is an attribute of G, and – ci is a constant value from the domain of A Note: – If ci ≠ cj for i ≠ j, and A is single-valued, then the attribute- defined specialization will be disjoint. EER  Model   44   Formal definitions of ERR model Category or UNION type T – A class that is a subset of the union of n defining superclasses D1, D2,… Dn, n > 1 T ⊆ (D1 ∪ D2 ∪ … ∪ Dn) – Can have a predicate pi on the attributes of Di to specify entities of Di that are members of T If a predicate is specified on every Di – T = (D1[p1] ∪ D2[p2] ∪ … ∪ Dn [pn]) EER  Model   45   Formal definitions of ERR model The definition of relationship type in ER/ EER should have 'entity type' replaced with 'class‘ to allow relationships among classes in general – The graphical notation of EER is consistent with ER because all classes are represented by rectangles EER  Model   46   Formal definitions of ERR model ER/EER diagrams are a specific notation for displaying the concepts of the model diagrammatically DB design tools use many alternative notations for the same or similar concepts One popular alternative notation uses UML class diagrams EER  Model   47    Design choices Specializations and subclasses can be defined to make conceptual model accurate – Drawback is a cluttered design If subclass has few local attributes and no specific relationships – Merged into superclass For entities that are not members of the subclass – The merged local attributes would be NULL Include a type attribute to specify the subclass EER  Model   48    Design choices Category should generally be avoided unless the situation warrants it Choice of disjoint/overlapping and total/ partial constraints is driven by the rules in the miniworld – If requirements do not indicate any constraints Default is overlapping and partial – Since this does not specify any restrictions on subclass membership EER  Model   49    UML Example EER  Model   50   EER  Model   51    ER Data Modeling Conceptual modeling is a very important phase in designing a successful db application – Application refers to A particular db and Associated programs that implement db queries and updates ER model remains the mainstream approach for conceptual data modeling EER  Model   52