Docstoc

Entity Relationship ER Modeling (PDF)

Document Sample
Entity Relationship ER Modeling (PDF) Powered By Docstoc
					                                          4




             Chapter 4

 Entity Relationship (ER) Modeling


           Database Systems:
Design, Implementation, and Management,
    Seventh Edition, Rob and Coronel

                                          1
                                                                                         4

            In this chapter, you will learn:

• The main characteristics of entity relationship
  components
• How relationships between entities are defined and
  refined and how those relationships are incorporated
  into the database design process
• How ERD components affect database design and
  implementation
• That real-world database design often requires the
  reconciliation of conflicting goals

    Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   2
                                                                                        4

   The Entity Relationship (ER) Model

• ER model forms the basis of an ER diagram
• ERD represents conceptual database as
  viewed by end user
• ERDs depict database’s main components:
  – Entities
  – Attributes
  – Relationships


   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   3
                                                                                         4

                                          Entities

• Refers to entity set and not to single entity
  occurrence
• Corresponds to table and not to row in relational
  environment
• In both Chen and Crow’s Foot models, entity is
  represented by rectangle containing entity’s
  name
• Entity name, a noun, is usually written in capital
  letters
    Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   4
                                                                                         4

                                       Attributes

• Characteristics of entities
• In Chen model, attributes are represented by
  ovals and are connected to entity rectangle with
  a line
• Each oval contains the name of attribute it
  represents
