ER Diagrams

An Entity-Relationship (ER) Diagram is a visual representation of the entities, attributes, and relationships in a database design. It is the primary tool...

What is an ER Diagram?

An Entity-Relationship (ER) Diagram is a visual representation of the entities, attributes, and relationships in a database design. It is the primary tool used during the conceptual design phase — before any tables are created, before any SQL is written, the database designer draws an ER diagram to model the real-world scenario.

ER diagrams serve as a communication tool between database designers and stakeholders (clients, managers, domain experts). They are intuitive enough for non-technical people to understand, yet precise enough to guide the implementation of actual database tables.

Types of Attributes in ER Diagrams

Attribute Type	Notation	Example
Simple (Atomic)	Regular oval	Name, Age, Salary
Composite	Oval with sub-ovals connected	Name → (FirstName, LastName)
Multi-valued	Double-bordered oval	PhoneNumbers (a person can have many)
Derived	Dashed-border oval	Age (derived from DateOfBirth)
Key attribute	Underlined text in oval	StudentID (uniquely identifies entity)

Drawing a Complete ER Diagram: Step-by-Step Example

Let us design an ER diagram for a University Course Registration System.

Step 1: Identify Entities

From the requirements, we identify the following entities:

Student — individuals enrolled at the university
Course — subjects offered for study
Instructor — faculty members who teach courses
Department — organizational units within the university

Step 2: Identify Attributes for Each Entity

Student	StudentID (PK), FirstName, LastName, Email, DateOfBirth, GPA
Course	CourseID (PK), CourseName, Credits, Syllabus
Instructor	InstructorID (PK), Name, Qualification, JoinDate
Department	DeptID (PK), DeptName, Building, Budget

Step 3: Identify Relationships and Cardinality

Step 4: Identify Relationship Attributes

The "enrolls" relationship between Student and Course has its own attributes:

Grade (earned by a specific student in a specific course)
Semester (when the enrollment happened)
EnrollmentDate

These attributes belong to the relationship itself, not to either entity independently.

Step 5: Draw the Complete Diagram

Example

(DeptName) (Building) (Budget) | | | ┌───────────────────────────┐ │ DEPARTMENT │ └────────────┬───────────────┘ │ 1 │ ◇ belongs_to ◇ │ │ N ┌────────────┴───────────────┐ (Qualification)───│ INSTRUCTOR │───(Name) └────────────┬───────────────┘ │ 1 │ ◇ teaches ◇ │ │ N (Credits)─────────┌────────────┴───────────────┐───(CourseName) │ COURSE │ └────────────┬───────────────┘ │ M │ ◇ enrolls ◇───(Grade)───(Semester) │ │ N ┌────────────┴───────────────┐ (Email)───────────│ STUDENT │───(GPA) └───────────────────────────┘ | | (StudentID) (FirstName, LastName) [underlined] [composite]

Cardinality Notation Styles

Different textbooks use different notations for cardinality. The most common are:

Style	One	Many	Example
Chen notation	1	N or M	1:N placed near the line
Crow's foot	Single line	Fork (crow's foot)	Line ending indicates cardinality
(min,max) notation	(1,1)	(0,N)	Minimum and maximum participation

Crow's Foot Notation Example: DEPARTMENT ────────────┤├── belongs_to ──┤┤──────── INSTRUCTOR (one department) (many instructors) ──┤├── means "one" (single vertical bar) ──┤┤── means "many" (crow's foot) ──○┤── means "zero or one" (optional)

Participation Constraints

Total participation (mandatory): Every entity instance must participate in the relationship. Drawn with a double line.

Partial participation (optional): Some entity instances may not participate. Drawn with a single line.

Every EMPLOYEE must work in a DEPARTMENT	total participation (double line)
Not every DEPARTMENT needs a manager	partial participation (single line)
(total	every employee must work somewhere)
(partial	not every employee manages a department)

Common Mistakes in ER Diagrams

Mistake	Problem	Correction
Multi-valued attribute where entity needed	Cannot represent complex structures	Create a separate entity (e.g., Phone entity)
Relationship between more than two entities when not needed	Overly complex diagram	Most relationships are binary; ternary only when necessary
Missing cardinality	Ambiguous design	Always specify cardinality constraints
Derived attribute stored as regular	Wastes space, may cause inconsistency	Mark with dashed border, compute at query time
Forgetting weak entity notation	Loss of dependency information	Use double border and identifying relationship

From ER Diagram to Relational Schema

The ER diagram is not the final product — it must be converted to relational tables for implementation:

