CPS 430/542 Lecture notes: E/R Modeling Constraints and Weak Entity Sets



Coverage: [FCDB] §§2.3-2.4 (pp. 47-59)


Solved exercise 2.1.1 on p. 36 from [FCDB]


Inheritance in E/R modeling

  • special-case entity sets (or subclasses)
  • use an `isa' relationship
    • represented with a triangle (half diamond) which points to superclass
    • always one-one
  • entity belongs to both entity sets
  • subclasses inherit the attributes of their ancestors and participate in their relationships
  • example:
    • cartoons have voices
    • murder mysteries have weapons
  • what about multiple inheritance?
    • e.g., Roger Rabbit
    • not every situation can be modeled with an E/R diagram


Types of Graphs (DAGs and trees)

  • a directed graph with no cycles, e.g., a C++ class inheritance hierarchy involving multiple inheritance
  • in a tree, there is only one path from the root to each leaf
  • every tree is also a DAG, but
  • some DAGs are not trees
  • how can we model these types of graphs using the E/R model?


Constraints

  • additional requirements some of which the modeling language can enforce and others for which it cannot
  • reflection of schema, not contents
  • examples
  • single-valued constraints
    • attributes may only have a single value
    • no way to indicate the absence of an attribute value
    • many-one relationships
  • keys
  • referential integrity
  • domain constraints


Keys

  • attribute or set of attributes from an entity class which uniquely identifies an entity
  • two members of an entity set may not have the same value for a key, e.g., ssn
  • underline key attributes in E/R
  • can consist of more than one attribute
  • there may be more than one key
    • but no way to represent this in E/R
    • choose one
  • sometimes key attributes do not belong to entity set: weak entity sets (coming soon ...)
  • storage implications, e.g., C int vs. linked list of ints analogy


Referential Integrity

  • strengthens at most one to exactly one
  • use curved arrow head in E/R ---)
  • ---) = ---> + `referenced entity must exist', i.e., you can only add a curved arrow head if you already have a regular arrow head
  • movie-studios example
  • enforcement
    • prevent deletion of referenced entity (in [Studios]) until no referents (in [Movies]) refer to it
    • if referenced entity deleted, all referent entities are deleted as well
    • force the entry or association of a referenced entity when inserting a member of the referent entity set
  • 3 ways to enforce
    • 2 involve deletions
    • 1 involves insertion
  • extended example
    • studios can exist without a president,
    • but you cannot call yourself a president if you are not the president of a studio


Other kinds of modeling constraints

  • attributes
    • types (int, floats)
    • ranges
      • ≤ 4.0 (gpa)
      • ≤ 25 (baseball roster)
  • cardinality on relationships
  • write `Note(s): ...' on the margin of the page for aspects which cannot be represented using the E/R model


Weak-entity sets

  • an entity set which derives a subset of its key attributes from other entity sets
  • sources of weakness
    • hierarchy of entity sets (not inheritance)
        crew number and studio name form a key (e.g., homo sapiens); species name and genus name form a key
    • connecting entity sets resulting from `pushing out', i.e., those with no attributes
  • a strong entity set must live at the end of a chain of weak sets
  • gets its key from
    • zero or more of its attributes, and
    • key attributes from entity sets which are reached by certain many-one, called supporting, relationships
    • weak entity set may participate in non-supporting relationships
  • representation in E/R
    • use double rectangle [[...]] for weak entity set
    • use double diamond <<...>> for supporting relationship


Example of a weak entity set

  • entity set [[Crews]] is weak (a weak entity set)
  • relationship <<works for>> is a supporting relationship
  • key of entity set [[Crews]] consists of its attribute (number) and the key attribute (name) from entity set [Studios]
  • entity set [[Crews]] gets part of its key from the key of entity set [[Studios]]


Requirements for supporting relationship

  • binary
  • many-one (includes one-one)
  • referential integrity
  • attributes supplied from referenced entity set F to help form key for weak entity set E must be key attributes of F
  • if referenced entity set F is weak, following the chain to a non-weak entity set
  • rationale behind requirements, especially binary, many-one, and referential integrity requirements


Solved exercise 2.4.4(b) on p. 59 from [FCDB]


References

    [FCDB] J.D. Ullman and J. Widom. A First Course in Database Systems. Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.

Return Home