ER by yaoyufang


									Entity-Relationship Data Model

            Reading: Chapter 2
  Slides adapted from material by Profs.
   Jeff Ullman (Stanford) and Art Keller
       Entity/Relationship Model
Diagrams to represent designs.
• Entity like object, = “thing.”
• Entity set like class = set of “similar”
• Attribute = property of entities in an entity set,
  similar to fields of a struct.
• In diagrams, entity set  rectangle;
  attribute  oval.
                              ID       name     phone

                                   Students     height
• Connect two or more entity sets.
• Represented by diamonds.

            Students        Taking             Courses

Fall 2007              Chris Clifton - CS541             3
                Relationship Set
Think of the “value” of a relationship set as a table.
• One column for each of the connected entity
• One row for each list of entities, one from each
  set, that are connected by the relationship.
            Students        Courses
            Sally           CS541
            Sally           CS555
            Joe             CS541
            …               …

Fall 2007              Chris Clifton - CS541         4
              Multiway Relationships
Usually binary relationships
  (connecting two E.S.)
• However, there are some
  cases where three or
  more E.S. must be
  connected by one         Students              Taking   Courses
• Example: relationship
  among students, courses,
  TA's (and graders).                                      Assisting

    Possibly, this E/R
    diagram is OK:
Fall 2007                Chris Clifton - CS541                         5
             Multiway relationships
• Works in CS541, because each TA (or
  grader) is a TA of all students. Connection
  student-TA is only via the course.
• But what if students were divided into
  sections, each headed by a TA?
     – Then, a student in CS541 would be related to
       only one of the TA's for CS541. Which one?
• Need a 3-way relationship to tell.

Fall 2007            Chris Clifton - CS541            6

Students     Enrolls


  Students   Courses   TAs
  Ann        CS541     Li
  Sue        CS541     Li
  Bob        CS541     Chris
  …          …         …
• Our (or to give credit where due, Prof.
  Ullman’s) running example for the course.
                          name     addr       license

              Serves              Bars                   Frequents

              Beers               Likes                  Drinkers

Fall 2007   name   manf     Chris Clifton - CS541       name   addr   8
     Multiplicity of Relationships

 Many-many        Many-one           One-one

Representation of Many-One
• E/R: arrow pointing to “one.”
  – Rounded arrow = “exactly one.”
 Drinkers Have Favorite Beers
              name    addr      license

  Serves              Bars                 Frequents

  Beers                                    Drinkers

name   manf                               name   addr
           One-One Relationships
Put arrows in both directions.
   Manfs                    Beers

Design Issue:
Is the rounded arrow justified?
Design Issue:
Here, manufacturer is an E.S.
In earlier diagrams it is an attribute.
Which is right? Chris Clifton - CS541
Fall 2007                                 11
      Attributes on Relationships

         Bars       Sells      Beers

• Shorthand for 3-way relationship:


         Bars       Sells      Beers
• A true 3-way relationship.
  – Price depends jointly on beer and bar.
• Notice arrow convention for multiway
  relationships: “all other E.S. determine
  one of these.”
  – Not sufficiently general to express any
  – However, if price, say, depended only on
    the beer, then we could use two 2-way
    relationships: price-beer and beer-bar.
  – Or better: just make price an attribute of
           Converting Multiway to 2-Way
