ER Model Entity Relationship Diagram: Tutorial, Examples, FAQs & Interview Tips

Entity-Relationship Model in DBMS

The Entity-Relationship Model, commonly called the ER model, is a high-level conceptual data model used to design a database before creating tables. It represents real-world objects as entities, their properties as attributes, and associations among them as relationships.

The ER model was proposed by Peter Chen in 1976. It is mainly used during the database design phase because it helps users, analysts, and database designers understand the structure of the system without worrying about physical storage or SQL implementation details.

Simple idea: If you are designing a college database, you first identify objects such as Student, Course, Teacher, and Department. Then you identify their properties and how they are connected. That conceptual design is an ER model.

Why ER Model is Needed

A database should not be created directly from random tables. First, the real-world requirements must be understood clearly. The ER model provides a visual and logical way to capture those requirements.

It helps understand real-world data before implementation.
It improves communication between users, analysts, developers, and DBAs.
It reduces design mistakes by showing entities, attributes, and relationships clearly.
It helps identify primary keys, weak entities, cardinality, and participation constraints.
It acts as a bridge between requirements analysis and relational schema design.
It provides a foundation for normalization and table creation.

Main Components of ER Model

Component	Meaning	Example
Entity	A real-world object or concept that can be identified separately.	Student, Course, Employee, Customer, Product.
Attribute	A property or characteristic of an entity or relationship.	Student name, roll number, email, salary.
Relationship	An association between two or more entities.	Student enrolls in Course, Customer places Order.
Constraint	A rule that controls valid data or valid relationships.	Every student must have a unique roll number.

Entity

An entity is a real-world object, person, place, event, or concept about which data is stored. An entity must be distinguishable from other objects.

Examples of entities include Student, Teacher, Course, Book, Department, Bank Account, and Order.

Domain	Possible Entities
College System	Student, Course, Teacher, Department, Exam, Enrollment.
Banking System	Customer, Account, Branch, Loan, Transaction.
Ecommerce System	Customer, Product, Order, Payment, Seller, Shipment.
Hospital System	Patient, Doctor, Appointment, Ward, Prescription.

Entity Type and Entity Set

Entity type describes a group or category of similar entities. Entity set is the collection of all entities of a particular entity type at a given time.

Term	Meaning	Example
Entity Type	The structure or definition of similar entities.	STUDENT with attributes RollNo, Name, Email.
Entity	One individual object of an entity type.	Student with RollNo 101 and Name Asha.
Entity Set	Collection of all entities of the same type.	All students currently stored in the college database.

Strong Entity and Weak Entity

Entities can be classified as strong or weak depending on whether they can be identified independently.

Entity Type	Meaning	Key	Example
Strong Entity	An entity that has its own primary key and can exist independently.	Has a primary key.	Student, Department, Customer, Product.
Weak Entity	An entity that cannot be identified by its own attributes alone and depends on an owner entity.	Uses a partial key plus owner's key.	Dependent of Employee, Order_Item of Order.

Example: An Order_Item may have item number 1, 2, or 3, but item number alone is not unique across all orders. It becomes unique only with Order_ID. So Order_Item is a weak entity.

Attribute

An attribute is a property that describes an entity or relationship. For example, a Student entity may have attributes such as RollNo, Name, Email, DateOfBirth, PhoneNumber, and Address.

Attributes are important because they later become columns in relational tables.

Types of Attributes

Attribute Type	Description	Example	ER Notation
Simple Attribute	Cannot be divided into smaller meaningful parts.	Age, Gender, Salary.	Single ellipse.
Composite Attribute	Can be divided into sub-attributes.	Name -> FirstName, MiddleName, LastName.	Ellipse connected to smaller ellipses.
Single-Valued Attribute	Has only one value for each entity.	RollNo, DateOfBirth.	Single ellipse.
Multivalued Attribute	Can have multiple values for one entity.	PhoneNumbers, Skills, EmailAddresses.	Double ellipse.
Derived Attribute	Can be calculated from another attribute.	Age from DateOfBirth.	Dashed ellipse.
Stored Attribute	Stored directly in the database.	DateOfBirth used to calculate Age.	Single ellipse.
Key Attribute	Uniquely identifies an entity.	Student_ID, Employee_ID.	Underlined attribute name.
Partial Key	Identifies weak entity instances within the owner entity.	Dependent_Name under Employee.	Dashed underline.
Null Attribute	May have no value for some entities.	MiddleName, AlternatePhone.	Normal attribute, with optional value.

