ER Diagramming

Document Sample
ER Diagramming Powered By Docstoc
					44220: Database Design & Implementation
            ER Diagramming


                  Ian Perry
           Room: C41C   Tel Ext.: 7287
           E-mail: I.P.Perry@hull.ac.uk

  http://itsy.co.uk/ac/0809/sem2/44220_DDI/
   Conceptual Data Modelling Process
 1. Identify ALL of the relevant Entities.
     • must play a necessary role in the business
       system.
 2. Identify those Attributes that adequately
       describe each Entity.
            • remember to choose ‘key’ attribute(s).
 3. Identify the Relationships between Entities.
     • determine the Degree of each Relationship:
     • determine the Type of each Relationship.
     • attempt to decompose any many-to-many
       Relationships that you have identified.

Ian Perry          44220: Database Design & Implementation: ER Diagramming   Slide 2
   Hospital Example
  Let us suppose that you have been asked to
   build a Conceptual Data Model for a Hospital.
  The questions you must ask (and answer?) are
   as follows:
           What are the major objects of interest?
                Entities
           What details adequately describe each of these
            objects?
                Attributes
           What associations are valid between these
            objects?
                Relationships
Ian Perry           44220: Database Design & Implementation: ER Diagramming   Slide 3
   What are the Entities?
    i.e. the major objects of interest.
           Doctor
           Nurse
           Ward
           Patient
           Medication
           Operation
           Clinic
           etc.


Ian Perry        44220: Database Design & Implementation: ER Diagramming   Slide 4
   What are the Attributes?
    i.e. the details that adequately describe each
     of the Entities.
           Ward
                Name, Location, Number of Beds, …
           Patient
                Name, Address, Date-of-Birth, Gender, …
           Doctor
                Name, Room, Extension, Speciality, …
           Nurse
                Name, Room, Extension, Speciality, …
           NB.
                Perhaps ‘people’ who work in the Hospital could be
                 described using ONE Entity, i.e. Staff?
Ian Perry             44220: Database Design & Implementation: ER Diagramming   Slide 5
   Entity Definition
    Syntax:
           EntityName (key attribute(s), attribute, ...)

    For Example:
           Staff (FirstName, FamilyName, Role, Room,
            Extension, Speciality, …)
           Ward (WardName, Location, Number-of-Beds, …)
           Medication (DrugCode, Type, Dosage, …)
           Patient (FirstName, FamilyName, DOB, Gender,
            Street, Town, Post Code, Tel. No., …)

    Always Remember:
           to indicate the ‘key’ Attribute(s).

Ian Perry          44220: Database Design & Implementation: ER Diagramming   Slide 6
   ‘Type’ of Relationships?
    i.e. associations which are valid, in BOTH
     directions, between pairs of Entities.
       Patient has Operation.

                Operation performed on Patient
           Staff work in Ward
                Ward employs Staff
           Ward has beds for Patient
                Patient stays in Ward
           Patient attends Clinic
                Clinic treats Patient
           Etc.
Ian Perry            44220: Database Design & Implementation: ER Diagramming   Slide 7
   ‘Degree’ of Relationships?
    One-to-One (1:1)
           Very rare!
    One-to-Many (1:M)
           Quite rare as a ‘real world’ relationship;
            however, what we require for both Logical and
            Physical Modelling.
    Many-to-Many (M:M)
           Most common as a ‘real world’ relationship;
            however, impossible to implement Logically or
            Physically.
                MUST be decomposed into two One-to-Many
                 relationships linked by an ‘artificial’ Entity.

Ian Perry             44220: Database Design & Implementation: ER Diagramming   Slide 8
   Occurrence Diagrams?
    Use these (with values for Key Attributes)
     to discover how many occurrences of each
     Entity are actually on either side of a
     Relationship.

                 Staff                                        Ward
             Fred Smith                                       Ward 1
             Jane Bloggs                                      Ward 2
            Arthur Jones                                      Ward 3
            Angela Oust
                               M
                               1                          1
                                                          M

Ian Perry        44220: Database Design & Implementation: ER Diagramming   Slide 9
   Participation in a Relationship?
    Mandatory:
           compulsory attachment on both sides (very rare).
    Contingent:
           compulsion on one side only (most common).
    Optional:
           no compulsory attachment for either Entity (rare).
    Inclusive:
           attachment only occurs when another relationship
            exists (very rare).
    Exclusive:
           only one attachment from a set of possible
            relationships is permitted (quite rare).

Ian Perry          44220: Database Design & Implementation: ER Diagramming   Slide 10
   Degree, Type & Participation - 1

