Entities and Relationships

Document Sample
Entities and Relationships Powered By Docstoc
					Entities and Relationships




                Database Principles
What is a Relationship?

• An association between or among entities. Can be
  binary, ternary, quartic, etc.
• Because entities are “types” of things, relationships also
  have to be “types” of associations.
• Entities have instances and relationships have
  occurrences. Entities are while relationships happen.
• In sentences, relationships often show up as verbs.
                   Most People own Automobiles

• Is a relationship between the entities PERSON and
  AUTOMOBILE.
           Andrew Pletch owns a VW Passat with VIN: 1234567890987654321

   is an occurrence of this relationship.
                       Database Principles
How do we picture a Relationship?

                                          ?

                                       OWNS
       PERSON                                                  AUTOMOBILE




        As a line joining the related entities with a diamond in the middle


         The name of the relationship goes in or on top of the diamond

   The relationship name tends to make grammatical sense in one direction only




                           Database Principles
Just to make it clear:

                                   OWNS
      PERSON                                               AUTOMOBILE




                       This is a relationship




           This is a collection of occurrences of this relationship




                        Database Principles
Can Relationships have Properties?

• Yes!
• They are also called attributes.
• They appear in the diagram underneath the diamond.
                             OWNS
       PERSON                                  AUTOMOBILE


                                           ?

                          PurchaseDate


• Why can’t PurchaseDate be an attribute of AUTOMOBILE?
  – Reason 1: Because the same car can be sold several times and
    so have a list of PurchaseDates
  – Reason 2: Because PurchaseDate only makes sense in the
    context of “what car?” and “who bought it?”


                     Database Principles
Attributes Don’t Get Copied.

                           OWNS
      PERSON                                    AUTOMOBILE
      SSN                                       VIN
      FName                                     OwnerName
      LName                                     Make
      DOB                                       Model
                                                Year



                    NOT permitted/recommended

• The fact that a vehicle has an owner who has a name is
  modeled by the <owns> relationship.
• We do NOT replicate this fact by also putting an extra
  attribute in the VEHICLE entity.



                   Database Principles
Does a Relationship have a Key?

• Yes, but they are not documented in the relationship.
• Typically it is the combined keys of the participating
  entities.
                             OWNS
        PERSON                            AUTOMOBILE
        PersonID                          VIN




                           PurchaseDate


• The key to <owns> is the pair (PersonID,VIN).
• We do NOT duplicate these atrributes anywhere else in
  the diagram.
• No two occurrences of <owns> have the same key
  values.
                    Database Principles
Exercise:

• What does <owns> mean?
• What are some occurrences of <owns>?
• Describe a general occurrence of <owns>.
• Based on the model, can the same person <own> the
  same vehicle more than once? Why not?
• What would it mean to <own> the same vehicle more
  than once? Give a couple of examples.
• How would we change the model if this kind of multiple
  ownership needed to be modeled?




                   Database Principles
What if you CAN <own> the same car more than once?

• That’s possible!
• But you need the purchase date to tell these two events
  apart
                             OWNS
        PERSON                            AUTOMOBILE
        PersonID                          VIN




                           PurchaseDate


• Key to <owns> now: (PersonID,VIN,PurchaseDate).
• No two occurrences of <owns> have the same key
  values.
• We underscore any attribute of a relationship that is part
  of the relationship key.
                    Database Principles
Exercise:

• How do the two versions of <owns> differ?
• What are some occurrences of the new <owns>?
• Describe a general occurrence of the new <owns>.




                  Database Principles
Relationship Cardinalities:

• Read the notes:
        www.cs.newpaltz.edu/~pletcha/DB/ThreeQuestions.html




• Enterprise Rules are used by the database designer
  determine what things are entities, what things are
  relationships and what things are attributes.
      Example: Every Employee earns one and only one salary


                       EMPLOYEE

                       ID
                       .. .
                       Salary              single-valued fact about all employees
                                           so is modeled as an attribute of EMPLOYEE


                         Database Principles
Relationship Enterprise Rules:
                                 OWNS
      PERSON                                            AUTOMOBILE
      PersonID                                          VIN




                               purchase_date



