Entity Relationship (ER) Modeling_4

Entity Relationship Modeling

Entity Relationship Model (ERM)

• The foundation of an Entity Relationship Diagram (ERD).

• Represents the conceptual database from the end-users’ perspective.

• ERDs illustrate essential components of a database: entities, attributes, and relationships.

Attributes in ERM

Required and Optional Attributes

• Required Attribute: Must have a value when the instance is added; null not allowed.

• Optional Attribute: Can be left empty when the instance is added.

Domains

• Set of possible values for an attribute, expressed as:

  • Numeric & date: (minimum, maximum)

  • Categorical: (value1, value2, …, valueN)

• Examples:

  • Sex (M, F)

  • QPA (0, 4)

  • Years of service (0, 30)

• Attributes may share the same domain.

Identifiers (Primary Keys)

• Composed of one or more attributes to distinguish instances of an entity.

• Mapped to primary keys in tables and underlined in the ERD.

• Composite Identifiers: Primary key with more than one attribute.

Attribute Types

Composite and Simple Attributes

• Composite Attribute: Can be subdivided into several attributes.

• Simple Attribute: Cannot be subdivided into new attributes.

Single-valued and Multivalued Attributes

• Single-valued Attributes: Can have one value only (e.g., social security number, student ID).

  • Not necessarily a simple attribute.

• Multivalued Attributes: Can have multiple values and are indicated with a double line in Chen ERM.

Handling Multivalued Attributes

• Despite being allowed in conceptual models, do not implement in RDBMS.

• Options for resolution:a. Create several new attributes within the original entity for the multivalued attribute.b. Create a new entity for the components of the original multivalued attribute.

Derived Attributes

• Attributes whose values are calculated from other attributes and may not need storage in the database.

• Representation: Dashed line connecting the entity and attribute in Chen notation.

• Sometimes referred to as computed attributes.

• Storage decision depends on processing requirements and application constraints.

Stored vs. Derived Attributes

Stored Attributes (Advantages & Disadvantages)

• Advantages: Saves CPU processing cycles; readily available data values.

• Disadvantages: Consumes storage space.

Derived Attributes (Advantages & Disadvantages)

• Advantages: Can track historical data.

• Disadvantages: Requires constant updates for accuracy; computative complexity may slow queries.

Relationships in ERM

Relationship Names

• Names are often active or passive verbs that describe interactions between entities.

Connectivity and Cardinality

• Cardinality: Represents the minimum and maximum counts of entity occurrences linked to a related entity.

• Indicated in ERDs by numbers in the format (x, y).

• Database management systems implement cardinalities at the application level, not table level.

Types of Relationships

Weak (Non-identifying) Relationships

• Occurs when the primary key of the related entity does not include a primary key component from the parent entity.

• Example: COURSE and CLASS entities showing weak relationships.

Strong (Identifying) Relationships

• Occurs when the primary key of the related entity includes a primary key component from the parent entity.

• Critical for loading order of tables.

Entity Types

Weak Entities

• Depend on another entity for existence and typically lack their own identifier.

• Often represented with a partial identifier in an E-R diagram.

Strong Entities

• Exist independently and usually represent primary entities in a model.

Relationship Participation

Optional Participation

• An entity occurrence may not require a corresponding occurrence in a relationship (minimum cardinality of 0).

Mandatory Participation

• An entity occurrence requires a corresponding occurrence in a relationship (minimum cardinality of 1).

Crow's Foot Symbols

Relationship Degree

• Indicates the number of entities associated with a relationship.

  • Unary Relationship: Association within one entity (recursive).

  • Binary Relationship: Two entities associated (most common form).

  • Ternary Relationship: Association among three entities, often decomposed into binary relationships.

Developing an ER Diagram

• Database design is iterative, not linear or sequential.

• Typical steps include:

  • Creating a detailed narrative of organizational operations.

  • Identifying business rules from the operational narrative.

  • Determining main entities and relationships from business rules.

  • Developing the initial ERD.

  • Identifying attributes and primary keys that describe entities.

  • Revising the ERD based on feedback.

Iterative Process

• Activities are repeated until agreement is reached between end users and designers on the ERD's representation of organizational functions.

End of Presentation

Source

• Rob, Peter and Coronel, Carlos. Database Systems: Design, Implementation and Management, 7th Edition. Course Technology, Thomson Learning Inc.©2007.