Key Attribute and Composite Key

A key attribute uniquely identifies each entity in an entity set. If one attribute is enough, it is a simple key. If multiple attributes together are needed, it is a composite key.

Key Type	Meaning	Example
Simple Key	One attribute uniquely identifies the entity.	Student_ID identifies each student.
Composite Key	Combination of attributes uniquely identifies the entity.	Course_ID and Section_No together identify a course section.
Candidate Key	Any minimal key that can uniquely identify entities.	Student_ID or Email may both be candidate keys.
Primary Key	The candidate key chosen as the main identifier.	Student_ID selected as primary key.

Relationship

A relationship describes how two or more entities are associated. For example, Student ENROLLS_IN Course, Employee WORKS_FOR Department, and Customer PLACES Order.

A relationship can also have attributes. For example, the relationship ENROLLS between Student and Course may have attributes such as Enrollment_Date, Semester, and Grade.

Relationship	Entities Involved	Possible Relationship Attributes
ENROLLS_IN	Student and Course	Semester, Enrollment_Date, Grade.
WORKS_FOR	Employee and Department	Joining_Date, Role.
PLACES	Customer and Order	Order_Date, Order_Source.
BORROWS	Student and Book	Issue_Date, Return_Date, Fine.

Degree of Relationship

The degree of a relationship is the number of entity types participating in that relationship.

Relationship Degree	Meaning	Example
Unary or Recursive	Relationship among entities of the same entity type.	Employee supervises Employee.
Binary	Relationship between two entity types.	Student enrolls in Course.
Ternary	Relationship among three entity types.	Supplier supplies Part to Project.
N-ary	Relationship among more than three entity types.	Complex project assignment involving multiple entity types.

Recursive Relationship

A recursive relationship occurs when an entity type has a relationship with itself. The same entity type plays different roles in the relationship.

Example: In an Employee entity, one employee may supervise another employee. Here Employee participates twice: once as Supervisor and once as Subordinate.

Cardinality Ratio

Cardinality defines how many instances of one entity can be associated with instances of another entity in a relationship.

Cardinality	Meaning	Example	Relational Design Hint
One-to-One (1:1)	One entity instance is related to at most one instance of another entity.	Person has one Passport.	Foreign key can be placed in either table, usually on the total participation side.
One-to-Many (1:N)	One entity instance is related to many instances of another entity.	Department has many Employees.	Foreign key is placed on the many side.
Many-to-One (N:1)	Many entity instances are related to one instance of another entity.	Many Employees work for one Department.	Same as 1:N, viewed from the opposite direction.
Many-to-Many (M:N)	Many instances of one entity are related to many instances of another entity.	Students enroll in many Courses; Courses have many Students.	Create a separate relationship table.

Participation Constraints

Participation tells whether all entity instances must participate in a relationship or only some instances may participate.

Participation Type	Meaning	Example	ER Notation
Total Participation	Every entity instance must participate in the relationship.	Every Employee must work for a Department.	Double line.
Partial Participation	Some entity instances may not participate in the relationship.	Some Employees may not manage any Department.	Single line.

Minimum and Maximum Cardinality

Some ER diagrams use minimum and maximum cardinality notation. It shows the minimum and maximum number of relationship instances allowed for each entity.

Notation	Meaning	Example Meaning
`(0, 1)`	Optional, at most one.	An employee may manage zero or one department.
`(1, 1)`	Mandatory, exactly one.	Each employee must belong to exactly one department.
`(0, N)`	Optional, many allowed.	A student may enroll in zero or many optional workshops.
`(1, N)`	Mandatory, many allowed.	A department must have one or more employees.

Weak Entity in Detail

A weak entity depends on a strong entity for identification. It does not have a complete primary key of its own. It is identified using the primary key of the owner entity plus its own partial key.

