E/R Model

• High level design of a data model for a specific application
• Allows us to sketch database designs
• Pictures are called entity-relationship diagrams

Definitions

• Entity
  • Thing or object
• Entity set
  • Collection of similar entities
  • Notation: Rectangle
  • Operations are defined over a entity set
• Attribute
  • Property of an entity
  • NotatioN: Oval with a line to the entity set where it belongs
• Key
  • Attribute(s) that can uniquely identify an attribute
  • Can be one or more attributes (if multiple attributes are needed to uniquely identify the entity)
  • Notation: Underlined attribute(s)
  • Types
    • Minimal Key = Key where removing any attribute of the key would make it no longer can uniquely identify records
    • Candidate Keys = All keys for an entity that are minimal
    • Primary Key = A chosen candidate key that is used for relations
    • Super-key = a superset of attributes that includes a key (non-minimal)
• Relationship
  • Connection between two or more entity sets
  • Can have additional attributes
  • Notation: Diamond with lines to each of the entity sets involved
  • Types
    • Many to many
      • An entity of either set can be connected to many entities of the other set
      • Notated as edge without any arrows
      • Ex: friends
    • Many to one / One to many
      • One set of entities can have a relationship with many of a different set, but not the other way around
      • Notated as an arrow from many to one
      • Ex: children and father
    • One to one
      • Each entity can only be in one of these relationships
      • Notated as an arrow both ways
      • If not exactly one, then “partial participation”
      • Ex: marriage
    • Exactly one
      • At most and at least one
      • Represented as a rounded arrow
      • Modifier of many to one and one to one arrows
      • Called “total participation”
      • Ex: each student must have exactly one grade
• Relationship Set
  • Collection of similar relationship instances (all the entities that are connected to each other) by a single relationship
• Roles
  • Sometimes an entity set appears more than once in a relationship
  • Notation: Label the edges between the relationship and the entity set with names called roles
  • For one-to-one relationships
• Weak Entity Sets
  • Weak entity = depends on another entity to identify itself
  • Notation:
    • Double diamond for supporting many-one relationships (the relationship to the entity that helps identify the first)
    • Double rectangle for the weak entity set
  • Alternative: Assign meaningless keys to the weak entities
  • Ex: Players on a team have numbers that are only unique within the team so it also needs a reference to the team to be unique
• Subclasses
  • For when an entity is a special case of another entity with additional attributes
  • Notation: Triangle pointing to superclass
  • Often has “ISA” written inside the triangle to mean “is a”
• Aggregation
  • Allows us to indicate that a relationship set participates in another relationship set
  • Notation: Dashed box around the other relationship set

Designing

• Principles
  • Avoid redundancy
    • Redundancy increases complexity and inconsistency
  • Prefer attributes over entity sets
    • Attributes are simpler
  • Avoid weak entity sets, aggregations, etc
    • Increase model complexity
• Choices
  • Entity vs. relationship
  • Entity vs attribute
  • Binary vs ternary vs aggregation
  • Total participation vs partial participation (multiplicity)
• Tips
  • Entity set vs attributes
    • Entity sets should have at least one non-key attribute
    • If there is a clear relationship (many to one), use entity sets
  • Weak entity sets
    • Use when there is no global authority capable of creating unique IDs

Constraints

• Cannot compare functional dependencies, candidate keys
• Total participation only on the one side of many to one
• Can get around these by just stating assumptions if they are not contradictory