• How do we model the following rules?
          Rule 1: A person can own one and only one AUTOMOBILE
          Rule 2: A person can own many automobiles acquired at any time
          Rule 3: Some people do not own automobiles at all
          Rule 4: Every automobile has at least one and possibly many owners.



• The answer seems to boil down to asking how many
  times each PERSON or each AUTOMOBILE can
  participate in the <owns> relationship.

                        Database Principles
How many vehicles can a person own?

• Rule 1 and Rule 2 on the previous slide are mutually
  exclusive but our picture doesn’t allow us to say which of
  these is the rule that applies in this case.
• That is because the picture is not complete.
• We complete the picture by adding what we call
  Participation Numbers to each entities participation in
  each relationship.




                    Database Principles
Minimum Participation Numbers:
                                    OWNS
      PERSON                                                 AUTOMOBILE
      PersonID                                               VIN

                  (0,?)                              (1,?)

                                 purchase_date



• Does every PERSON own at least one AUTOMOBILE?
                 no – min participation number (mPN) is 0    √
                 yes – min participation number (mPN) is 1


• Is every AUTOMOBILE owned by at least one
  PERSON?
                 no – min participation number (mPN) is 0
                 yes – min participation number (mPN) is 1   √



                          Database Principles
Maximum Participation Numbers:
                                   OWNS
     PERSON                                                 AUTOMOBILE
     PersonID                                               VIN

                 (0,n)                             (1,1)

                                purchase_date



• Does any PERSON own more than one AUTOMOBILE?
                no – max participation number (MPN) is 1
                yes – max participation number (MPN) is n   √

• Is any AUTOMOBILE owned by more than one
  PERSON?
                no – max participation number (MPN) is 1    √
                yes – max participation number (MPN) is n




                         Database Principles
Interpreting Participation Numbers:
                                 OWNS
      PERSON                                            AUTOMOBILE
      PersonID                                          VIN

                 (1,1)                          (0,n)

                              purchase_date


      mPN = 1:       Every PERSON <owns> at least one AUTOMOBILE

      MPN = 1:      No PERSON <owns> more than one AUTOMOBILE

      mPN = 0:      Not every AUTOMOBILE <is owned by> a PERSON

      MPN = n:      Some AUTOMOBILES <may be owned by> more than one PERSON




                         Database Principles
Participation Numbers and Keys

• Compare
                                   OWNS
        PERSON                                             AUTOMOBILE
        PersonID                                           VIN

                   (0,n)                          (1,n)

                                PurchaseDate
• and
                                   OWNS
        PERSON                                             AUTOMOBILE
        PersonID                                           VIN

                   (0,n)                           (1,n)

                                PurchaseDate


        The first lets a PERSON own multiple AUTOMOBILES but not
        the same AUTOMOBILE twice.
        The latter allows both things to happen.



                           Database Principles
An Example:


      A library keeps records of current loans of books to borrowers.

      Each borrower has a borrower# and each copy of a book has
      an accession# (there may be several copies of the same book).

      The library keeps the name and address of each borrower so
      that overdue reminders can be sent if necessary.

      For each book, the library keeps the title, authors, publisher,
      publication date, ISBN, purchase price and current list price.

      Borrowers can have one of two statuses - junior and senior.
      There are restrictions on the number of books a borrower may
      take out at one time depending on his/her status.

      Books which are out on loan may be reserved by other borrowers.

      The library does not buy paperbacks. When a new edition of a book
      is acquired, all copies of earlier editions are removed from the shelves.



                         Database Principles
 List all Nouns:

                                                                  Library
A library keeps records of current loans of books to borrowers.   Loan
                                                                  Book
Each borrower has a borrower# and each copy of a book has         Borrower
an accession# (there may be several copies of the same book).     Borrowerid
                                                                  Copy
The library keeps the name and address of each borrower so        Accession_no
that overdue reminders can be sent if necessary.                  B_name
                                                                  B_address
For each book, the library keeps the title, authors, publisher,   Reminder
publication date, ISBN, purchase price and current list price.    Title
                                                                  Author
Borrowers can have one of two statuses - junior and senior.       Publisher
There is a loan limit on the number of books a borrower may       Pub_date
take out at one time depending on his/her status.                 ISBN
                                                                  P_price
Books which are out on loan may be reserved by other borrowers.   C_price
                                                                  B_status
