Conceptual Data Modeling, E-R Diagrams by davebusters

VIEWS: 42 PAGES: 32

									Conceptual Data Modeling,
E-R Diagrams




                            1
Importance of Conceptual Data
Modeling
 Data rather than processes are more complex in
  many modern information systems.

 Characteristics of data (structure, properties) are
  more stable, i.e. less likely to change over time,
  easier to reach consensus on.

 It is shared between many processes, therefore is
  crucial in the design of databases, ensuring integrity
  of the data in an information system, efficiency of
  processing.



                                                           2
Outline
 Purpose and importance of
  conceptual data modeling
 Entity-Relationship Model
   Entity
     Attributes
   Relationships




                              3
 An Entity
 Something of interest in the environment (e.g.,
  person, place, object, event, concept)
 Represented in E-R diagram by a rectangle
 An instance is a particular occurrence of an
  entity

                                    0010
                                    Scott George
                                    56 Neat Street
 CUSTOMER                           Boulder, Colorado
                                    35882-2799
                                    507-293-8749

                                                         4
    Entity, or Entity Type   An Instance of the Customer Entity
 Entities

 Entity Type - a collection of entity
  instances that share common properties
  (also simply called an Entity)
 Entity Instance - an individual occurrence
  of an entity type




                                         5
Example Entity & Instances

        Identifier Attribute

 Cust_ID    Last_Name   First_Name      Address       City      ST    Zip

 0001      Snerd        Mortimer     General Delivery Tampa     FL   33647
 0002      Fogg         Bob          567 Fogg Lane Omaha        NE   32405
 0003      Amos         Famous       2 Cookie Ct.     Miami     FL   33133
 0004      Targa        Maxine       67 Fast Lane     Clinton   NJ   20082
 0005      George       Scott        56 Neat St.      Boulder   CO    35882
 0006      Guy           Nice        290 Pleasant St. Tampa     FL   33641
 0007      Smith         Bob         76 Quaker Path Wynn        NY    21118
 0009      Smith        James         234 Bayview     Tampa     FL    33641




                                                                              6
Basic E-R Model Constructs and
notation
                       Entity
                         Attribute




                       Relationship

                                      7
Sample E-R Diagram (figure 3-1)




                                  8
What Should an Entity Be?
SHOULD BE:
  An object that is important to business
  An object that will have many instances
   in the database
  An object that will be composed of
   multiple attributes
SHOULD NOT BE:
  A user of the database system(unless
   system keeps track of users)
  An output of the database system (e.g.
   a report)
                                             9
Business Rules
 Policies and rules about the operation of a
  business that a data model represents
   Govern how data is stored and handled.
   E.g. “a section of a course has between 15 and
    35 students”
 Must be expressed in terms familiar to end
  users, clear and concise.
 Not all business rules are related to data



                                                     10
Figure 3-4     Inappropriate entities




 System user                                 System output




                                   Appropriate entities


                                                          11
An Attribute

 A discrete data element
 A characteristic (property) of an entity
                            CUSTOMER
                            Customer_Number
                            Last_Name
                            First_Name
                            Street_Address
   This Customer entity    City
                            State
   has eight attributes
                            Zip
                            Phone

                                              12
  Types of Attributes
 Simple vs. Composite
   Simple - most basic level
   Composite – decomposable into a group of related
    attributes
     ex: address (street, city, state, zip)
 Single Valued vs. Multi Valued –
   Single - only one value per entity instance (e.g., last
    name, date of birth)
   Mulitvalued- multiple values per entity instance (e.g.,
    degrees, clubs, skills)
 Stored vs Derived        (e.g. DateOfBirth vs Age)


                                                          13
Attributes on ERDs

 May be shown on ERDs as ellipses



     emp-id                        pt-num


   PHYSICIAN       Admits   0      PATIENTS


  name   address
                            name       ward

                                              14
Attributes on ERDs

 Multivalued attributes are shown as double
  ellipses

 Composite attributes may be shown broken
  down into their simple components
     Simple/Single Valued; Primary Key