• In Crow’s Foot model, attributes are written in
  attribute box below entity rectangle

    Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   5
                                                                                     4

                   Attributes (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   6
                                                                                        4

                                       Domains

• Attributes have domain
  – Domain is attribute’s set of possible values
• Attributes may share a domain




   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   7
                                                                                        4

                 Identifiers (Primary Keys)

• Underlined in the ERD
• Key attributes are also underlined in
  frequently used table structure shorthand




   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   8
                                                                                        4

                 Composite Primary Keys

• Primary keys ideally composed of only single
  attribute
• Possible to use a composite key
  – Primary key composed of more than one
    attribute




   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   9
                                                                                     4
Composite Primary Keys (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   10
                                                                                        4

      Composite and Simple Attributes

• Composite attribute can be subdivided
• Simple attribute cannot be subdivided




   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   11
                                                                                        4

                  Single-Valued Attributes

• Single-value attribute can have only a single
  value




   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   12
                                                                                        4

                      Multivalued Attributes

• Multivalued attributes can have many values




   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   13
                                                                                     4

   Multivalued Attributes (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   14
                                                                                        4

Resolving Multivalued Attribute Problems

• Although conceptual model can handle M:N
  relationships and multivalued attributes, you
  should not implement them in relational DBMS
   – Within original entity, create several new
     attributes, one for each of the original multivalued
     attribute’s components
        • Can lead to major structural problems in table
   – Create new entity composed of original
     multivalued attribute’s components


   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   15
                                                                                       4
Resolving Multivalued Attribute Problems
              (continued)




  Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   16
                                                                                       4
Resolving Multivalued Attribute Problems
              (continued)




  Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   17
                                                                                       4
Resolving Multivalued Attribute Problems
              (continued)




  Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   18
                                                                                        4

                           Derived Attributes

• Attribute whose value may be calculated
  (derived) from other attributes
• Need not be physically stored within
  database
• Can be derived by using an algorithm




   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   19
                                                                                     4
       Derived Attributes (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   20
                                                                                     4
       Derived Attributes (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   21
                                                                                         4

                                  Relationships

• Association between entities
• Participants are entities that participate in a
  relationship
• Relationships between entities always operate in
  both directions
• Relationship can be classified as 1:M
• Relationship classification is difficult to establish if
  know only one side of the relationship

    Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   22
                                                                                        4

             Connectivity and Cardinality

• Connectivity
  – Used to describe the relationship classification
• Cardinality
  – Expresses minimum and maximum number of
    entity occurrences associated with one
    occurrence of related entity
• Established by very concise statements
  known as business rules
   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   23
                                                                                       4

Connectivity and Cardinality (continued)




  Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   24
                                                                                        4

                   Existence Dependence

• Existence dependence
  – Exist in database only when it is associated
    with another related entity occurrence
• Existence independence
  – Entity can exist apart from one or more related
    entities
  – Sometimes refers to such an entity as strong
    or regular entity


   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   25
                                                                                        4

                      Relationship Strength

• Weak (non-identifying) relationships
  – Exists if PK of related entity does not contain
    PK component of parent entity
• Strong (Identifying) Relationships
  – Exists when PK of related entity contains PK
    component of parent entity




   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   26
                                                                                     4

Weak (Non-Identifying) Relationships




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   27
                                                                                     4
              Weak (Non-Identifying)
             Relationships (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   28
                                                                                     4

   Strong (Identifying) Relationships




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   29
                                                                                        4

                                Weak Entities

• Weak entity meets two conditions
  – Existence-dependent
        • Cannot exist without entity with which it has a
          relationship
  – Has primary key that is partially or totally derived
    from parent entity in relationship
• Database designer usually determines whether
  an entity can be described as weak based on
  business rules

   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   30
                                                                                     4
             Weak Entities (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   31
                                                                                     4

             Weak Entities (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   32
                                                                                        4

                Relationship Participation

• Optional participation
  – One entity occurrence does not require
    corresponding entity occurrence in particular
    relationship
• Mandatory participation
  – One entity occurrence requires corresponding
    entity occurrence in particular relationship



   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   33
                                                                                      4

Relationship Participation (continued)




 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   34
                                                                                     4
Relationship Participation (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   35
                                                                                     4
Relationship Participation (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   36
                                                                                     4
Relationship Participation (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   37
                                                                                        4

                       Relationship Degree

• Indicates number of entities or participants
  associated with a relationship
• Unary relationship
  – Association is maintained within single entity
• Binary relationship
  – Two entities are associated
• Ternary relationship
  – Three entities are associated

   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   38
                                                                                     4
  Relationship Degree (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   39
                                                                                     4
    Relationship Degree (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   40
                                                                                        4

                  Recursive Relationships

• Relationship can exist between occurrences
  of the same entity set
• Naturally found within unary relationship




   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   41
                                                                                     4
               Recursive Relationships
                    (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   42
                                                                                     4
               Recursive Relationships
                    (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   43
                                                                                     4
               Recursive Relationships
                    (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   44
                                                                                     4
               Recursive Relationships
                    (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   45
                                                                                     4
               Recursive Relationships
                    (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   46
                                                                                     4
               Recursive Relationships
                    (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   47
                                                                                        4

                         Composite Entities

• Also known as bridge entities
• Composed of primary keys of each of the
  entities to be connected
• May also contain additional attributes that
  play no role in connective process




   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   48
                                                                                     4

      Composite Entities (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   49
                                                                                     4
      Composite Entities (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   50
                                                                                     4

      Composite Entities (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   51
                                                                                        4

              Developing an ER Diagram

• Database design is iterative rather than linear
  or sequential process
• Iterative process
  – Based on repetition of processes and
    procedures




   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   52
                                                                                         4

 Developing an ER Diagram (continued)
• Building an ERD usually involves the following
  activities:
   – Create detailed narrative of organization’s description
     of operations
   – Identify business rules based on description of
     operations
   – Identify main entities and relationships from business
     rules
   – Develop initial ERD
   – Identify attributes and primary keys that adequately
     describe entities
   – Revise and review ERD
    Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   53
                                                                                         4

 Developing an ER Diagram (continued)

• Tiny College
   – Tiny College is divided into several schools
         • Each school is composed of several departments
   – Each department may offer courses
   – Each department may have many professors assigned to
     it
   – Each professor may teach up to four classes; each class
     is section of course
   – Student may enroll in several classes, but (s)he takes
     each class only once during any given enrollment period

    Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   54
                                                                                         4

 Developing an ER Diagram (continued)

• Tiny College (continued)
   – Each department has several students
         • Each student has only a single major and is associated with
           a single department
   – Each student has an advisor in his or her department
         • Each advisor counsels several students
   – The relationship between class is taught in a room and
     the room in the building




    Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   55
                                                                                      4

Developing an ER Diagram (continued)




 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   56
                                                                                      4

Developing an ER Diagram (continued)




 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   57
                                                                                      4

Developing an ER Diagram (continued)




 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   58
                                                                                      4

Developing an ER Diagram (continued)




 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   59
                                                                                      4

Developing an ER Diagram (continued)




 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   60
                                                                                      4

Developing an ER Diagram (continued)




 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   61
                                                                                      4

Developing an ER Diagram (continued)




 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   62
                                                                                      4

Developing an ER Diagram (continued)




 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   63
                                                                                      4

Developing an ER Diagram (continued)




 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   64
                                                                                      4

Developing an ER Diagram (continued)




 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   65
                                                                                        4
Developing an ER Diagram (continued)




   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   66
                                                                                        4
          Database Design Challenges:
               Conflicting Goals
• Database design must conform to design
  standards
• High processing speeds are often a top
  priority in database design
• Quest for timely information might be focus of
  database design



   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   67
                                                                                     4
       Database Design Challenges:
       Conflicting Goals (continued)




Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   68
                                                                                        4

                                      Summary

• Entity relationship (ER) model
  – Uses ERD to represent conceptual database
    as viewed by end user
  – ERM’s main components:
        • Entities
        • Relationships
        • Attributes
  – Includes connectivity and cardinality notations


   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   69
                                                                                        4

                      Summary (continued)

• Connectivities and cardinalities are based on
  business rules
• In ERM, M:N relationship is valid at
  conceptual level
• ERDs may be based on many different ERMs
• Database designers are often forced to make
  design compromises

   Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel   70

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:275
posted:3/8/2010
language:English
pages:70