When a book is returned the person who made the earliest          Loan_limit
Reservation for he book is notified.                              Reservation



                                  Database Principles
Which Nouns are Entities and Which are Attributes?

   Library
   Loan               Why is Author not an entity?
   Book                 (i) It certainly is a property of Book.
   Borrower            (ii) All we know about authors are their name.
   Borrowerid         (iii) Authors do nothing but author books.
   Copy               (iv) In a Publishing House database Authors
   Accession_no              would be entities.
   B_name              (v) In a Library database, Authors only exist as
   B_address                 authors of Books so do not have “independent”
   Reminder                  existence.
   Title
   Author
   Publisher
   Pub_date
   ISBN
   P_price
   C_price
   B_status
   Loan_limit
   Reservation




                  Database Principles
List Entities and their Attributes:

   Loan
     l_date         -- how else do we know if it is overdue
   Book
     ISBN
     Author                      Why is Library dropped altogether?
     Title                         (i) Although certainly an entity, there is only one instance.
     Pub_date                     (ii) All other entities have multiple instances.
     C_price                     (iii) No properties exist for Library other than its name.
   Borrower                      (iv) If our application was a library system with many
     Borrowerid                        libraries then it would make sense to make Library
     B_name                            an entity.
     B_address
     B_status
     Loan_limit
   Publisher
     Pub_name
   Reminder
   Copy
     Accession_no
     P_price
   Reservation
     R_date      -- how else do we send a notice to the earliest reservation

                             Database Principles
First Look at a List of Entities:



                           Reservation

                           R_date
                                              Book       Publisher
                                          ISBN          Pub_name
            Borrower                      Author
           Borrowerid                     Title
           B_name                         Pub_date
           B_addr                         C_price
           B_status
           Loan_limit                                   NOTES:
                                                        1: Things that have keys
                           LOAN
                                                        that belong to them are
                                                        likely to be entities.
                           l_date
                                              Copy      2: Nouns that are missing
            Reminder                     Accession_no   keys and seem to relate to
                                         P_price        other things are likely to be
                                                        relationships and not entities.




                        Database Principles
Now Add Some Relationships



                      Reserves
                                         Book           Publisher
                                      ISBN             Pub_name
         Borrower                     Author
        Borrowerid                    Title
        B_name
                       r_date         Pub_date
        B_addr                        C_price
        B_status                                   pub_date??
        Loan_limit
                       Borrows




             ?                          Copy           1: Reminder is more of
                        l_date      Accession_no       a transaction or activity
        Reminder
                                    P_price            than a thing. All the info
                                                       needed for a Reminder
                                                       is found in other things.

                                                       2: Pub_name can be an
                                                       attribute of Book if we drop
                                                       the Publisher entity

                     Database Principles
Final Version (without mMpn):




                              Reserves
                                                 Book
                                              ISBN
               Borrower                       Author
                                              Title
              Borrowerid                      Pub_name
              B_name
                               r_date
                                              Pub_date
              B_addr                          C_price
              B_status
              Loan_limit
                               Borrows



                                                Copy

                                l_date      Accession_no
                                            P_price




                      Database Principles
Final Version (with mMpn)
                                                            Does every Cardholder reserve a book? (n=0)
                                                            Does any Cardholder reserve more than
                                                             one book? y=n
                     Reserves                               Is every Book reserved by at least one
                                              Book
                                                             Cardholder? (n=0)
             (0,n)               (0,n)     ISBN             Is any Book reserved by more than one
Cardholder                                 Author
                                           Title             Cardholder? (y=n)
Borrowerid
                      r_date               Pub_name         Does every Cardholder borrow a book? (n=0)
B_name                                     Pub_date
B_addr                                                      Does any Cardholder borrow more than
                                           C_price
B_status                                                     one book?        (y=n)
Loan_limit
                     Borrows                        (1,n)   Is every Copy borrowed by at least one
             (0,n)                                           Cardholder?        (n=0)
                                                            Is any Cardholder borrowed by more than one
                                                    (1,1)
                                                             Cardholder? (n=1) [current loans only]
                                             Copy
                                                            Does every Book exist as a copy in the
                      l_date             Accession_no
                                (0,1) P_price                 Library?       (y=1)
                                                            Does any Book have more than one copy
                                                             in the Library?       (y=n)
                                                            Is every Copy a copy of some Book?      (y=1)
                                                            Is any Copy a copy of more than one
                                                             Book?        (n=1)