It is represented by a double rectangle.
Its identifying relationship is represented by a double diamond.
It normally has total participation in the identifying relationship.
It has a partial key, also called a discriminator.

Owner Entity	Weak Entity	Partial Key	Final Identifier
Employee	Dependent	Dependent_Name	Employee_ID + Dependent_Name
Order	Order_Item	Item_No	Order_ID + Item_No
Course	Course_Section	Section_No	Course_ID + Section_No

ER Diagram Notation Summary

Symbol	Represents	Example
Rectangle	Strong entity	STUDENT
Double rectangle	Weak entity	DEPENDENT
Ellipse	Attribute	Name, Age, Email
Double ellipse	Multivalued attribute	PhoneNumbers
Dashed ellipse	Derived attribute	Age from DateOfBirth
Underlined attribute	Key attribute	Student_ID
Dashed underline	Partial key of weak entity	Item_No of Order_Item
Diamond	Relationship	ENROLLS_IN
Double diamond	Identifying relationship	HAS_DEPENDENT
Single line	Partial participation	Some employees manage departments.
Double line	Total participation	Every dependent belongs to an employee.

CSS Diagram: Common ER Symbols

STUDENT

Strong entity

ORDER_ITEM

Weak entity

ENROLLS

Relationship

Student_ID

Key attribute

Phone

Multivalued attribute

Age

Derived attribute

Rectangle = entity, double rectangle = weak entity, ellipse = attribute, underlined ellipse = key attribute, double ellipse = multivalued attribute, dashed ellipse = derived attribute, and diamond = relationship.

Enhanced ER Model Concepts

The basic ER model can be extended with advanced concepts such as specialization, generalization, aggregation, and inheritance. These are often called Enhanced ER or EER concepts.

Generalization

Generalization is a bottom-up process in which common features of multiple entity types are combined into a higher-level generalized entity.

Example: Car and Truck can be generalized into Vehicle because both have Vehicle_ID, Model, Brand, and Registration_No.

Specialization

Specialization is a top-down process in which a general entity is divided into specialized sub-entities based on distinguishing features.

Example: Employee can be specialized into Teacher, Clerk, Manager, and Technician. All are employees, but each type may have extra attributes.

Inheritance

In EER modeling, specialized entities inherit attributes and relationships from the general entity. For example, if Teacher is a subtype of Employee, then Teacher inherits Employee_ID, Name, Email, and Salary from Employee.

Disjoint and Overlapping Constraints

When an entity is specialized into subtypes, we must decide whether one entity instance can belong to one subtype or multiple subtypes.

Constraint	Meaning	Example
Disjoint	An entity instance can belong to only one subtype.	An employee is either Permanent or Contract, not both.
Overlapping	An entity instance can belong to more than one subtype.	A person can be both Student and Employee.

Total and Partial Specialization

Specialization Type	Meaning	Example
Total Specialization	Every entity in the superclass must belong to at least one subclass.	Every Account must be Savings_Account or Current_Account.
Partial Specialization	Some superclass entities may not belong to any subclass.	Some Employees may not be classified as Manager or Engineer.

Aggregation

Aggregation is an abstraction that treats a relationship as a higher-level entity so it can participate in another relationship.

Example: Employee works on Project. The relationship WORKS_ON may have Hours. A Manager monitors this work assignment. In this case, WORKS_ON can be treated as a higher-level object involved in MONITORS.

How to Design an ER Diagram

ER modeling is a step-by-step design activity. The following process works well for most academic and practical database problems.

Read the requirement carefully and identify nouns as possible entities.
Identify properties of each entity as attributes.
Choose key attributes for each strong entity.
Identify relationships among entities using verbs from the requirement.
Decide cardinality ratios such as 1:1, 1:N, or M:N.
Decide participation constraints such as total or partial participation.
Identify weak entities and their owner entities, if any.
Attach relationship attributes where they describe the association rather than one entity alone.
Review the diagram with sample data to check whether the design matches real-world rules.
Convert the final ER model into relational tables.

Example: University ER Model

Consider a university database where students enroll in courses, instructors teach courses, and departments offer courses.

