Entity Cluster in ERD and Primary Keys Characteristics

Entity Cluster

• Entity cluster is a "virtual" entity type that represents multiple entities and relationships in an ERD.
• It is formed by combining multiple interrelated entities and relationships into a single object.

Primary Keys

• Characteristics of primary keys:
  • Must have unique values
  • Should be non-intelligent
  • Must not change over time
  • Preferably numeric or composed of a single attribute
• Natural keys are identifiers that exist in the real world and can make good primary keys.
• Composite keys are useful to represent M:N relationships and weak entities.
• Surrogate primary keys are useful when no suitable natural key makes a good primary key.

Time-Variant Data

• Time-variant data is data whose values change over time.
• To maintain history of time-variant data:
  • Create an entity containing the new value, date of change, and other time-relevant data.
  • The entity maintains a 1:M relationship with the entity for which history is maintained.

Relationships

• In a 1:1 relationship, place the primary key of the mandatory entity as a foreign key in the optional entity.
• In a 1:1 relationship, place the primary key of the entity that causes the least number of nulls as a foreign key.
• Fan trap occurs when one entity is in two 1:M relationships to other entities, producing an association among other entities not expressed in the model.
• Redundant relationships occur when there are multiple relationship paths between related entities, and the main concern is that they remain consistent across the model.

Extended Entity Relationship (EER) Model

• The EER model adds semantics to the ER model via entity supertypes, subtypes, and clusters.
• An entity supertype is a generic entity type related to one or more entity subtypes.
• Specialization hierarchy depicts the arrangement and relationships between entity supertypes and entity subtypes.
• Inheritance means an entity subtype inherits attributes and relationships of its supertype.

Entity Supertypes and Subtypes

• Entity supertype contains common characteristics of entity subtypes.
• Entity subtype contains unique characteristics of each entity subtype.
• A subtype exists only within the context of its supertype, and every subtype has only one supertype to which it is directly related.

Subtype Discriminator

• A subtype discriminator is an attribute in the supertype entity that determines to which entity subtype each supertype occurrence is related.
• The default comparison condition for the subtype discriminator attribute is an equality comparison.
• The subtype discriminator may be based on other comparison conditions.

Disjoint and Overlapping Constraints

• Disjoint subtypes are subtypes that contain unique subsets of the supertype entity set.
• Overlapping subtypes are subtypes that contain non-unique subsets of the supertype entity set.

Completeness Constraint

• Specifies whether an entity supertype occurrence must be a member of at least one subtype.
• Partial completeness is symbolized by a circle over a single line, and some supertype occurrences are not members of any subtype.
• Total completeness is symbolized by a circle over a double line, and every supertype occurrence must be a member of at least one subtype.

Specialization and Generalization

• Specialization identifies more specific entity subtypes from a higher-level entity supertype and is a top-down process.
• Generalization identifies more generic entity supertypes from lower-level entity subtypes and is a bottom-up process.

Design Cases

• Design Case 1: Implementing 1:1 Relationships - Foreign keys work with primary keys to properly implement relationships in the relational model.
• Design Case 2: Maintaining History of Time-Variant Data - Normally, existing attribute values are replaced with new values without regard to previous values.
• Design Case 3: Fan Traps - A design trap occurs when a relationship is improperly or incompletely identified.
• Design Case 4: Redundant Relationships - Redundancy is seldom a good thing in a database environment, and the main concern is that redundant relationships remain consistent across the model.