Entity Relationship Model by dffhrtcv3


									Entity Relationship Modeling
Database Design
   Requirements analysis
   Conceptual database design
   Logical database design
   Schema refinement
   Physical database design
   Security design
   Tuning
               Marina G. Erechtchoukova   2
Conceptual Model
   Global view of data
   Basis for identification and description
    of main data items
   Hardware and software independent
   The result of conceptual design

                Marina G. Erechtchoukova       3
The Entity Relationship (E-R)
   Main Components
       Entities
            Attributes
       Relationships
            Descriptive attributes
   ERD – visual representation of E-R

                          Marina G. Erechtchoukova   4
   are objects (physical or conceptual) in
    the real world, which are
    distinguishable from other objects
   Entity occurrence (entity instance)
   Entity sets:
       Attributes
       An instance of an entity set

                       Marina G. Erechtchoukova   5
Attribute Values

   Domain
   Atomic (simple) vs. composite
   Single-valued vs. multi-valued
   Derived

              Marina G. Erechtchoukova   6

           Marina G. Erechtchoukova   7
   Composite key – a key containing
    more than one attribute
   A candidate key – irreducible subset
    of attributes whose values uniquely
    identify an entity in the set
   A primary key
   An alternative key

               Marina G. Erechtchoukova    8
   Are associations among two or more
   Relationship set
   Instance of a relationship set
   Descriptive Attributes
   A relationship is uniquely identified by
    participating entities

                   Marina G. Erechtchoukova    9
Degree of a relationship set
   Indicates number of associated
    entity sets
   Binary
   Ternary
   Quaternary …
   Unary - Recursive relationships

              Marina G. Erechtchoukova   10
   Connectivity – the number of possible
    occurrences of an entity set that may
    relate to a single occurrence of an
    associated entity set through a
    particular relationship:
       One-to-one
       One-to-many
       Many-to-many

                  Marina G. Erechtchoukova   11
Cardinality of a Relationship Set
   Describes the maximum number of
    possible relationship occurrences for
    an entity set participating in a given
    relationship set
   Assigns a specific value to

                Marina G. Erechtchoukova     12
Connectivity for Complex
   Complex relationships are relationships
    of degree 3 or higher
   Connectivity – the number of possible
    occurrences of an entity set in an n-ary
    relationship when the other (n-1) values
    of participants are fixed

               Marina G. Erechtchoukova   13
Connectivity and Cardinality in
an ERD

          Marina G. Erechtchoukova   14
Constraints (continued…)
   One-to-many (Key constraint ): Each
    entity from an instance of entity set E
    appears in at most one relationship in a
    correspondent instance of the relationship
    set R
   Participation constraints:
       Mandatory (total) participation
       Optional (partial) participation

                     Marina G. Erechtchoukova    15
Weak Entities
   Existence dependence
   Doesn’t have a primary key
   A partial key - some attributes of the weak
   Identifying owner – an entity which
    determines the existence of the weak entity

                 Marina G. Erechtchoukova     16
Identifying Relationships
   A relationship between weak entity and
    identifying owner
       One-to-many (one owner to one or more weak
       Mandatory participation of weak entity set
   Primary key of a weak entity set is the
    primary key of identifying owner along with
    partial key of weak entity set

                   Marina G. Erechtchoukova          17
Relationship Strength
   Weak (non-identifying)
       One entity is existence-independent on another
       PK of related entity doesn’t contain PK
        component of another entity
   Strong (identifying)
       A relationship between weak entity set and
        identifying owner
       PK of weak entity contains PK component of
        identifying owner entity

                    Marina G. Erechtchoukova         18
Composite Entity Sets
   Used to ‘bridge’ between M:N

               Marina G. Erechtchoukova   19
Generalization Hierarchy
   ISA hierarchy
   Supertype versus subtype
   Inheritance
   Two ways to view a hierarchy:
       Specialization
       Generalization

                   Marina G. Erechtchoukova   20
Generalization Hierarchy (cont…)
   Overlapping constraints
       Disjoint (non-overlapping) subtypes use a ‘d’
        (‘G’) symbol
       Overlapping subtypes use a ‘o’ (‘Gs’) symbol
   Completeness (Covering) constraints
       Total
       Partial
       No symbol is available

                     Marina G. Erechtchoukova           21
Generalization Hierarchy (cont…)
   Reasons to use Generalization (ISA)
       To add extra attributes to a part of
        entities from a set
       To specify a relationship with a subset of
        an entity set

                   Marina G. Erechtchoukova      22
 Erratum (Sixth ed.)

Supertype/Subtype Relationship in an ERD

                                          Figure 4.57 (3.42)

               Marina G. Erechtchoukova                        23
Comparison of ERD Symbols

         Marina G. Erechtchoukova   24
Additional ERD Symbols for
Chen Notation
 A_name                Attribute
 A_name                Primary Key
 A_name               Derived Attribute

  A_name               Multi-Valued Attribute

    R_name              Strong Relationship Set

             Marina G. Erechtchoukova             25
Conceptual Database Design
   Entity versus entity attribute
       need to record a set of values
   Entity versus relationship attribute
       need to record a set of values
   Binary versus ternary relationships

                   Marina G. Erechtchoukova   26
      Conceptual Database Design
   Business rule – brief and precise description
    of a policy, procedure, or principle within a
    specific organization’s environment
   ERD must reflect business rules
                 Main point:
      If we cannot express all necessary
    business rules with a current model, the
             model must be revised

                      Marina G. Erechtchoukova      27
Conceptual design: Tips
   1:1 relationships may indicate a
    mistake in conceptual model
   Eliminate composite attributes
   Eliminate multi-valued attributes
   Eliminate derived attributes

                Marina G. Erechtchoukova   28
Developing an ERD
   Iterative Process
       Step1: General narrative of organizational
        operations is developed
       Step2: Basic E-R Model is depicted and reviewed
       Step3: Modifications are made to incorporate
        newly discovered E-R components
   Repeat process until designers and users
    agree E-R Diagram complete

                      Marina G. Erechtchoukova            29
Challenge of Database Design:
Conflicting Goals
   Database must be designed to conform to
    design standards
   High-speed processing may require design
   Other concerns
       Security
       Performance
       Shared access
       Integrity

                    Marina G. Erechtchoukova   31

To top