Entity	Attributes	Key
STUDENT	Student_ID, Name, DOB, Email, Phone	Student_ID
COURSE	Course_ID, Title, Credits	Course_ID
INSTRUCTOR	Instructor_ID, Name, Email, Designation	Instructor_ID
DEPARTMENT	Dept_ID, Dept_Name, Location	Dept_ID

Relationship	Entities	Cardinality	Relationship Attributes
ENROLLS_IN	STUDENT and COURSE	M:N	Semester, Enrollment_Date, Grade
TEACHES	INSTRUCTOR and COURSE	1:N	Assigned_Date
BELONGS_TO	INSTRUCTOR and DEPARTMENT	N:1	Joining_Date
OFFERS	DEPARTMENT and COURSE	1:N	None

CSS Diagram: Student Course Enrollment

STUDENT

Student_ID Name

ENROLLS_IN

COURSE

Course_ID Title

STUDENT and COURSE have an M:N relationship, so the relationship needs its own table during relational mapping. Semester and Grade describe the enrollment, not only the student or only the course.

Text Representation of the ER Design

In written answers, you can describe the ER model clearly even without drawing a diagram.

university-er-model.txt

STUDENT(Student_ID, Name, DOB, Email, Phone)
COURSE(Course_ID, Title, Credits)
INSTRUCTOR(Instructor_ID, Name, Email, Designation)
DEPARTMENT(Dept_ID, Dept_Name, Location)

Relationships:
1. STUDENT M:N COURSE through ENROLLS_IN
   Attributes: Semester, Enrollment_Date, Grade

2. INSTRUCTOR 1:N COURSE through TEACHES
   Attribute: Assigned_Date

3. DEPARTMENT 1:N INSTRUCTOR through BELONGS_TO
   Attribute: Joining_Date

4. DEPARTMENT 1:N COURSE through OFFERS

Converting ER Model to Relational Tables

After the ER model is finalized, it is converted into relational tables. The conversion rules depend on entity type, relationship type, attributes, and constraints.

ER Construct	Relational Mapping Rule	Example
Strong entity	Create a table with all simple attributes. Key attribute becomes primary key.	STUDENT(Student_ID, Name, Email)
Composite attribute	Store its simple components, not the composite attribute itself.	Name becomes First_Name, Last_Name.
Multivalued attribute	Create a separate table with owner key and multivalued attribute.	STUDENT_PHONE(Student_ID, Phone)
Derived attribute	Usually do not store it unless performance requires it.	Age can be calculated from DateOfBirth.
Weak entity	Create a table including owner's primary key and partial key.	ORDER_ITEM(Order_ID, Item_No, Product_ID, Quantity)
1:N relationship	Place foreign key of the one side in the many-side table.	Employee table contains Dept_ID.
M:N relationship	Create a separate table using primary keys of both entities.	ENROLLMENT(Student_ID, Course_ID, Semester, Grade)

university-er-schema.sql

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE instructors (
    instructor_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    designation VARCHAR(50),
    dept_id INT NOT NULL,
    joining_date DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    credits INT NOT NULL,
    dept_id INT NOT NULL,
    instructor_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
    FOREIGN KEY (instructor_id) REFERENCES instructors(instructor_id)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    dob DATE,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20)
);

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    semester VARCHAR(20),
    enrollment_date DATE,
    grade VARCHAR(5),
    PRIMARY KEY (student_id, course_id, semester),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

ER Model vs Relational Model

Point	ER Model	Relational Model
Level	Conceptual design level.	Logical implementation level.
Main focus	Entities, attributes, relationships, constraints.	Tables, rows, columns, keys, constraints.
Representation	ER diagram.	Relational schema and SQL tables.
Used by	Analysts, database designers, users.	Developers, DBAs, DBMS.
Example	STUDENT entity related to COURSE entity.	students, courses, and enrollments tables.

Common ER Modeling Mistakes

Mistake	Correct Approach
Making every noun an entity.	Check whether it has independent existence and important attributes.
Storing multivalued attributes in one column.	Create a separate table or entity for repeated values.
Putting relationship attributes on the wrong entity.	If the attribute describes the association, attach it to the relationship.
Ignoring cardinality.	Always decide 1:1, 1:N, or M:N before mapping tables.
Ignoring participation constraints.	Check whether participation is mandatory or optional.
Treating weak entities as strong entities.	Use owner key plus partial key when the entity cannot be identified independently.
Creating a direct foreign key for an M:N relationship.	Create a separate relationship table for M:N relationships.

