Entity-relationship model20104713313 by sdfwerte


									                        Lecture plan
• Outline of DB design process
• Entity-relationship model
     – Entities and attributes
     – Relationships

Jane Reid, BSc/IT DB,                  1
QMUL, 7/1/02
                DB design process [1]
• Requirements collection and analysis
     – Users’ data requirements
     – Users’ functional requirements (using software
       engineering tools, e.g. data flow diagrams)

Jane Reid, BSc/IT DB,                                   2
QMUL, 7/1/02
                DB design process [2]
• Conceptual design
     – Creation of conceptual schema
           • Concise description of users’ data requirements
           • Uses high-level conceptual data model, e.g. Entity-
             relationship model
     – Data model operations used to specify user
       operations from functional analysis
     – Compatibility check and possible modification

Jane Reid, BSc/IT DB,                                              3
QMUL, 7/1/02
                DB design process [3]
• Logical design / data model mapping
     – Uses implementation data model, e.g. relational
       data model
     – Conceptual schema transformed from high-
       level data model to implementation data model

Jane Reid, BSc/IT DB,                                4
QMUL, 7/1/02
                DB design process [4]
• Physical design
     – Internal storage structures, access paths, file
       organisation specified
     – Application programs designed and

Jane Reid, BSc/IT DB,                                    5
QMUL, 7/1/02
           Entity-relationship model
• High-level conceptual modelling technique
  for DB applications
• DB application = DB + associated programs
• Application programs need to be designed,
  implemented and tested
• ER model used for conceptual design of DB

Jane Reid, BSc/IT DB,                     6
QMUL, 7/1/02
                 Entities and attributes
• An entity is an object with a physical or
  conceptual existence
• Attributes are the particular properties
  which describe the entity
• An individual attribute has a value

Jane Reid, BSc/IT DB,                         7
QMUL, 7/1/02
    Simple vs composite attributes
• Composite attributes
     – Can be divided into smaller subparts which
           • Represent more basic attributes with independent
           • Can form a hierarchy
     – Useful if attribute must be handled sometimes
       as a unit and sometimes as components
• Simple (atomic) attributes are not divisible
Jane Reid, BSc/IT DB,                                           8
QMUL, 7/1/02
Single- vs multi-valued attributes
• Single-valued attributes have a single value
  for a particular entity
• Multi-valued attributes
     – Have a set of values for a particular entity
     – May have lower/upper bounds on the number
       of values allowed

Jane Reid, BSc/IT DB,                                 9
QMUL, 7/1/02
         Stored vs derived attributes
• Derived attributes
     – Are related to another attribute
     – Are derivable from a stored attribute
     – May be derivable from related entities
• Stored attributes
     – Are not derivable from any other source

Jane Reid, BSc/IT DB,                            10
QMUL, 7/1/02
                   Null attribute values
• Used to denote value of an attribute for a
  particular entity which is
     – Non-applicable
     – Unknown
           • Exists but is missing
           • Not known if it exists or not

Jane Reid, BSc/IT DB,                          11
QMUL, 7/1/02
                        Complex attributes
• Composite attributes
     – Components grouped between parentheses ()
     – Components separated by commas
• Multi-valued attributes
     – Displayed between braces {}
• Composite and multi-valued attributes can
  be nested in an arbitrary way

Jane Reid, BSc/IT DB,                              12
QMUL, 7/1/02
              Entity types and sets [1]
• An entity type is
     – A collection (set) of entities with the same
       attributes but different values for the attributes
     – Schema / intension for a set of entities which
       share the same structure

Jane Reid, BSc/IT DB,                                       13
QMUL, 7/1/02
              Entity types and sets [2]
• An entity set
     – A collection of all entities for a particular entity
     – Extension of the entity type
• Entity type and associated entity set have
  the same name

Jane Reid, BSc/IT DB,                                     14
QMUL, 7/1/02
              Entity types and sets [3]
                         Name, Age, Salary
                        (John Smith, 55, 80K)
                        (Fred Brown, 40, 30K)
                         (Judy Clark, 25, 20K)
Jane Reid, BSc/IT DB,                            15
QMUL, 7/1/02
                        Key attributes [1]