Each strong entity becomes a table with its attributes as columns
Each weak entity becomes a table with the owner's PK included
Each M:N relationship becomes a separate junction table
Each 1:N relationship adds a foreign key to the "many" side
Multi-valued attributes become separate tables
Composite attributes are flattened into their simple components

This mapping process is systematic and follows well-defined rules, making the ER diagram a reliable blueprint for database implementation.

What is an ER Diagram?

Types of Attributes in ER Diagrams

Attribute Type	Notation	Example
Simple (Atomic)	Regular oval	Name, Age, Salary
Composite	Oval with sub-ovals connected	Name → (FirstName, LastName)
Multi-valued	Double-bordered oval	PhoneNumbers (a person can have many)
Derived	Dashed-border oval	Age (derived from DateOfBirth)
Key attribute	Underlined text in oval	StudentID (uniquely identifies entity)

Drawing a Complete ER Diagram: Step-by-Step Example

Let us design an ER diagram for a University Course Registration System.

Step 1: Identify Entities

From the requirements, we identify the following entities:

Student — individuals enrolled at the university
Course — subjects offered for study
Instructor — faculty members who teach courses
Department — organizational units within the university

Step 2: Identify Attributes for Each Entity

Student	StudentID (PK), FirstName, LastName, Email, DateOfBirth, GPA
Course	CourseID (PK), CourseName, Credits, Syllabus
Instructor	InstructorID (PK), Name, Qualification, JoinDate
Department	DeptID (PK), DeptName, Building, Budget

Step 3: Identify Relationships and Cardinality

Step 4: Identify Relationship Attributes

The "enrolls" relationship between Student and Course has its own attributes:

Grade (earned by a specific student in a specific course)
Semester (when the enrollment happened)
EnrollmentDate

These attributes belong to the relationship itself, not to either entity independently.

Step 5: Draw the Complete Diagram

Example

Cardinality Notation Styles

Different textbooks use different notations for cardinality. The most common are:

Style	One	Many	Example
Chen notation	1	N or M	1:N placed near the line
Crow's foot	Single line	Fork (crow's foot)	Line ending indicates cardinality
(min,max) notation	(1,1)	(0,N)	Minimum and maximum participation

Participation Constraints

Total participation (mandatory): Every entity instance must participate in the relationship. Drawn with a double line.

Partial participation (optional): Some entity instances may not participate. Drawn with a single line.

Every EMPLOYEE must work in a DEPARTMENT	total participation (double line)
Not every DEPARTMENT needs a manager	partial participation (single line)
(total	every employee must work somewhere)
(partial	not every employee manages a department)

Common Mistakes in ER Diagrams

Mistake	Problem	Correction
Multi-valued attribute where entity needed	Cannot represent complex structures	Create a separate entity (e.g., Phone entity)
Relationship between more than two entities when not needed	Overly complex diagram	Most relationships are binary; ternary only when necessary
Missing cardinality	Ambiguous design	Always specify cardinality constraints
Derived attribute stored as regular	Wastes space, may cause inconsistency	Mark with dashed border, compute at query time
Forgetting weak entity notation	Loss of dependency information	Use double border and identifying relationship

From ER Diagram to Relational Schema

The ER diagram is not the final product — it must be converted to relational tables for implementation:

Each strong entity becomes a table with its attributes as columns
Each weak entity becomes a table with the owner's PK included
Each M:N relationship becomes a separate junction table
Each 1:N relationship adds a foreign key to the "many" side
Multi-valued attributes become separate tables
Composite attributes are flattened into their simple components

This mapping process is systematic and follows well-defined rules, making the ER diagram a reliable blueprint for database implementation.

ER Diagrams

What is an ER Diagram?

Types of Attributes in ER Diagrams

Drawing a Complete ER Diagram: Step-by-Step Example

Step 1: Identify Entities

Step 2: Identify Attributes for Each Entity

Step 3: Identify Relationships and Cardinality

Step 4: Identify Relationship Attributes

Step 5: Draw the Complete Diagram

Cardinality Notation Styles

Participation Constraints

Common Mistakes in ER Diagrams

From ER Diagram to Relational Schema

Study next in this module

ER Diagrams

What is an ER Diagram?

Types of Attributes in ER Diagrams

Drawing a Complete ER Diagram: Step-by-Step Example

Step 1: Identify Entities

Step 2: Identify Attributes for Each Entity

Step 3: Identify Relationships and Cardinality

Step 4: Identify Relationship Attributes

Step 5: Draw the Complete Diagram

Cardinality Notation Styles

Participation Constraints

Common Mistakes in ER Diagrams

From ER Diagram to Relational Schema

Study next in this module