Advantages of ER Model

Easy to understand because it represents real-world objects.
Provides a clear visual design before table creation.
Helps identify data requirements and business rules.
Supports better communication among technical and non-technical people.
Helps convert requirements into relational schema systematically.
Reduces redundancy and improves database design quality.

Limitations of ER Model

It does not describe physical storage details.
Very large ER diagrams can become complex and difficult to read.
Some business rules may be hard to show using only basic ER notation.
Different designers may model the same requirement differently.
It must still be converted carefully into relational tables.

Interview and Exam Questions

Question	Short Answer
What is the ER model?	A conceptual data model that represents entities, attributes, relationships, and constraints.
Who proposed the ER model?	Peter Chen proposed the ER model in 1976.
What is an entity?	A real-world object or concept that can be identified separately.
What is an attribute?	A property or characteristic of an entity or relationship.
What is a relationship?	An association between two or more entities.
What is a weak entity?	An entity that cannot be identified by its own attributes alone and depends on an owner entity.
What is a partial key?	An attribute that identifies weak entity instances within the same owner entity.
What is cardinality?	It defines how many instances of one entity can be associated with instances of another entity.
What is total participation?	Every entity instance must participate in the relationship.
How is an M:N relationship mapped?	By creating a separate relationship table containing primary keys of participating entities.
What is specialization?	A top-down process of dividing a general entity into sub-entities.
What is generalization?	A bottom-up process of combining similar entity types into a generalized entity.

Quick Revision Notes

ER model is a conceptual model used before relational schema design.
Main ER components are entities, attributes, relationships, and constraints.
Strong entities have their own primary key; weak entities depend on owner entities.
Attributes may be simple, composite, single-valued, multivalued, derived, stored, key, or partial key attributes.
Cardinality can be 1:1, 1:N, N:1, or M:N.
Participation can be total or partial.
M:N relationships are converted into separate relational tables.
Multivalued attributes are usually converted into separate tables.
EER concepts include generalization, specialization, inheritance, and aggregation.
ER diagrams help convert real-world requirements into correct database structure.

Key Takeaways

The ER model is a high-level conceptual data model proposed by Peter Chen in 1976.
It represents real-world objects as entities, properties as attributes, and associations as relationships.
Strong entities have their own key, while weak entities depend on owner entities.
Attributes can be simple, composite, multivalued, derived, stored, key, partial key, or nullable.
Cardinality defines 1:1, 1:N, N:1, and M:N relationship rules.
Participation constraints define whether relationship participation is mandatory or optional.
Weak entities use an owner key plus a partial key for identification.
Specialization, generalization, inheritance, and aggregation are enhanced ER concepts.
M:N relationships and multivalued attributes usually become separate relational tables.
ER modeling is the bridge between real-world requirements and relational database schema.

ER Model Entity Relationship Diagram: Tutorial, Examples, FAQs & Interview Tips

Entity-Relationship Model in DBMS

Why ER Model is Needed

Main Components of ER Model

Entity

Entity Type and Entity Set

Strong Entity and Weak Entity

Attribute

Types of Attributes

Key Attribute and Composite Key

Relationship

Degree of Relationship

Recursive Relationship

Cardinality Ratio

Participation Constraints

Minimum and Maximum Cardinality

Weak Entity in Detail

ER Diagram Notation Summary

Enhanced ER Model Concepts

Generalization

Specialization

Inheritance

Disjoint and Overlapping Constraints

Total and Partial Specialization

Aggregation

How to Design an ER Diagram

Example: University ER Model

Text Representation of the ER Design

Converting ER Model to Relational Tables

ER Model vs Relational Model

Common ER Modeling Mistakes

Advantages of ER Model

Limitations of ER Model

Interview and Exam Questions

Quick Revision Notes

Level Up Your Dbms Skills

Popular Tutorials

Ready to Level Up Your Skills?