Entity Relationship (ER) Modeling

• Entity Relationship (ER) Modeling explained

Objectives

• Learn the main characteristics of entity relationship components.
• Learn how relationships between entities are defined, refined, and incorporated into the database design process.
• How ERD components affect database design and implementation.
• Real-world database design often requires reconciling conflicting goals.

The Entity Relationship Model (ERM)

• The ER model forms the basis of an ER diagram.
• ERD represents conceptual database as viewed by the end user.
• ERDs depict the database's main components, including entities, attributes, and relationships.

Entities

• Refers to an entity set, not a single entity occurrence.
• Corresponds to a table, not a row, in a relational environment.
• In Chen and Crow's Foot models, entities are represented by a rectangle with the entity's name inside.
• The entity name, is a noun, and should be written in capital letters.

Attributes

• Attributes are characteristics of entities.
• In Chen notation, attributes are represented by ovals connected to the entity rectangle with a line; each oval contains the name of the attribute.
• In Crow's Foot notation, attributes are written in an attribute box below the entity rectangle.
• Required Attributes: must have a value.
• Optional Attributes: may be left empty.
• Domain: set of possible values for an attribute.
• Attributes may share a domain.
• Identifiers: one or more attributes that uniquely identify each entity instance.
• Composite identifier: a primary key composed of more than one attribute.
• Composite attributes can be subdivided.
• Simple attributes cannot be subdivided.
• Single-value attributes can have only a single value.
• Multivalued attributes can have many values.
• M:N relationships and multivalued attributes should not be implemented.
• Derived attributes: values may be calculated from other attributes; need not be physically stored within the database.
• Storing Derived Attributes Advantages: saves CPU processing cycles, saves data access time and data value is readily available
• Storing Derived Attributes Disadvantages: Requires constant maintenance to ensure the derived value is current
• Not Storing Derived Attributes Advantages: Saves storage space, computation always yields current value.
• Not Storing Derived Attributes Disadvantages: Uses CPU processing cycles, Adds coding complexity to queries

Relationships

• Relationships are associations between entities.
• Participants are entities that participate in a relationship.
• Relationships between entities always operate in both directions.
• Relationships can be classified as 1:M.
• Relationship classification can be difficult to establish if only one side of the relationship is known.
• Connectivity: Describes the relationship classification.
• Cardinality: Expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity.
• These are established by concise statements, known as business rules.
• Existence dependence: Entity exists in database only when it is associated with another related entity occurrence.
• Existence independence: Entity can exist apart from one or more related entities and Such an entity is referred to as a strong or regular entity.
• Weak (non-identifying) relationships: Exists if the PK of related entity does not contain the PK component of the parent entity.
• Strong (identifying) relationships: Exists when the PK of the related entity contains the PK component of the parent entity.
• Weak entities meet two conditions: they are existence-dependent, and their primary key is partially or totally derived from the parent entity in the relationship.
• The database designer determines whether an entity is weak based on business rules.
• 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
• 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.

Recursive Relationships

• Relationships exist between occurrences of the same entity set, naturally found within a unary relationship.

Associative (Composite) Entities

• Associative (Composite) Entities are also known as bridge entities, used to implement M:N relationships and Composed of primary keys of each of the entities to be connected.
• May also contain additional attributes that play no role in connective process

Development of an ER Diagram

• Database design is an iterative process, involving creating a detailed narrative of the organization's operations, identifying business rules, determining main entities and relationships, developing an initial ERD, identifying attributes and primary keys, and revising and reviewing the ERD.

Database Design Challenges

• Database designers make design compromises with conflicting goals: design standards, processing speed, information requirements should be considered, plus meeting logical requirements and design conventions
• Design is of little value unless it delivers all specified query and reporting requirements as well as solving design and implementation problems which may not yield "clean" solutions.

Summary:

• Entity relationship (ER) model, uses ERD to represent conceptual database as viewed by end user
• ERM's main components: Entities, Relationships, and Attributes include connectivity and cardinality notations
• Connectivities and cardinalities are based on business rules, M:N relationship is valid at the conceptual level and ERDs may be based on many different ERMs
• UML class diagrams are used to represent the static data structures in a data model
• Database designers are often forced to make design compromises