Docstoc

ER

Document Sample
ER Powered By Docstoc
					Entity-Relationship Data Model


            Reading: Chapter 2
  Slides adapted from material by Profs.
   Jeff Ullman (Stanford) and Art Keller
                 (UCSC)
       Entity/Relationship Model
Diagrams to represent designs.
• Entity like object, = “thing.”
• Entity set like class = set of “similar”
  entities/objects.
• 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
                   Relationships
• 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
  sets.
• 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.)
  suffice.
• However, there are some
  cases where three or
  more E.S. must be
  connected by one         Students              Taking   Courses
  relationship.
• Example: relationship
  among students, courses,
  TA's (and graders).                                      Assisting

    Possibly, this E/R
    diagram is OK:
                                                          TA/Graders
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
                       Courses



Students     Enrolls


                         TAs

  Students   Courses   TAs
  Ann        CS541     Li
  Sue        CS541     Li
  Bob        CS541     Chris
  …          …         …
                   Beers-Bars-Drinkers
                        Example
• 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.”
           Example:
 Drinkers Have Favorite Beers
              name    addr      license



  Serves              Bars                 Frequents




                     Likes
  Beers                                    Drinkers

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

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
                     price




         Bars       Sells      Beers

• Shorthand for 3-way relationship:
                     price


                    Prices



         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
    possibility.
  – 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
    beer.
           Converting Multiway to 2-Way
• Baroque in E/R, but necessary in certain “object-oriented”
  models.
• 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
  others.
                            BBP

                 The-              The-              The-
                 Bar               Beer              Price


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

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
  not.
  – No way to say “symmetric” in E/R.
Design Question
Should we replace husband and wife by
 one relationship spouse?
                  Subclasses
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
  relation.
        name        Beers    manf




                     isa



        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


                               Grape
                               Beers
                   Problems
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
  meanings.
• 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
                              Keys
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
                    Example
• Suppose name is key for Beers.
           name           Beers           manf




                           isa



           color           Ales

• Beer name is also key for ales.
  – In general, key at root is key for all.
                       Example:
                   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
  such.


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)
  essential.
     – 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
  relationship.

Fall 2007                  Chris Clifton - CS541                    26
                Example: Logins (Email
                    Addresses)
Login name = user name + host name, e.g., ark@soe.ucsc.edu.
• 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.,
  ark.
• 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
  domain.
• Key for host = its name + key of secondary domain = its
  name + name of secondary domain + name of primary
  domain.
Fall 2007               Chris Clifton - CS541                28
                                              BBP

                        The-                  The-       The-
                        Bar                   Beer       Price
      All
 “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
  ThePrice.
• 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
                                         Shipment
             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
                    Ordered
Part-of is                                                 ID
many-many and                 Part of        Shipment
not a weak        Qty
relationship!
                Shipped
                   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.
Example
            name                  name   addr
Good:

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



                      Beers         Manf
                                    addr


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



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

Example
• 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
  “larger.”

               name                           name
Wrong:

              Beers         ManfBy           Manfs



Right:                    name   manf


                             Beers
            Intuitive Rule for E.S. Vs.
                     Attribute
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
                            Example
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
      relationships.
•     However, in practice, we almost always create unique
      ID's to compensate: social-security numbers, VIN's,
      etc.
•     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
        studios.


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
  relation.
            Entity Set  Relation
E.S. attributes become relational attributes.
                         name     manf


                           Beers
Becomes:
     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
  attributes.
• Example: If name is a key for Beers:
     Beers(name, manf)

Fall 2007          Chris Clifton - CS541       43
               E/R Relationships 
                    Relations
Relation has attribute for key attributes of
  each E.S. that participates in the
  relationship.
• 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
       Example
                    Logins       @          Hosts
Hosts(hostName)
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
                       Example

            name          Beers             manf




                            isa



            color         Ales


Fall 2007           Chris Clifton - CS541          49
name       manf
                      OO-Style
                       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
                               Beers

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:34
posted:10/9/2011
language:English
pages:47