Before a builder lays a single brick, an architect draws a plan. Before a developer writes a single CREATE TABLE, a database designer draws an ER diagram.
The Entity-Relationship (ER) model is the most widely used way to design a database at the conceptual level. It lets you describe what data an organization needs, in a picture that both programmers and non-technical people can understand.
In this chapter we will learn what the ER model is, its three building blocks (entities, attributes and relationships), the standard symbols, and how to go from a plain-English requirement to an ER diagram step by step. The next few chapters then zoom into each building block in detail.
What Is the ER Model?
The Entity-Relationship (ER) model is a high-level conceptual data model that describes a database as a collection of entities, the attributes that describe them, and the relationships among them.
It was proposed by Peter Chen in 1976. The picture you draw using it is called an ER diagram (ERD).
Recall from Data Models that data models come in levels:
Conceptual model → ER model (what data, what meaning)
↓
Logical model → Relational model (tables, keys)
↓
Physical model → Files, indexes (how it is stored)The ER model sits at the top. It does not care about tables, data types or indexes. It only cares about the things in the business and how they are connected.
Why Use an ER Model?
You might ask: "Why not just start creating tables directly?"
For a tiny project, you might get away with that. For anything real, starting with tables usually leads to problems:
Missed requirements. You forget that a student can have several phone numbers, or that a course can be taught by more than one teacher.
Wrong structure. Fixing a table design after it is full of data and used by ten applications is expensive.
Communication gap. A college principal cannot review your SQL, but they can look at a diagram and say "No, one department can have two HODs during transition."
The ER model solves these by giving you:
A clear, visual blueprint that is easy to discuss and correct.
A design that is independent of any DBMS (MySQL, Oracle, PostgreSQL all work).
A systematic way to convert the design into tables later (covered in Converting ER Diagrams to Tables).
In short: think in ER first, then build in tables.
Where ER Fits in Database Design
Database design usually goes through these phases:
1. Requirements collection "What does the college need to store?"
↓
2. Conceptual design ER diagram ◄── this chapter
↓
3. Logical design Tables (relational schema)
↓
4. Normalization Remove redundancy (later chapters)
↓
5. Physical design Indexes, storage, tuningBuilding Block 1: Entity
An entity is a real-world object or concept that can be distinctly identified and about which we want to store data.
An entity may be:
Physical (tangible): a student, a book, a car, an employee.
Conceptual (intangible): a course, a bank account, an order, a job.
Students often mix up three similar words. Let's separate them clearly:
Term | Meaning | Example |
|---|---|---|
Entity | One specific object | The student "Priya, roll no. 21CS045" |
Entity type | The category/template that entities share (same attributes) | STUDENT (with roll_no, name, dob, ...) |
Entity set | The collection of all entities of one type at a given moment | All students currently stored in the database |
A helpful analogy from programming:
Entity type ≈ a class
Entity ≈ an object
Entity set ≈ the collection of all objects of that class
Later, when the design becomes a table, the entity type becomes the table definition, each entity becomes a row, and the entity set is all the rows.
Strong and Weak Entities
Most entities can be identified on their own (every student has a unique roll number). These are strong entities. Some cannot: a "dependent" of an employee (like their child) may only be identified together with the employee. These are weak entities, and they get their own chapter: Weak Entities.
Building Block 2: Attribute
An attribute is a property that describes an entity (or a relationship).
A STUDENT entity type may have the attributes roll_no, name, dob, email and phone. Each individual student has a value for each attribute, for example name = 'Priya'.
Every attribute has a domain (value set): the set of allowed values. For example, the domain of semester might be the integers 1 to 8.
One attribute (or a group of attributes) whose value is unique for every entity is called a key attribute. For STUDENT, roll_no is a natural key attribute. In Chen-style ER diagrams, the name of a key attribute is underlined. Since this page uses plain text diagrams, we will mark it with underscores, like _roll_no_.
Attributes come in several flavours: simple vs composite, single-valued vs multivalued, stored vs derived. These are covered in detail in the next chapter, Types of Attributes.
Building Block 3: Relationship
A relationship is an association among two or more entities.
For example, "Priya enrolls in DBMS" is a relationship between the student Priya and the course DBMS.
Just like entities, we separate:
Relationship (instance): one specific association, Priya–DBMS.
Relationship type: the general pattern, ENROLLS_IN between STUDENT and COURSE.
Relationship set: all such associations currently stored.
Relationships can also have their own attributes. For ENROLLS_IN, the grade belongs neither to the student alone (they have many grades) nor to the course alone (it has many students). It belongs to the pair, so it is an attribute of the relationship.
How many entities can take part, how many times, and whether participation is compulsory are all covered in Relationships, Cardinality & Participation.
ER Diagram Symbols (Chen Notation)
The original and most exam-friendly notation is Chen notation. Here are the symbols you need to know:
Symbol | Meaning |
|---|---|
Rectangle | Strong entity type |
Double rectangle | Weak entity type |
Ellipse (oval) | Attribute |
Ellipse with underlined name | Key attribute |
Ellipse with dashed underline | Partial key of a weak entity |
Double ellipse | Multivalued attribute |
Dashed ellipse | Derived attribute |
Ellipse connected to smaller ellipses | Composite attribute |
Diamond | Relationship type |
Double diamond | Identifying relationship (for weak entities) |
Single line | Partial participation |
Double line | Total participation |
1, N, M written on lines | Cardinality ratio |
Since this page uses text diagrams, we will draw them like this:
[ STUDENT ] rectangle → entity
[[ DEPENDENT ]] double → weak entity
( name ) oval → attribute
( _roll_no_ ) underlined → key attribute
( ~dep_name~ ) dashed underline → partial key (weak entity)
(( phone )) double → multivalued attribute
( -age- ) dashed → derived attribute
< ENROLLS > diamond → relationship
<< HAS >> double → identifying relationship
----- single line → partial participation
===== double line → total participationOther Notations
Industry tools (MySQL Workbench, draw.io, Lucidchart) commonly use Crow's Foot notation, where attributes are listed inside the entity box and the "many" side of a relationship is drawn as a three-pronged crow's foot. UML class diagrams are also used for data modeling. The concepts are the same; only the drawing style changes. For university exams and GATE, Chen notation (as in the Elmasri & Navathe and Korth textbooks) is the standard.
Worked Example: A Small College Database
Let's design from a requirement, step by step. Here is what the college tells us:
"The college has departments, each with a unique code and a name. Every student belongs to exactly one department. A student has a roll number, a name, a date of birth and possibly several phone numbers. Courses have a unique code, a title and credits, and each course is offered by one department. Students enroll in courses, and we record the grade a student gets in each course."
Step 1: Find the entities (look for nouns)
Nouns that are important "things" with their own data: Department, Student, Course.
"Grade" and "phone number" are nouns too, but they only describe something else, so they are attributes, not entities.
Step 2: Find the attributes of each entity
DEPARTMENT : _dept_code_, dept_name
STUDENT : _roll_no_, name, dob, ((phone)), -age- (can be computed from dob)
COURSE : _course_code_, title, creditsStep 3: Find the relationships (look for verbs)
Student belongs to Department → BELONGS_TO
Department offers Course → OFFERS
Student enrolls in Course → ENROLLS (with attribute
grade)
Step 4: Decide how many (cardinality) and whether compulsory (participation)
Each student belongs to exactly one department; a department has many students → N:1, and every student must have a department (total participation of STUDENT).
Each course is offered by one department; a department offers many courses → N:1, total participation of COURSE.
A student takes many courses; a course has many students → M:N.
Step 5: Draw the diagram
( _dept_code_ ) ( dept_name )
\ /
[ DEPARTMENT ]
/1 \1
/ \
< BELONGS_TO > < OFFERS >
|| ||
||N ||N
[ STUDENT ] [ COURSE ]
|M |N
| |
+-----< ENROLLS >------+
|
( grade )
STUDENT attributes : ( _roll_no_ ) ( name ) ( dob ) (( phone )) ( -age- )
COURSE attributes : ( _course_code_ ) ( title ) ( credits )
| single line = partial participation || double line = total participationRead it like a sentence: "Each STUDENT belongs to one DEPARTMENT (double line: every student must), each COURSE is offered by one DEPARTMENT, and STUDENTs ENROLL in many COURSEs, earning a grade."
Notice what the diagram tells us without a single line of SQL:
phoneis multivalued, so it will need special handling when we create tables.ageis derived, so we probably should not store it at all.gradebelongs to the ENROLLS relationship, not to STUDENT or COURSE.
All three insights would be easy to get wrong if we jumped straight to tables.
How to Decide: Entity, Attribute or Relationship?
This is where most design confusion happens. Some useful rules of thumb:
Question | If yes, model it as... |
|---|---|
Does it have its own properties that we need to store? | Entity |
Does it only describe one other thing (one value per thing)? | Attribute |
Is it an action/association connecting two or more things? | Relationship |
Is it shared by many entities and needs its own data (e.g., city with population, pin code)? | Promote it to an entity |
Example: if you only need a student's city name, city is an attribute. If the college also needs each city's state, distance from campus and bus route, then CITY becomes an entity, and "student lives in city" becomes a relationship.
So the answer depends on the requirements, not on the word itself. There is often more than one correct ER design.
Advantages and Limitations of the ER Model
Advantages | Limitations |
|---|---|
Simple and visual; easy for non-technical users to review | No standard notation; many variants exist |
Independent of any specific DBMS | Does not show data manipulation (queries, operations) |
Maps cleanly to the relational model | Some constraints (e.g., "salary of a manager must exceed salary of their team") cannot be drawn |
Good for finding missing or wrong requirements early | Large diagrams become cluttered; needs extensions (EER) for inheritance |
That last point is why the Enhanced ER (EER) model adds generalization, specialization and aggregation, which we cover in Generalization, Specialization & Aggregation.
Common Mistakes
Making every noun an entity. "Grade", "phone" and "address" usually describe something else and are attributes.
Putting relationship attributes on an entity. Putting
gradeon STUDENT would mean a student has only one grade in total, which is wrong.Drawing foreign keys in an ER diagram. Do not add
dept_codeas an attribute of STUDENT. The relationship BELONGS_TO already says that. Foreign keys appear only when you convert to tables.Confusing entity, entity type and entity set. One object vs the template vs the collection.
Forgetting the key attribute of a strong entity. Every strong entity type must have one.
Interview Questions
1. What is an ER model and who proposed it?
A high-level conceptual data model that represents data as entities, attributes and relationships. It was proposed by Peter Chen in 1976.
2. What is the difference between an entity, an entity type and an entity set?
An entity is one real-world object (one student). An entity type is the category with a common set of attributes (STUDENT). An entity set is the collection of all entities of that type stored at a moment.
3. Why do we design an ER diagram before creating tables?
It captures requirements clearly, is easy to verify with non-technical stakeholders, is DBMS-independent, and catches design errors early, when they are cheap to fix. It can then be mechanically converted into tables.
4. Can a relationship have attributes? Give an example.
Yes. In STUDENT–ENROLLS–COURSE, grade describes a specific student–course pair, so it is an attribute of the ENROLLS relationship.
5. What do a rectangle, ellipse and diamond represent in an ER diagram?
Entity type, attribute and relationship type respectively. Double versions mean weak entity, multivalued attribute and identifying relationship.
6. Is the ER model a logical or conceptual model?
Conceptual. It describes what data exists and how it is related, without any implementation details. The relational model (tables) is the logical level.
Summary
The ER model (Chen, 1976) is a conceptual model for designing databases before building them.
It has three building blocks: entities (things), attributes (their properties) and relationships (associations between things).
Distinguish an entity (one object), entity type (template) and entity set (collection). The same applies to relationships.
Chen notation: rectangle = entity, ellipse = attribute, diamond = relationship; doubled shapes mean weak entity, multivalued attribute and identifying relationship; double line = total participation.
Design process: find nouns (entities), their properties (attributes), verbs (relationships), then cardinality and participation.
Whether something is an entity or attribute depends on the requirements.
In the next chapter, Types of Attributes, we will look closely at the different kinds of attributes (composite, multivalued, derived and more) and why each kind matters when the design becomes tables.