1: Cardholder not
Borrower (more accurate)


                                  Database Principles
A Practical Approach to ER Design

• Read the “Ten Steps to Database Design” web page. It
  shows how to complete the task of preparing an External
  View ER diagram in a systematic fashion.

      http://www.cs.newpaltz.edu/~pletcha/DB/TenSteps2DBDesign.html




                        Database Principles
More on Participation Numbers:

• Some relationships are ternary: Consider vaccines
  produced by different manufacturers and sold in different
  countries.
                  VACCINE                        MANUFACTURER

                  V_ID                           Man_ID

                            distributed by, in




                               COUNTRY

                               Name


• The problem is, how to ask the PN questions.

                   Database Principles
Asking the Right Question:
 VACCINE                        MANUFACTURER
                       (1,n)
                        (c,d)
 V_ID      (a,b)
           (1,n)                Man_ID

           distributed by, in

                                      a? : Does every VACCINE participate at least once in
                                           the <distributed by, in> relationship? (n=0, y=1)
                     (1,n)
                    (e,f)             b? : Does any VACCINE participate more than once in
              COUNTRY                      the <distributed by, in> relationship? (n=1, y=n)

              Name
                                      c? : Does every MANUFACTURER <distribute> at least
                                           one VACCINE in some COUNTRY?
                                      d? : Does any MANUFACTURER <distribute> more than
                                           one VACCINE in some COUNTRY?

                                      e? : Is every COUNTRY the recipient of at least
                                           one VACCINE manufactured by someone?
                                      f? : Is any COUNTRY the recipient of more than
                                           one VACCINE manufactured by someone?



                                Database Principles
A Class Example: Part A:

   An Airline has planes that are used to fly scheduled flights.

   Flight segments are between two airports – departure and arrival. They
    are scheduled to depart at a certain time and arrive at a certain time. The
    type of plane to be used is also known.

   Scheduled flights are a sequence of flight segments.

   Actual flights occur on particular dates using a particular plane flown
    by a particular pilot and co-pilot. They take off at a given time (perhaps)
    different from the scheduled departure time and have an ETA.

   Pilots have names, SSNs, addresses, DOB.

   Planes have unique numbers, capacities – both1st class and economy

   This database doesn’t deal with passengers.

                         Database Principles
A Class Example: Part B:


  An Airline reservation system keeps information about customers,
   their reservations, tickets, seat assignments for scheduled flights.

  Scheduled flights are between two airports – departure and arrival. They
   are scheduled to depart at a certain time and arrive at a certain time. The
   type of plane to be used is also known. Scheduled flights have flight
   numbers.

  Customer info includes name, address, email address, phone number

  A reservation is for a certain customer on a sequence of scheduled flights. It
   has a locator number. Each reservation has a single source and single
   destination airport.

  A ticket is a paid reservation. It has a ticket number and a payment method
  Including credit card info

                          Database Principles
Weak Entities

• An entity is weak if it depends on another entity for part
  of its key.
• Remember, we can’t copy keys around an ER diagram.
• We can’t do the following
                                                                     Line
                                   Order
                                            (1,n)            (1,1) OrderNum
                                 OrderNum
                                                                   LineNum



                                                    Not allowed



• But because the key to an Order Line is really the
  combination of (OrderNumber,LineNumber), Line
  instances depend on the key to Order for part of their
  key.


                    Database Principles
Weak Entities (cont):

• Instead we draw a double line around the weaker
  (dependent) entity and the relationship that it depends
  on.

                Order                             Line
                           (1,n)        (1,1)
              OrderNum                          LineNum



• This picture says that Line is a weak entity whose key
  consists of the pair (OrderNumber, LineNumber).
• Weak entities always have a (1,1) participation number
  pair linking to the entity they depend upon. Why?



                        Database Principles
Weak Entity – Another Example

• Some times a relationship from one department’s point a
  view is an entity in some other department.
• For the Registrar’s Office, Enrollment as a relationship
  between two entities – Student and Course:
                                                        enrolls
                      Student                                                     Course
                                        (1,n)                       (1,n)
                  StudentNum                                                 CourseNum