• Also called uniqueness constraint
• Value of key attribute
     – Identifies each entity uniquely
     – Must be distinct for each entity in the collection
     – Unique for every extension of the entity type

Jane Reid, BSc/IT DB,                                   16
QMUL, 7/1/02
                        Key attributes [2]
• Composite key
     – Combination of attribute values form the key
     – Must be minimal (no superfluous attributes)
• Entity type may have
     – More than one key attribute
     – No key attribute - a weak entity type

Jane Reid, BSc/IT DB,                                 17
QMUL, 7/1/02
               Value sets of attributes
• Also called “domain” of attributes
• The set of values which may be assigned to
  that attribute for each individual entity
• Not displayed in ER diagrams

Jane Reid, BSc/IT DB,                      18
QMUL, 7/1/02
• Implicit relationships between attributes can
  be converted to explicit relationships
• An attribute of one entity type refers to
  another entity type

Jane Reid, BSc/IT DB,                         19
QMUL, 7/1/02
         Relationship types and sets
• Relationship type defines set of associations
  (relationship set) among entities
• Relationship type and set have same name
• Relationship instance associates exactly one
  entity from each participating entity type

Jane Reid, BSc/IT DB,                         20
QMUL, 7/1/02
                    Relationship degree
• Number of participating entity types:
     – Relationship type of degree two is binary
     – Relationship type of degree three is ternary
     – Higher degree relationships are more complex

Jane Reid, BSc/IT DB,                                 21
QMUL, 7/1/02
           Relationships as attributes
• Two entity types A and B, where
     – Attribute a2 of entity type A indicates a link to a
       particular entity of entity type B
• Binary relationship may be considered as:
     – Value set of attribute a2 is set of all B entities
     – Additional multi-valued attribute b3 for entity
       type B, whose value for each entity is the set of
       all A entities matching on attribute a2
Jane Reid, BSc/IT DB,                                    22
QMUL, 7/1/02
                        Role names
• Explains what the relationship means
• Indicates the role that a particular entity
  plays in a relationship instance
• Necessary for recursive relationships
     – same entity type participates more than once in
       a relationship type in different roles

Jane Reid, BSc/IT DB,                                23
QMUL, 7/1/02
 Relationship type constraints [1]
• Structural constraints which limit
  combination of entities in relationship set
     – Cardinality ratio
           • Number of relationship instances an entity can
             participate in
           • Possible ratios - 1:1, 1:N, N:1, M:N

Jane Reid, BSc/IT DB,                                         24
QMUL, 7/1/02
 Relationship type constraints [2]
     – Participation constraints
           • Indicates if existence of an entity depends on its
             being related to another entity by relationship type
           • Total (existence dependency) - every entity in the
             total set must be related
           • Partial - some entities in the total set must be related

Jane Reid, BSc/IT DB,                                               25
QMUL, 7/1/02
         Relationship type attributes
• Similar idea to entity type attributes
• Attributes of 1:1 relationship types can be
  migrated to one of participating entities
• Attributes of 1:N (or N:1) relationship types
  can be migrated to entity type at N side
• Attributes of M:N relationship types cannot
  be migrated

Jane Reid, BSc/IT DB,                         26
QMUL, 7/1/02
                 Weak entity types [1]
• Does not have a key attribute
• Identified by a combination of
     – Relationship to specific entities from another
       entity type (identifying/owner entity type)
           • Identifying relationship of weak entity type
           • Weak entity has total participation constraint
     – Some (or all) of its attribute values

Jane Reid, BSc/IT DB,                                         27
QMUL, 7/1/02
                 Weak entity types [2]
• Normally has partial key (discriminator)
• Can sometimes be represented as complex
  (composite, multi-valued) attribute
     – Not if it participates independently in other
       relationship types

Jane Reid, BSc/IT DB,                                  28
QMUL, 7/1/02
                 ER conceptual design
• Schema design is iterative and may involve
     – Attribute may be refined into relationship if
       attribute is a reference to another entity type
     – Attribute that exists in several entity types may
       be refined into an independent entity type
     – Inverse refinement to above
     – Specialisation/generalisation
Jane Reid, BSc/IT DB,                                  29
QMUL, 7/1/02

To top