Entity Relationship Model - PowerPoint

Document Sample
Entity Relationship Model - PowerPoint Powered By Docstoc
					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
Alternative Terminology
Formal Term       Alternative 1              Alternative 2

External Schema   Subschema                  External model
Logical Schema    Conceptual                 Internal model
                  Schema
Physical Schema                 -            Physical model




                  Marina G. Erechtchoukova                    3
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       4
The Entity Relationship (E-R)
Model
   Main Components
       Entities
            Attributes
       Relationships
            Descriptive attributes
   ERD – visual representation of E-R
    Model


                          Marina G. Erechtchoukova   5
Entities
   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   6
Attribute Values

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


              Marina G. Erechtchoukova   7
Erratum (Seventh ed.)
      composite




           Marina G. Erechtchoukova   8
Keys
   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    9
Relationships
   Are associations among two or more
    entities
   Relationship set
   Instance of a relationship set
   Descriptive Attributes
   A relationship is uniquely identified by
    participating entities

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

              Marina G. Erechtchoukova   11
Constraints
   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   12
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
    connectivity



                Marina G. Erechtchoukova     13
Connectivity for Complex
Relationships
   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   14
Connectivity and Cardinality in
an ERD




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



                 Marina G. Erechtchoukova     17
Identifying Relationships
   A relationship between weak entity and
    identifying owner
       One-to-many (one owner to one or more weak
        entities)
       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          18
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         19
Composite Entity Sets
   Used to ‘bridge’ between M:N
    relationships




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



                   Marina G. Erechtchoukova   21
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           22
Generalization Hierarchy (cont…)
   Reasons to use Generalization (ISA)
    hierarchy:
       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      23
 Erratum (Sixth ed.)

Supertype/Subtype Relationship in an ERD




                                          Figure 4.57 (3.42)




               Marina G. Erechtchoukova                        24
Comparison of ERD Symbols




         Marina G. Erechtchoukova   25
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             26
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   27
      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      28
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   29
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            30
Challenge of Database Design:
Conflicting Goals
   Database must be designed to conform to
    design standards
   High-speed processing may require design
    compromises
   Other concerns
       Security
       Performance
       Shared access
       Integrity


                    Marina G. Erechtchoukova   32

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:21
posted:9/11/2012
language:Unknown
pages:31