Week 6: Entity Relationship Model

Entity Relationship (ER) Model

• Represents a conceptual database as viewed by the end user
• ERD depicts database's main components: Entities, Attributes, and Relationships
• ER model forms the basis of an ER diagram

Entity and Attributes

• Entity: Refers to an entity set (table/relation) and not to a single entity
• Attribute: Characteristics of entities
• Required Attribute: Must have a value
• Optional Attribute: May be left empty
• Domain: Set of possible values for an attribute
• Identifiers (Primary Keys): One or more attributes that uniquely identify each entity instance
• Composite Identifier: Primary key composed of more than one attribute

Attributes

• Relational Schema: Shorthand notation of a table structure
• Composite Attribute: Can be subdivided (e.g., Address -> Street, City, State)
• Simple Attribute: Cannot be subdivided (e.g., Age)
• Single-value Attribute: Can have only a single value (e.g., ID)
• Multivalued Attribute: Can have many values (e.g., EMP_DEGREE)
• Derived Attribute: Value may be calculated from other attributes

Relationships

• Association between entities
• Participants: Entities that participate in a relationship
• Connectivity: Describes how entities are related to one another
• Cardinality: Expresses minimum and maximum number of entity occurrences associated with one occurrence of the related entity
• Classification: Describes the relationship

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

Weak Entities

• Meets two conditions: Existence-dependent and Primary key partially or totally derived from parent entity in the relationship
• Determined by business rules

Relationship Participation

• Optional Participation: One entity occurrence does not require corresponding entity occurrence in a particular relationship
• Mandatory Participation: One entity occurrence requires corresponding entity occurrence in a particular relationship

Relationship Degree

• Indicates 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 or more entities are associated

Recursive Relationships

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

Associative (Composite/Bridge) Entities

• Used to implement M:N relationships
• Composed of primary keys of each of the entities to be connected
• May also contain additional attributes that play no role in the connective process

Developing an ER Diagram

• Database design is an iterative process
• Create a detailed narrative of the organization's description of operations
• Identify business rules based on the description of operations
• Identify main entities and relationships from business rules
• Develop an initial ERD
• Identify attributes and primary keys that adequately describe entities
• Revise and review ERD

This quiz covers the concepts of the Entity Relationship (ER) Model, including ERD, entities, attributes, and relationships. It also touches on Chen's and Crow foot notation.