Entity Relationship (ER) Modeling
• Forms the basis of an entity relationship diagram (ERD), Conceptual database as viewed by end user, Database’s main components, Entities, Attributes, Relationships
Entities
• Object of interest to the end user, Refers to the entity set and not to a single entity occurrence, ERM corresponds to a table—not to a row—in the relational environment, ERM refers to a table row as an entity instance or entity occurrence, In Chen, Crow’s Foot, and UML notations, an entity is represented by a
rectangle that contains the entity’s name, The entity name, a noun, is usually written in all capital letters
Attributes (1 of 7)
• Characteristics of entities
• Required attribute: must have a value and cannot be left empty
• Optional attribute: does not require a value and can be left empty
• Domain: set of possible values for a given attribute
• Identifier: one or more attributes that uniquely identify each entity instance
• Composite identifier: primary key composed of more than one attribute
• Composite attribute: attribute that can be subdivided to yield additional
attributes
• Simple attribute: attribute that cannot be subdivided
• Single-valued attribute: attribute that has only a single value
• Multivalued attributes: attributes that have many values
Attributes (4 of 7)
•Requirements of multivalued attributes, Create several new attributes, one for each component of the original
multivalued attribute, Develop a new entity composed of the original multivalued attribute’s
components, Derived attribute: attribute whose value is calculated from other attributes, Derived using an algorithm
Relationships, Connectivity, and Cardinality
• Association between entities that always operate in both directions
• Participants: entities that participate in a relationship
• Connectivity: describes the relationship classification
• Include 1:1, 1:M, and M:N
• Cardinality: expresses the minimum and maximum number of entity
occurrences associated with one occurrence of related entity
• In the ERD, cardinality is indicated by placing the appropriate numbers beside
the entities, using the format (x, y)
Existence Dependence
• Existence dependence, Entity exists in the database only when it is associated with another related
entity occurrence, Existence independence, Entity exists apart from all of its related entities, Referred to as a strong entity or regular entity
Relationship Strength
• Weak (non-identifying) relationship, Primary key of the related entity does not contain a primary key component of
the parent entity, Strong (identifying) relationships, Primary key of the related entity contains a primary key component of the
parent entity
Weak Entities (1 of 3)
• Conditions of a weak entity, Existence-dependent, Has a primary key that is partially or totally derived from parent entity in the
relationship, Database designer determines whether an entity is weak, Based on business rules
Relationship Participation (1 of 3)
• Optional participation, One entity occurrence does not require a corresponding entity occurrence in a
particular relationship, Mandatory participation, One entity occurrence requires a corresponding entity occurrence in a
particular relationship
Relationship Degree (1 of 2)
• Indicates the number of entities or participants associated with a
relationship
• Unary relationship: association is maintained within a single entity
• Binary relationship: two entities are associated
• Ternary relationship: three entities are associated
• Recursive relationship: relationship exists within a single entity type
Recursive Relationships (1 of 2)
• Relationship can exist between occurrences of the same entity set, Naturally, such a condition is found within a unary relationship
– Common in manufacturing industries, One common pitfall when working with unary relationships is to confuse
participation with referential integrity, Similar because they are both implemented through constraints on the same
set of attributes
Associative (Composite) Entities (1 of 2)
• Used to represent an M:N relationship between two or more entities, Has a 1:M relationship with the parent entities, Composed of the primary key attributes of each parent entity, May also contain additional attributes that play no role in connective
process
Database Design Challenges: Conflicting Goals (1 of 2)
• Database designers must often make design compromises that are
triggered by conflicting goals, Database design must conform to design standards, High processing speed may limit the number and complexity of logically
desirable relationships
• Maximum information generation may lead to loss of clean design structures
and high transaction speed
• The ERM uses ERDs to represent the conceptual database as viewed by
the end user
• Connectivity describes the relationship classification (1:1, 1:M, or M:N)
• In the ERM, an M:N relationship is valid at the conceptual level
• ERDs may be based on many different ERMs
• Unified Modeling Language (UML) class diagrams are used to represent
the static data structures in a data model
• Database designers, no matter how well they can produce designs that
conform to all applicable modeling conventions, are often forced to make
design compromises