Multivalued                              composite
                           emp-id
                                                     f_name

          skills        EMPLOYEE              name        m_name

                                                     l_name        15
  Textbook’s notation

     An attribute
     broken into
   component parts




    Multivalued
an employee can have      Derived
  more than one skill    from date
                         employed
                        and current
   16                          16
                            date
 Identifiers/Primary Key

 Every instance of an entity must be uniquely
  identified (to unambiguously distinguish them)
 An identifier can be one or more attributes
  called a composite identifier (e.g., first name, middle
  name, and last name)
 Partial identifier (in weak entities) – attribute
  that together with some attribute from another
  entity identifies an instance
 Underline identifiers in diagrams                  17
 Identifiers

 Must be unique
 Should not change value over time
 Guaranteed to have a valid value
 No intelligent identifiers (e.g. containing
  locations or people that might change)
 Consider substituting
  single-attribute                       CUSTOMER
  identifiers for               Customer_Number

  composite identifiers
                                Last_Name
                                First_Name
  to simplify design and       Address
  enhance performance          City
                               State
                               Zip                  18
                               Phone
Relationships

 A relationship is an association
  between one or more entities
 The degree of a relationship indicates
  the number of entities involved
 The cardinality of a relationship
  describes the number of instances of
  one entity associated with another
  entity
                                       19
Figure 3-10 Relationship types and instances



a) Relationship



     b)
Relationship
 instances




 20
Cardinality Constraints



  A patient history is         A patient must have
 recorded for one and         recorded at least one
   only one patient           history, and can have
                                       many
0   Optional relationships        Mandatory relationships


    0           none or one                     one and only one

                                                  one or more
    0          none or more
                                                            21
 Cardinality Constraints

Cardinality Constraints - the number of
 instances of one entity that can or must
 be associated with each instance of
 another entity.
Minimum Cardinality
   If zero, then optional
   If one or more, then mandatory
Maximum Cardinality
   The maximum number
                                        22
  Degrees of Relationships: unary and
  binary
The number of different entities involved in a relationship



        EMPLOYEE                     Manages     UNARY



                                               BINARY

         STUDENT           Is assigned         DORMITORY
                                                           23
Degrees of Relationships -
ternary




  A vendor supplies parts to warehouses. The unit cost
  and delivery method may differ for every warehouse.

 Note: a relationship can have attributes of its own
                                                         24
 More on Relationships
 Relationships (many-to-many or one-to-one) can have
  attributes
    These describe features pertaining to the association between the entities in
     the relationship




 Two entities can have more than one type of relationship
  between them (multiple relationships)
 Associative Entity = combination of relationship and entity
 Some typical cases
                                                                           25
Examples of multiple relationships – entities can be
related to one another in more than one way
Figure 3-21a Employees and departments




                                                       26
Time stamping




                27
Entities can be related to one another in
more than one way




Figure 3-21a Employees and departments
                                         28
 Strong vs. Weak Entities, and
 Identifying Relationships

 Strong entities
   exist independently of other types of entities
   has its own unique identifier
   represented with single-line rectangle
 Weak entity
   dependent on a strong entity…cannot exist on its
    own
   Does not have a unique identifier
   represented with double-line rectangle
 Identifying relationship
   links strong entities to weak entities
   represented with double line diamond
                                                       29
Associative Entities
 Associative entities provide details of a many-to-many
  association.
 It’s an   entity   – it has attributes

 AND it’s a  relationship       – it links entities together
 When should a relationship with attributes instead be an
  associative entity? Guidelines:
     All relationships for the associative entity should be many
     The associative entity could have meaning independent of the
      other entities
     The associative may be participating in other relationships other
      than the entities of the associated relationship
     The associative entity preferably has a unique identifier, and
      should also have other attributes.
     If an associative entity may have a partial identifier.
     Ternary relationships should be converted to associative entities



                                                                          30
An associative entity                 (CERTIFICATE)
(Fig. 3-11b)




Associative entity is depicted as a rectangle with a diamond
inside.



                                                               31
Modeling a ternary relationship as
an associative entity




                                     32

								
To top