Home / Database / ER Modeling

Entity-Relationship Modeling

Overview

Entity-Relationship (ER) Modeling is a graphical technique used for conceptual database design. It represents the logical structure of a database using entities, attributes, and relationships.

ER modeling provides a visual way to understand and document database structure before implementation, making it easier to communicate database design with stakeholders and developers.

Core Components of ER Model

1. Entity

An entity is a real-world object or concept that can be distinctly identified. Entities are represented as rectangles in ER diagrams.

Entity Examples

Examples of Entities:
- Student
- Course
- Professor
- Department
- Order
- Product
- Customer

2. Attribute

An attribute is a property or characteristic of an entity. Attributes describe entities and are represented as ovals or listed inside the entity rectangle.

Entity with Attributes

Student Entity:
- StudentID (key attribute)
- StudentName
- DateOfBirth
- Email
- PhoneNumber
- Major

3. Relationship

A relationship is an association between two or more entities. Relationships show how entities are related and are represented as diamonds in ER diagrams.

Relationship Examples

Examples:
- Student ENROLLS IN Course
- Professor TEACHES Course
- Customer PLACES Order
- Employee WORKS FOR Department

Types of Attributes

1. Simple vs Composite

Simple Attribute: Cannot be divided further (e.g., Age, Email)
Composite Attribute: Can be divided into smaller parts (e.g., Name → FirstName, LastName)

2. Single-Valued vs Multi-Valued

Single-Valued: Has only one value per entity instance (e.g., DateOfBirth)
Multi-Valued: Can have multiple values per entity instance (e.g., PhoneNumbers, EmailAddresses)

3. Stored vs Derived

Stored Attribute: Value is physically stored (e.g., DateOfBirth)
Derived Attribute: Value is calculated from other attributes (e.g., Age calculated from DateOfBirth)

4. Key Attributes

Primary Key: Uniquely identifies an entity instance (e.g., StudentID)
Candidate Key: Can potentially be a primary key

Types of Entities

1. Strong Entity

A strong entity has its own primary key and doesn't depend on other entities for existence.

Strong Entity Example

Student Entity:
- Has its own primary key (StudentID)
- Exists independently
- Not dependent on other entities

2. Weak Entity

A weak entity depends on another entity for its existence and has a partial key (discriminator).

Weak Entity Example

Dependent Entity:
- Depends on Employee entity
- Partial key: DependentName
- Full key: EmployeeID + DependentName

Relationship Cardinality

Cardinality specifies the number of instances of one entity that can be associated with instances of another entity:

One-to-One (1:1)

One instance of Entity A is related to exactly one instance of Entity B, and vice versa.

1:1 Relationship Example

Employee ──1:1── Manager
- Each employee has one manager
- Each manager manages one employee

One-to-Many (1:M)

One instance of Entity A can be related to many instances of Entity B, but each instance of B relates to only one instance of A.

1:M Relationship Example

Department ──1:M── Employee
- One department has many employees
- Each employee belongs to one department

Many-to-Many (M:N)

Many instances of Entity A can be related to many instances of Entity B, and vice versa.

M:N Relationship Example

Student ──M:N── Course
- A student can enroll in many courses
- A course can have many students
- Requires junction table in implementation

Relationship Participation

Participation indicates whether all or some entity instances participate in a relationship:

Total Participation

Every instance of an entity must participate in the relationship (represented by double line).

Total Participation

Order ──must have── OrderItem
- Every order must have at least one order item
- No order can exist without order items

Partial Participation

Some instances of an entity may not participate in the relationship (represented by single line).

Partial Participation

Customer ──may place── Order
- Some customers may not have placed orders yet
- Not all customers participate in Orders relationship

ER Modeling Process

Steps to create an ER model:

Identify entities: Determine main objects in the system
Identify attributes: Determine properties of each entity
Identify relationships: Determine how entities relate
Determine cardinality: Specify relationship types (1:1, 1:M, M:N)
Specify participation: Identify mandatory vs optional relationships
Identify keys: Determine primary keys for entities
Validate model: Check completeness and correctness

ER Diagram Notation

Common symbols in ER diagrams:

ER Diagram Symbols

Rectangle ──────── Entity
Ellipse/Oval ──── Attribute (underlined = key)
Diamond ───────── Relationship
Double Line ───── Total participation
Single Line ───── Partial participation
1 ──────────────── One (in relationship)
M, N ───────────── Many (in relationship)

Example: Complete ER Model

University Database Example:

University ER Model

Entities:
- Student (StudentID, Name, Email)
- Course (CourseID, CourseName, Credits)
- Professor (ProfessorID, Name, Department)
- Department (DeptID, DeptName)

Relationships:
- Student ENROLLS IN Course (M:N)
- Professor TEACHES Course (1:M)
- Professor BELONGS TO Department (M:1)
- Student MAJORS IN Department (M:1)

Cardinality:
- Enrollment: M:N (junction table needed)
- Teaching: 1:M (one professor, many courses)
- Department relationships: M:1 (many to one)

Converting ER Model to Relational Model

When converting ER model to tables:

Conversion Rules

1. Entity → Table
   - Entity becomes table name
   - Attributes become columns
   - Primary key identified

2. 1:1 Relationship → Foreign key in either table

3. 1:M Relationship → Foreign key in "many" table
   - Department 1:M Employee
   - Employee table gets DeptID foreign key

4. M:N Relationship → Junction table
   - Student M:N Course
   - Creates Enrollment table with StudentID and CourseID

Best Practices

Start simple: Begin with main entities and relationships
Use clear names: Make entities and relationships meaningful
Document assumptions: Note design decisions
Validate with users: Ensure model reflects requirements
Iterate and refine: ER models evolve as understanding grows
Check normalization: Ensure proper normalization when converting to tables

Common Mistakes

Confusing entities with attributes: Ensure clear distinction
Incorrect cardinality: Verify relationship types carefully
Missing relationships: Ensure all entity connections are captured
Over-complicating: Keep model as simple as possible

Next Steps

Learn about advanced modeling techniques in Advanced Modeling, or see visual representations in ERD & Crow's Foot Notation.

← Erd Diagrams Advanced Modeling →