chapter 6

• Extended Entity Relationship (EER) model


  Semantic data model developed in response to increasing complexity of applications
• EER model


  • DBMS based on the EER model often described as an object/relational database management system (O/RDBMS)
  • Primarily geared to business applications
• EER diagram (EERD)


  Diagram using the EER model
• Entity supertype


  • Generic entity type that is related to one or more entity subtypes
  • Contains common characteristics
• Entity subtype


  Contains unique characteristics of each entity subtype
• Employee entity supertype will have common entries e.g. staff_numb
• Academic staff entity subtype will have unique entries e.g. degree
• Maintenance staff entity subtype will have unique entries e.g. plumbing
• Administrative staff entity subtype will have unique entries such as speciality e.g. finance, HR
• Specialization hierarchy

  • Depicts arrangement of higher-level entity supertypes (parent entities) and lower-level entity subtypes (child entities)
  • Subtype can exist only within context of supertype
  • Every subtype can have only one supertype to which it is directly related

  
• Specialization hierarchy


  • Supports attribute inheritance
  • Enables an entity subtype to inherit the attributes and relationships of the supertype
  • Defines a special supertype attribute known as the subtype discriminator
  • Defines disjoint/overlapping constraints and complete/partial constraints
• At implementation level, supertype and its subtype(s) depicted in the specialization hierarchy maintain a 1..1 relationship

  
• Specialization


  • Top-down process of identifying lower-level, more specific entity subtypes from higher-level entity supertypes
  • Based on grouping unique characteristics and relationships of the subtypes

  
• Generalization


  • Bottom-up process of identifying higher-level, more generic entity supertypes from lower-level entity subtypes
  • Based on grouping common characteristics and relationships of the subtypes

  
• Composition


  • Special case of aggregation - mandatory association
  • When the parent entity instance is deleted, all child entity instances are automatically deleted
• Aggregation


  • A larger entity can be composed of smaller entities
  • Optional association
  • When you delete the parent entity the child entity is not deleted
• Entity clustering


  • A "virtual" entity type used to represent multiple entities and relationships in an ERD
  • Considered "virtual" or "abstract" because it is not actually an entity in final ERD
  • Temporary entity used to represent multiple entities and relationships
  • Eliminate undesirable consequences caused by missing info
  • Avoid display of attributes when entity clusters are used
• Natural key


  A real-world, generally accepted identifier used to uniquely identify real-world objects
• Primary key


  An attribute or combination of attributes that uniquely identifies entity instances in an entity set and guarantees entity integrity
• Primary key guidelines


  • A primary key should be simple, unique, immutable, and minimal
  • A primary key should be an integer or a short character string
  • A primary key should not contain null values
  • A primary key should not contain repeating groups
• Composite primary keys are useful as identifiers of composite entities, where each primary key combination is allowed only once in *:* relationship
• Composite primary keys automatically provide the benefit of ensuring that there cannot be duplicate values
• Entity set


  Main function is to guarantee entity integrity
• Primary Key Guidelines - summary
• Is the following an example of good Primary Keys?
• When to Use Composite Primary Keys

  • Useful as identifiers of composite entities, where each primary key combination is allowed only once in *:* relationship
  • Useful as identifiers of weak entities, where weak entity has strong identifying relationship with parent entity
• When to Use Composite Primary Keys

  • Represent a real-world object that is existent-dependent on another real-world object
  • Represent a real-world object that is represented in the data model as two separate entities in a strong identifying relationship (e.g. INVOICE and LINE)
• Surrogate Primary Key


  A replacement for unsuitable attributes, especially helpful when there is no natural key, the selected candidate key has embedded semantic contents, or the selected candidate key is too long or cumbersome
• When To Use Surrogate Primary Keys

  • No natural primary key, combination of attributes is not practical or helpful
• Design Cases
• Implementing 1:1 Relationships


  • Foreign keys work with primary keys to properly implement relationships in a relational model
  • In a 1:1 relationship, place the FK in one of the entities - preferred, but which one?
• Implementing 1:1 Relationships


  • EMPLOYEE is Mandatory to DEPARTMENT, 'Manager' role is played In the DEPARTMENT
• 1. many relationship
• Time-variant data


  Data whose values change over time and for which you must keep a history of data changes
• Maintaining History of Time-Variant Data

  • 3 PK components
• Fan Trap


  Design trap that occurs when having one entity in two 1:* relationships to other entities, thus producing an association among other entities that is not expressed in the model
• Fan Traps - example


  • No way to identify what player belongs to which team
• Redundant Relationships


  Occur when there are multiple relationship paths between related entities, main concern is that redundant relationships remain consistent across model
• Redundant Relationships - example

  • Redundant relationship between DIVISION and PLAYER, DIV_ID is redundant in PLAYER, Transitive 1..* relationship between DIVISION and PLAYER through the TEAM entity
• Data Modeling Checklist