CPS 430/542 Lecture notes: Essential E/R Elements



Coverage: [FCDB] §2.1 (pp. 23-38)


Database modeling

  • why? to determine structure of database prior to implementation
  • start with ideas and English specifications
  • use entity-relationship (E/R) model
    • developed at MIT
    • analogies to OOP
      • entity = object
      • entity set = class
      • attribute = property
    • not standardized; textbooks vary
  • end goal: a set of relations


Building blocks of the E/R model

  • has syntax (form) and semantics (meaning)
  • entity set (square), usually plural nouns
  • relationship (diamond), usually verbs
  • attribute (oval), usually adjectives
  • undirected edge (many-many relationship)
  • uni-directional arrow (many-one relationship)
  • bi-directional arrow (one-one relationship)


Example entity set with attributes


Types of relationships

  • binary, teriary, and so on
  • can we have a unary relationship?


Multiplicity of binary relationships

  • many-many relationship, e.g.,




      relationship set: a set of tuples connecting entities
      {(linus, cps430),
       (linus, cps444),
       (lucy,  cps430),
       (lucy,  cps444)}
      
  • many-one relationship, e.g.,




      relationship set:
      {(linus, perugini),
       (lucy,  courte),
       (linus, perugini),
       (linus, smith)}
      
      one of the last two tuples violates the semantics of the diagram
    • means `at most' one, not `exactly' one or `at least' one
    • special case of many-many relationship
    • any property which holds for a many-many relationship also will hold for a many-one relationship

  • one-one relationship, e.g.,




      relationship set:
      {(linus, lucy),
       (tom,   sally),
       (tom,   cindy)}
      
      one of the last two tuples violates the semantics of the diagram
    • special case of many-one relationship
    • any property which holds for a many-one relationship
      • also will hold for a one-one relationship,
      • but may not hold for a many-many relationship


Relationship roles

  • an entity set may participate in more than one relationship
  • an entity set may participate more than once in a single relationship
  • label the edges to differentiate roles
  • father relationship



  • manager relationship



  • married relationship



    • one-one
    • (anti-symmetric) relationship set:
      {(linus, lucy),
       (tom,   sally)}
      

  • another married relationship



    • still one-one
    • (symmetric) relationship set:
      {(linus, lucy),
       (lucy,  linus),
      
    • how to represent symmetry: no direct support, make a note

  • friendship relationship



    • not one-one
    • (symmetric) relationship set:
      {(linus, lucy),
       (lucy,  linus)}
      
  • moral of the story
    • symmetricity has nothing to do with one-one relationships
    • cannot encode symmetry using the E/R model; must add a note
  • can a many-one relationship be symmetric?


Attributes on relationships

  • a fact of the relationship



  • how else can we represent this? use an additional entity set [Classrooms] and a ternary relationship


Multiway relationships

  • e.g., a ternary relationship



  • means for 1 instructor and 1 course, there can be at most 1 classroom
  • not the same as 3 binary, many-one relationships between 3 entity sets
  • but there exists a process, called pushing out, to convert to an equivalent to all binary, many-one relationships


Instructor, courses, TA ternary relationship example




  • means for 1 student and 1 course, there can be at most 1 TA
  • relationship set:
    {(perugini, cps430, karthik),
     (perugini, cps430, vijay), 
     (perugini, cps444, vijay),
     (courte,   cps444, karthik)}
    
    one of the first two tuples violates the semantics of the diagram

  • not equivalent to 3 binary relationships without an arrow pointing to any of the original 3 entity sets:

    ------------------
    Instructor, Course
    ------------------
    perugini, cps430
    perugini, cps444
    courte,   cps444
    ------------------
    
    -----------------
    Instructor, TA
    -----------------
    perugini, karthik
    perugini, vijay
    courte,   karthik
    -----------------
    
    ---------------
    Course, TA
    ---------------
    cps430, karthik
    cps430, vijay
    cps444, vijay
    cps444, karthik
    ---------------
    
  • the following connections violate the semantics of the original (multiway relationship) diagram
    • perugini, cps430 → karthik, vijay
    • perugini, cps444 → karthik, vijay
    • courte, cps444 → karthik, vijay

  • not equivalent 3 binary relationships with two arrows entering the TA entity set (one from each of the other entity sets):

    ------------------
    Instructor, Course
    ------------------
    perugini, cps430
    perugini, cps444
    courte,   cps444
    ------------------
    
    -----------------
    Instructor, TA
    -----------------
    perugini, karthik
    perugini, vijay
    courte,   karthik
    -----------------
    
    one of the first two tuples violates the semantics of the diagram
    ---------------
    Course, TA
    ---------------
    cps430, karthik
    cps430, vijay
    cps444, vijay
    cps444, karthik
    ---------------
    
    one of the first two tuples violates the semantics of the diagram, and
    one of the last two tuples violates the semantics of the diagram

    now, 1 instructor can have at most 1 TA for any course, and 1 course, taught by any instructor, can have at most 1 TA


Pushing out





  • are the diagrams equivalent?
  • 1 instructor, 1 course → 1 TA constraint not met
  • new entity set [teach] is called a connecting entity set
  • may think of entities of new entity set [teach] as the tuples of the relationship set for the multiway relationship
  • teach
    -----
    {(perugini, cps430, karthik),
     (perugini, cps430, vijay),    ← violates original constraints!
     (perugini, cps444, vijay),
     (courte,   cps444, karthik)}
    
    teach1 → (perugini, cps430, karthik)
    teach1 → (perugini, cps430, vijay)   ← violates original constraints!
    teach2 → (perugini, cps444, karthik)
    teach3 → (courte, cps444, karthik)
    
  • to do the reverse, we would need the contents of the entity set [teach] in order to determine to which of the original 3 entity sets arrows should point
  • why push out?
    • to learn the E/R model
    • converting pushed out designs to relations might be easier than converting designs with ternary relationships
    • some data models, e.g., Object Definition Language (ODL), limit relationships to binary relationships


Another `pushing out' example

    
    
       
  • more natural if the relationship corresponds to something tangible in the real world

  • convert


    to

    Contracts
    ---------
    contract1 → (tom hanks, cast away, 20th century-fox)
    contract1 → (tom hanks, cast away, warner bros.)       ← violation?
    contract2 → (tom hanks, you've got mail, warner bros.)
    contract3 → (meg ryan, you've got mail, warner bros.)
    contract4 → (tom hanks, you've got mail, mgm)          ← violation?
    
    now, given a star and a movie, there can be more than one studio so long as they are not associated with the same contract


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