• For the Bursar’s Office, Enrollment is an entity that relates
  to a Chargeable Item.
                                     charges_for         ChargeableItem
                Enrollment                                                                  StudentAccount
                                                         ItemNum
               StudentNum    (1,1)              (1,1)    AmountOwed       (1,1)        (1,n) AccountNum
               CourseNum                                 AmountPaid




• Why are <enrolls> and Enrollment are the same thing???

                                Database Principles
How to merge the two diagrams?
                       enrolls
                                                                                                                   charges_for       ChargeableItem
                                                                                           Enrollment                                                              StudentAccount
    Student                                    Course                                                                                ItemNum
                                                                                          StudentNum                         (1,1)   AmountOwed       (1,1)   (1,n) AccountNum
               (1,n)              (1,n)                                                   CourseNum
                                                                                                           (1,1)
                                                                                                                                     AmountPaid
  StudentNum                                 CourseNum




                                                                                                                                               NOTE: We converted a
  become                                                                                                                                       relationship into a weak-
                                                                                                                                               entity relationship
Registrar’s View                                        enrolls

                                   Student                                    Course
                                              (1,n)                 (1,n)
                                 StudentNum                                 CourseNum




                                                      (1,1)


                                                       Enrollment
                                                      Grade                            Bursar’s View
                                                      (1,1)



                                                              charges_for           ChargeableItem
                                                                                                                                 StudentAccount
                                                                                    ItemNum
                                                                            (1,1)   AmountOwed          (1,1)             (1,n) AccountNum
                                                                                    AmountPaid




                                               Database Principles
A New Model for an Old Idea

• The previous example used to be called aggregation. It
  used to be modeled as:
             Enrollment

                                        enrolls

                   Student                                  Course
                              (1,n)               (1,n)
                 StudentNum                               CourseNum




                                      (1,1)



                                              charges_for         ChargeableItem
                                                                                                StudentAccount
                                                                  ItemNum
                                                          (1,1)   AmountOwed       (1,1)   (1,n) AccountNum
                                                                  AmountPaid




• The box labeled Enrollment turned the <enrolls>
  relationship into an entity with the same key.
• We don’t use this method any more because most
  modeling tools don’t support it.

                                 Database Principles
IS_A

• Before inheritance became popular because of OOP
  there was IS_A.
                                              Person
                                              ID
                                              Name
                                              DOB
                                      (0,1)            (0,1)
                               is_a                            is_a



                  (1,1)                                                  (1,1)
                  Professor                                           Student

                OfficeNumber                                          GPA



• Both Professor and Student possess the attributes ID,
  Name and DOB by “inheritance”.
• The key to Professor and the key to Student is the key
  to Person.
                          Database Principles
Modeling History:

• The <borrows> relationship in the Library model only
  models current loans and not past loans.
                                     borrows

           Cardholder                                     Copy

           borrowerid   (0,n)                   (0,1) accession_no




• This is clear for two reasons:
   – The same Copy can not be loaned more than once
      • Reason?
   – The same Cardholder can not borrow the same Copy
     more than once.
      • Reason?

                                Database Principles
Modeling History 2:

• Suppose we wanted to model a complete loan history of
  all loans; past and present.
• To allow more than one loan of the same copy we would
  first need to change the copy Max PN to n.
• To allow the same Cardholder to borrow the same Copy
  more than once (but on different occasions) we need to
  add time to the key. To show that this is part of the key to
  <borrows> and not just another attribute we underline it.

                                          has_borrowed

                     Cardholder                                    Copy

                     borrowerid   (0,n)                  (0,n) accession_no

                                              l_date
                                                                a copy can be loaned
       added to the key                                         out more than once

                             Database Principles
Self-Related Entities:

• Some entities are related to themselves.
                  manager
                      (0,n)     manages
                     Person
           Role
                     SSN

                     (0,1)
                   worker




• This models what kind of a data structure?




                    Database Principles
More on Participation Numbers:

• Participation Numbers are concise representations of
  Enterprise Rules.
• A database designer, viewing mMPNs, can make
  positive declarations about the rules by which a business
  is run.
• Participation numbers are flexible enough (16 different
  ways to model a binary relationship) to handle all we
  need to do.




                   Database Principles

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:2
posted:11/26/2012
language:Unknown
pages:40