• Baroque in E/R, but necessary in certain “object-oriented”
• Create a new connecting E.S. to represent rows of a
  relationship set.
   – E.g., (Joe's Bar, Bud, $2.50) for the Sells relationship.
• Many-one relationships from the connecting E.S. to the

                 The-              The-              The-
                 Bar               Beer              Price

                  Bars             Beers             Price
Sometimes an E.S. participates more than
  once in a relationship.
• Label edges with roles to distinguish.
                    Husband Wife
                    d1        d2
                    d3        d4
 husband       wife
                    …         …

Fall 2007              Chris Clifton - CS541   15
                      Buddy1     Buddy2
      Buddies         d1         d2
                      d1         d3
 1               2    d2         d1
                      d2         d4
      Drinkers        …          …

• Notice Buddies is symmetric, Married
  – No way to say “symmetric” in E/R.
Design Question
Should we replace husband and wife by
 one relationship spouse?
Subclass = special case = fewer entities =
  more properties.
• Example: Ales are a kind of beer. In
  addition to the properties (= attributes and
  relationships) of beers, there is a “color”
  attribute for ales.

Fall 2007         Chris Clifton - CS541      18
                E/R Subclasses
• Assume subclasses form a tree (no
  multiple inheritance).
• isa triangles indicate the subclass
        name        Beers    manf


        color       Ales
     Different Subclass Viewpoints
1. E/R viewpoint: An entity has a component in each
  entity set to which it logically belongs.
  – Its properties are the union of the properties of these E.S.
2. Contrasts with object-oriented viewpoint: An object
  (entity) belongs to exactly one class.
  – It inherits properties      name          Beers          manf
    of its superclasses.

                                                isa     Pete’s Ale

                                color          Ales
          Multiple Inheritance
Theoretically, an E.S. could be a subclass
 of several other entity sets.
           name         manf       name     vintner

             Beers                     Wines

                  isa                     isa

How should conflicts be resolved?
• Example: manf means vintner for wines,
  bottler for beers. What does manf mean
  for “grape beers”?
• Need ad-hoc notation to resolve
• In practice, we shall assume a tree of
  entity sets connected by isa, with all “isas”
  pointing from child to parent.
Fall 2007         Chris Clifton - CS541       22
A key is a set of attributes whose values can
  belong to at most one entity.
• In E/R model, every E.S. must have a key.
     – It could have more than one key, but one set
       of attributes is the “designated” key.
• In E/R diagrams, you should underline all
  attributes of the designated key.

Fall 2007             Chris Clifton - CS541           23
• Suppose name is key for Beers.
           name           Beers           manf


           color           Ales

• Beer name is also key for ales.
  – In general, key at root is key for all.
                   A Multiattribute Key
                     number             hours

            dept           Courses              room

• Possibly, the combination of hours + room also
  forms a key, but we have not designated it as

Fall 2007              Chris Clifton - CS541           25
                     Weak Entity Sets
Sometimes an E.S. E ’s key comes not (completely) from its
  own attributes, but from the keys of one or more E.S.’s to
  which E is linked by a supporting many-one relationship.
• Called a weak E.S.
• Represented by putting double rectangle around E and a
  double diamond around each supporting relationship.
• Many-one-ness of supporting relationship (includes 1-1)
     – With many-many, we wouldn't know which entity provided the
       key value.
• “Exactly one” also essential, or else we might not be able
  to extract key attributes by following the supporting

Fall 2007                  Chris Clifton - CS541                    26
                Example: Logins (Email
Login name = user name + host name, e.g.,
• A “login” entity corresponds to a user name on a particular host, but
  the passwd table doesn’t record the host, just the user name, e.g.,
• Key for a login = the user name at the host (which is unique for that
  host only) + the IP address of the host (which is unique globally).
                name                                name

              Logins                @               Hosts

• Design issue: Under what circumstances could we simply make
  login-name and host-name be attributes of logins, and dispense with
  the weak E.S.?

Fall 2007                   Chris Clifton - CS541                     27
            Example: Chain of “Weakness”
Consider IP addresses consisting of a primary domain
(e.g., edu), subdomain (e.g., ucsc), and host (e.g., soe).
name                           name                     name

Hosts           @
               In2          2ndary              In1
                                                 @    Primary
                           Domains                    Domains

• Key for primary domain = its name.
• Key for secondary domain = its name + name of primary
• Key for host = its name + key of secondary domain = its
  name + name of secondary domain + name of primary
Fall 2007               Chris Clifton - CS541                28

                        The-                  The-       The-
                        Bar                   Beer       Price
 “Connecting”           Bars                 Beers       Price
  Entity Sets
                   name        addr        name   manf   price
  Are Weak
• In this special case, where bar and beer
  determine a price, we can omit price from the
  key, and remove the double diamond from
• Better: price is attribute of BBP.
Fall 2007          Chris Clifton - CS541                     29
Entity-Relationship Data Model

       Reading: Chapters 2.4, 3.2-3.3
 Homework 1 available at course web page
               this afternoon!
  Slides adapted from material by Profs. Jeff
   Ullman (Stanford) and Art Keller (UCSC)
   Relationship To Weak Entities
• Consider a relationship, Ordered, between two entity
  sets, Buyer and Product                                UPC

    Buyer              Ordered               Product

              Name                 Qty
• How can we add Shipments to the mix?                   UPC

    Buyer              Ordered               Product

Name            Qty                                       ID
             is wrong. Why?
 • Solution: make Ordered into a weak entity set. UPC

      Buyer        OB         Ordered   OP      Product

   Name               Qty

 • And then add Shipment.                                 UPC

      Buyer        OB         Ordered   OP      Product

  Name                Qty
Part-of is                                                 ID
many-many and                 Part of        Shipment
not a weak        Qty
                   Design Principles
Setting: client has (possibly vague) idea of what
he/she wants. You must design a database that
represents these thoughts and only these thoughts.
Avoid redundancy
= saying the same thing more than once.
•Wastes space and encourages inconsistency.
            name                  name   addr

           Beers       ManfBy       Manfs
Bad: repeats manufacturer address for each
beer they manufacture.
                    name     manf

                      Beers         Manf

Bad: manufacturer’s name said twice.
      name   manf                      name    addr

        Beers              ManfBy          Manfs
              Use Schema to Enforce
• The design schema should enforce as
  many constraints as possible.
     – Don't rely on future data to follow

• If registrar wants to associate only one
  instructor with a course, don't allow sets of
  instructors and count on departments to
  enter only one instructor per course.
Fall 2007              Chris Clifton - CS541   35
         Entity Sets Vs. Attributes
You may be unsure which concepts are worthy of being entity
  sets, and which are handled more simply as attributes.
• Especially tricky for the class design project, since there is
  a temptation to create needless entity sets to make project

               name                           name

              Beers         ManfBy           Manfs

Right:                    name   manf

            Intuitive Rule for E.S. Vs.
Make an entity set only if it either:
1. Is more than a name of something; i.e., it
   has nonkey attributes or relationships
   with a number of different entity sets, or
2. Is the “many” in a many-one relationship.

Fall 2007         Chris Clifton - CS541     37
The following design illustrates both points:

          name                            name    addr

         Beers           ManfBy             Manfs

• Manfs deserves to be an E.S. because we record
  addr, a nonkey attribute.
• Beers deserves to be an E.S. because it is at the
  “many” end.
   – If not, we would have to make “set of beers” an attribute of
     Manfs – something we avoid doing, although some may tell
     you it is OK in E/R model.
             Don't Overuse Weak E.S.
•     There is a tendency to feel that no E.S. has its entities
      uniquely determined without following some
•     However, in practice, we almost always create unique
      ID's to compensate: social-security numbers, VIN's,
•     The only times weak E.S.'s seem necessary are when:
     a) We can't easily create such ID's; e.g., no one is going to accept
        a “species ID” as part of the standard nomenclature (species is
        a weak E.S. supported by membership in a genus).
     b) There is no global authority to create them, e.g., crews and