• One-to-Many, Contingent (compulsion one side)
                           1       has beds for => M
            Ward                    <= stays in
                                                              Patient


• One-to-Many, Contingent (compulsion one side)
                               1     treats =>          M
            Clinic                   <= attends
                                                                Patient

  NB. a Patient can NOT both be staying in a Ward AND
      attending the Clinic, so these two Relationships are
      also Exclusive.
Ian Perry          44220: Database Design & Implementation: ER Diagramming   Slide 11
   Degree, Type & Participation - 2

• Many-to-Many, Contingent (compulsion one side)
                              M         has =>           M
            Patient                                              Operation
                                  <= performed on




• Many-to-Many, Contingent (compulsion one side)
                              M        work in =>        M
            Staff                    <= employs
                                                                   Ward



Ian Perry           44220: Database Design & Implementation: ER Diagramming   Slide 12
   Decompose Complex Relationships
 • Can’t have any Many-to-Many Relationships;
   for example this one:
                                  M        work in =>        M
              Staff                      <= employs
                                                                       Ward

• MUST be decomposed into 2 x One-to-Many
  Relationships, linked by an ‘artificial’ Entity, i.e.:
                    1             M                          M              1
            Staff                           Team                                  Ward
                      work in =>                              <= employs

                                            FirstName
       FirstName                                                                  WardName
                                           FamilyName
      FamilyName
                                            WardName


Ian Perry               44220: Database Design & Implementation: ER Diagramming              Slide 13
   Rules for Drawing ER Diagrams
    Need to look good:
           So, DON’T draw them by hand!
    Need to be well laid out, so that:
           Entities with several Relationships are in
            the centre of the diagram.
           AND related Entities are adjacent to each
            other.
           AND Relationship lines do not cross.


Ian Perry         44220: Database Design & Implementation: ER Diagramming   Slide 14
   The final Degree, Type & Participation Diags.
                                      1   has beds for => M
               Ward                         <= stays in
                                                                        Patient

                                      1    treats =>            M
               Clinic                       <= attends
                                                                        Patient
            NB. the above Relationships are also Exclusive.

                          1           M                     M            1
             Staff                           Team                                  Ward
                           work in =>                       <= employs


                     1            M                     M                     1
     Patient                              Pat/Op                                     Operation
                         has =>                           <= performed on


i.e. having now ‘solved’ BOTH M:M Relationship ‘problems’.
Ian Perry                44220: Database Design & Implementation: ER Diagramming            Slide 15
   Plan BEFORE you begin!
    ‘Patient’ has Relationships with THREE other
     Entities:
           so, place ‘Patient’ in centre of the Diagram.
    ‘Ward’, ‘Team’ & ‘Pat/Op’ have Relationships
     with TWO other Entities:
           so, place these a little further out.
    The other 3 Entities (i.e. ‘Staff’, ‘Operation’
     & ‘Clinic’ only have ONE Relationship:
           so, they should be placed on the outside of the
            digaram, close to the Entity that they have a
            Relationship with.
    Join all of the Entities:
           indicating the Degree, Type and Participation of
            each Relationship.
Ian Perry           44220: Database Design & Implementation: ER Diagramming   Slide 16
   ER Diagram for a Hospital
                                                                                             has =>
       Clinic                                                              Ward          1            M
                                                                                                              Team
                                                              1                                                                  M




                                                                                                                                     <= work in
                              1




                                           has beds for =>
                <= attends




                                                             <= stays in
                             treats =>
                                                                                                                                 1

                                                                                                              Staff
            #==========#
                              M                               M

                              Patient                                       has =>           Pat/Op




                                                                                                               <= performed on
                                                                     1               M                    M




                                                                                                          1

                                                                                     Operation
Ian Perry                     44220: Database Design & Implementation: ER Diagramming                                                Slide 17
     This Week’s Workshop
    The purpose of this week’s Workshop it to check
     that you can create good looking ER Diagrams, using
     a suitable software tool.
           If you haven’t already got a favourite drawing
            application I suggest you use PowerPoint.
    Your ER Diagrams MUST follow the diagram
     conventions given in this lecture.
           The same is true for Assignment 1.
    Part 1 - Draw Degree, Type & Participation
     diagrams, which:
           depict 4, relatively simple, ‘real-world’ situations.
    Part 2 - Begin working on Assignment 1, by:
           identifying the Entities from the Case Study.
           then, drawing Occurrence diagrams for each Pair of
            Entities.
Ian Perry           44220: Database Design & Implementation: ER Diagramming   Slide 18

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:20
posted:7/26/2011
language:English
pages:18