Fall 2007                    Chris Clifton - CS541                     39
                Relational Design
Simplest approach (not always best): convert each
  E.S. to a relation and each relationship to a
            Entity Set  Relation
E.S. attributes become relational attributes.
                         name     manf

     Beers(name, manf)
Fall 2007            Chris Clifton - CS541      42
               Keys in Relations
An attribute or set of attributes K is a key for
  a relation R if we expect that in no
  instance of R will two different tuples
  agree on all the attributes of K.
• Indicate a key by underlining the key
• Example: If name is a key for Beers:
     Beers(name, manf)

Fall 2007          Chris Clifton - CS541       43
               E/R Relationships 
Relation has attribute for key attributes of
  each E.S. that participates in the
• Add any attributes that belong to the
  relationship itself.
• Renaming attributes OK.
     – Essential if multiple roles for an E.S.

Fall 2007              Chris Clifton - CS541     44
     name    addr                   name   manf

      Drinkers          Likes         Beers

       1     2

      Buddies            Favorite

   husband       wife
                        Likes(drinker, beer)
      Married           Favorite(drinker, beer)
                        Married(husband, wife)
                        Buddies(name1, name2)
• For one-one relation Married, we can
  choose either husband or wife as key.
                Weak Entity Sets,
            Relationships  Relations
• Relation for a weak E.S. must include its
  full key (i.e., attributes of related entity
  sets) as well as its own attributes.
• A supporting (double-diamond)
  relationship yields a relation that is actually
  redundant and should be deleted from the
  database schema.

Fall 2007          Chris Clifton - CS541       46
                     name                   name
                    Logins       @          Hosts
Logins(loginName, hostName)
At(loginName, hostName, hostName2)
• In At, hostName and hostName2 must be the
  same host, so delete one of them.
• Then, Logins and At become the same relation;
  delete one of them.
• In this case, Hosts’ schema is a subset of
  Logins’ schema. Delete Hosts?
              Subclasses  Relations
Three approaches:
1. Object-oriented: each entity is in one class. Create a relation for
   each class, with all the attributes for that class.
    – Don’t forget inherited attributes.
2. E/R style: an entity is in a network of classes related by isa. Create
   one relation for each E.S.
    – An entity is represented in the relation for each subclass to
        which it belongs.
    – Relation has only the attributes attached to that E.S. + key.
3. Use nulls. Create one relation for the root class or root E.S., with all
   attributes found anywhere in its network of subclasses.
    – Put NULL in attributes not relevant to a given entity.

Fall 2007                     Chris Clifton - CS541                           48

            name          Beers             manf


            color         Ales

Fall 2007           Chris Clifton - CS541          49
name       manf
                       name                       manf      color
Bud        A.B.              SummerBrew           Pete's    dark

    Beers                                   Ales
                       E/R Style
na me               man f                name               Col or
Bu d                A.B.                 SummerBrew         dark
Su mme rB re w      Pe te's
           Beers                                     Ales

                  Using NULLS
            na me                man f          co lo r
            Bu d                 A.B.           NUL L
            Su mme rB re w       Pe te 's       da rk

To top