ER Model in DBMS: ER Diagram, Entities & Keys Guide

Section 01

The Story That Explains the ER Model

📖 Real World Analogy

The Architect's Blueprint

Imagine an architect asked to build a hospital. She does not start by pouring concrete. First she draws a blueprint — rooms as boxes, corridors as lines, doors where boxes connect. Anyone can read it, argue about it, and fix mistakes on paper long before a single brick is laid.

A database designer works exactly the same way. Before creating a single table, she draws an Entity-Relationship Diagram — boxes for the "things" the business cares about, lines for how those things connect. The ER model is the blueprint of a database: cheap to draw, cheap to change, and impossible to skip if you want a clean design.

The Entity-Relationship (ER) Model is a high-level, conceptual data model introduced by Dr. Peter Chen in 1976. It describes what data an organisation needs to store and how the pieces relate — without yet worrying about how a specific DBMS will physically store it. Its visual form is the ER Diagram.

💡

The Core Insight

An ER diagram reads like a sentence. Entities are the nouns (Student, Course, Doctor), relationships are the verbs (enrols in, teaches, treats), and attributes are the adjectives that describe them (name, age, salary). If you can describe your business in plain English, you can already draw 80% of its ER diagram.

Section 02

The Three Building Blocks

Every ER diagram, no matter how large, is assembled from just three core ingredients. Master these and everything else is detail.

📦

1 — Entity

Drawn as a Rectangle

A real-world object or concept that can be distinctly identified and that we want to store data about — a Student, a Car, an Account. It can be physical (a person) or conceptual (a course).

🌑

2 — Attribute

Drawn as an Ellipse

A property that describes an entity. A Student has a Roll No, a Name, an Age. Attributes are the columns that will eventually appear inside the table.

🔗

3 — Relationship

Drawn as a Diamond

An association between two or more entities. A Student enrols in a Course; a Customer owns an Account. The diamond is the verb that links the noun-boxes.

Section 03

The ER Notation Legend (Chen's Notation)

ER diagrams use a small, fixed vocabulary of shapes. The animated legend below introduces every symbol you will meet in this tutorial. Each shape carries a colour-coded border so it stays readable in both light and dark themes — watch them appear one by one.

🎨 Chen Notation — Complete Symbol Set

Keep this legend handy — every later diagram reuses these exact symbols.

Section 04

Entities and Entity Sets

Beginners constantly confuse an entity with an entity set. The distinction is simple but vital.

🧮 Entity vs Entity Set — The Difference

Entity

A single, specific instance — e.g. the student Aman, Roll 101. It corresponds to one row.

Entity Set

The collection of all entities of the same type — e.g. STUDENT = every student. It corresponds to the whole table.

In ER

The rectangle is labelled with the entity-set name (STUDENT), never with an individual student. Individuals live in the data, not the diagram.

📦 One Entity Set "STUDENT" containing many entities

The dashed box is the set; each circle is one entity (one row). In the diagram we draw only the rectangle named STUDENT.

🔑

Quick Memory Hook

Entity Set = Table • Entity = Row • Attribute = Column. Whenever you read "STUDENT" in an ER diagram, think of an empty table with columns waiting to be filled.

Section 05

Attributes and Their Types

Attributes describe entities, but not all attributes behave the same way. There are six important types, each with its own symbol. The animated diagram shows them all radiating from a single STUDENT entity.

🌑 The Six Attribute Types on one STUDENT entity

Age is derived because it is computed from Date_of_Birth — it is never stored directly.

Attribute Type	Symbol	Meaning	Example
Simple (Atomic)	Plain ellipse	Cannot be divided further	Gender, Age
Composite	Ellipse with sub-ellipses	Splits into smaller parts	Name → First + Last
Single-valued	Plain ellipse	Holds exactly one value	Date_of_Birth
Multivalued	Double ellipse	Holds many values	Phone_No, Email
Derived	Dashed ellipse	Computed from others	Age (from DOB)
Key	Underlined ellipse	Uniquely identifies entity	Roll_No

More Examples — Each Type Across Real Entities

These six types show up in every database you will ever design. Below, each type is illustrated with several examples from different domains so the pattern becomes second nature.

🔢

Simple (Atomic)

Plain ellipse

STUDENT: Gender, Age
PRODUCT: Price, Colour
EMPLOYEE: Salary, Designation

🧩

Composite

Ellipse with sub-ellipses

STUDENT: Name → First + Last
CUSTOMER: Address → Street + City + PIN
EMPLOYEE: Date_of_Birth → Day + Month + Year

1️⃣

Single-valued

Plain ellipse, one value

PERSON: Date_of_Birth
BOOK: ISBN
ACCOUNT: Balance

📝

Multivalued

Double ellipse, many values

STUDENT: Phone_No, Email
EMPLOYEE: Skills, Languages
PERSON: Hobbies, Degrees

⚙️

Derived

Dashed ellipse, computed

PERSON: Age (from Date_of_Birth)
ORDER: Total (Qty × Price)
EMPLOYEE: Experience (today − Join_Date)

🔑

Key

Underlined ellipse, unique

STUDENT: Roll_No
EMPLOYEE: Emp_ID
PRODUCT: SKU

Worked Example — Breaking Down Composite Attributes

Composite attributes are the ones beginners most often get wrong. Here are three fully decomposed, showing exactly which leaf parts would become real columns.

🧩 Composite Decomposition in Practice

Name

Name → First_Name, Middle_Name, Last_Name — lets you sort by surname or greet by first name.

Address

Address → House_No, Street, City, State, PIN_Code — enables searching all customers in one city.

DOB

Date_of_Birth → Day, Month, Year — and note Age is then a derived attribute computed from it.

💡

Composite vs Multivalued — Don't Mix Them Up

Composite = one value split into parts (one Address = House + Street + City). Multivalued = many separate values of the same kind (two phone numbers, three email addresses). A single person has one composite Address but possibly several multivalued Phone_No entries.

⚠️

A Classic Exam Trap

A NULL value is a missing value, but a multivalued attribute is a single attribute holding several values at once. Students often write Phone_No as one normal ellipse — if a person can have a mobile and a landline, it must be a double ellipse.

Section 06

Keys — Guaranteeing Uniqueness

📖 Story

Why Two "Rahul Sharma"s Break the System

A college has two students both named Rahul Sharma, both aged 19, both from Delhi. When the exam office tries to upload marks "for Rahul Sharma", whose record updates? Chaos. The fix is a key — a unique Roll No that no two students share. A key is the database's promise: "give me this value and I will hand you exactly one row, never zero, never two."

Keys form a hierarchy. Every primary key is a candidate key; every candidate key is a super key — but not the other way around.

Key Type	Definition	Example (STUDENT)
Super Key	Any set of attributes that uniquely identifies a row (may contain extras)	{Roll_No}, {Roll_No, Name}, {Roll_No, Age}
Candidate Key	A minimal super key — remove any attribute and uniqueness is lost	{Roll_No}, {Email}, {Aadhaar_No}
Primary Key	The one candidate key chosen by the designer to identify rows	Roll_No
Alternate Key	Candidate keys not chosen as primary	Email, Aadhaar_No
Composite Key	A key made of two or more attributes together	{Course_ID, Section} in ENROLLMENT
Foreign Key	An attribute that refers to the primary key of another table	Dept_ID inside STUDENT → DEPARTMENT

🧩 The Key Hierarchy — nested sets

Every primary key ⊆ candidate key ⊆ super key. The designer simply picks the best candidate as primary.

Section 07

Relationships and Their Degree

A relationship is an association among entities; a relationship set is the collection of all such associations of the same type. The degree of a relationship is the number of entity sets it connects.

🔄

Unary (Degree 1)

Recursive / Self-relationship

One entity set related to itself. An Employee manages another Employee; a Course is a prerequisite of another Course.

👫

Binary (Degree 2)

Most common — ~95% of cases

Connects two entity sets. Student enrols in Course; Customer owns Account. This is the workhorse of database design.

👪

Ternary (Degree 3)

Three at once

Connects three entity sets simultaneously. A Doctor prescribes a Drug to a Patient — all three are needed to make the fact meaningful.

🔗 A Binary Relationship "ENROLS"

A relationship can have its own descriptive attributes too — e.g. ENROLS could carry a grade or enrol_date.

Section 08

Participation Constraints

Where cardinality counts how many, participation answers a yes/no question: "Must every entity take part in this relationship?"

➖ Partial Participation

Drawn with a single line

Some entities may not participate

Example: not every Employee manages a department

"It is optional"

➗ Total Participation

Drawn with a double line

Every entity must participate

Example: every Loan must belong to a customer

"It is mandatory"

➡️ Single line vs Double line

Total participation is a business rule: a loan with no customer is meaningless, so the connection is mandatory.

Section 09

Strong & Weak Entities

📖 Story

The Room That Cannot Exist Without Its Building

A hotel labels its rooms "101", "102", "201". But "Room 101" is ambiguous — every floor and every branch has one. Room number alone cannot identify a room. Only by saying "Room 101 of the Mumbai Taj" do we pin it down.

The Room is a weak entity: it has no key of its own and depends entirely on the Hotel (a strong entity) for identification. Delete the hotel and its rooms lose all meaning — they cannot exist on their own.

👑 Strong Entity

Has its own primary key

Exists independently

Drawn as a single rectangle

Example: HOTEL, LOAN, EMPLOYEE

🧹 Weak Entity

Has no key of its own

Depends on a strong "owner" entity

Drawn as a double rectangle

Example: ROOM, PAYMENT, DEPENDENT

A weak entity is connected to its owner through an identifying relationship (a double diamond), and it is uniquely identified by combining its own partial key (dashed underline) with the owner's primary key.

🧹 Strong LOAN identifies Weak PAYMENT

Full identifier of a payment = Loan_No + Payment_No. Payment_No alone repeats across loans.

✅

Three Signs You Are Looking at a Weak Entity

(1) It is drawn as a double rectangle; (2) it connects to its owner via a double diamond identifying relationship; (3) its key is a dashed-underlined partial key that only becomes unique when combined with the owner's primary key. A weak entity also always shows total participation in the identifying relationship.

Section 10

Putting It All Together — A Mini University ER Diagram

Now we assemble every concept into one diagram. Watch it build up: entities first, then the relationship, then the connecting lines and attributes.

🏫 University Enrolment — Complete Mini Schema

Reads as: "Many STUDENTs ENROL in many COURSEs, and each enrolment records a Grade." A textbook M:N relationship with a descriptive attribute.

Section 11

From ER Diagram to Tables

An ER diagram is a plan; eventually it becomes real tables. The conversion follows a few mechanical rules. (This is conceptual mapping — no programming required.)

📋 ER → Relational Mapping Rules

Strong entity → its own table; its key attribute becomes the primary key.

Composite attribute → store only its leaf parts as columns (Name → First_Name, Last_Name).

Multivalued attribute → a separate table linked by the entity's key (Phone_No gets its own table).

Derived attribute → usually not stored; computed on demand (Age from DOB).

1:N relationship → put the "one" side's primary key as a foreign key in the "many" side. No new table needed.

M:N relationship → always create a new junction table holding both keys plus any descriptive attributes.

Weak entity → a table whose primary key = owner's key + partial key (Loan_No + Payment_No).

Applying rules 1 and 6 to our University diagram gives three tables:

-- Strong entities become their own tables
STUDENT ( Roll_No PK, Name, Gender )
COURSE  ( Course_ID PK, Title )

-- M:N relationship becomes a junction table
ENROLS ( Roll_No FK, Course_ID FK, Grade )
        -- Primary key = (Roll_No, Course_ID)

🧮

Why the Junction Table Matters

You can never store an M:N relationship in just two tables — there is nowhere to put the Grade. The junction table ENROLS solves this: one row per (student, course) pair, with the grade attached. This is one of the most common real-world schema patterns.

Section 12

Common Mistakes to Avoid

⚠️

Mistake 1 — Modelling a verb as an entity

"Purchase" sounds like a thing, but it is usually a relationship between Customer and Product, not an entity. Ask: does it have an independent identity, or does it only exist because two entities met?

⚠️

Mistake 2 — Forgetting the key underline

An attribute ellipse without an underline is just data. Examiners deduct marks instantly when the key attribute is not underlined. Every strong entity needs exactly one underlined key.

⚠️

Mistake 3 — Confusing cardinality with participation

Cardinality (1, N, M) answers "how many?"; participation (single vs double line) answers "is it mandatory?". They are independent — a relationship can be 1:N and total at the same time.

Section 13

Quick Reference — Symbol Cheat Sheet

Symbol	Represents	Remember By
Rectangle	Entity set	A solid "thing"
Double rectangle	Weak entity	Needs a partner to stand
Ellipse	Attribute	A descriptor
Underlined ellipse	Key attribute	The unique ID
Double ellipse	Multivalued attribute	Holds a list
Dashed ellipse	Derived attribute	Calculated, not stored
Diamond	Relationship set	The verb
Double diamond	Identifying relationship	Anchors a weak entity
Single line	Partial participation	Optional
Double line	Total participation	Mandatory

Section 14

Golden Rules of ER Modelling

🏆 ER Model — Non-Negotiable Rules

Nouns are entities, verbs are relationships, adjectives are attributes. Write your requirements as a sentence first, then translate it shape by shape.

Every strong entity must have exactly one underlined primary key. No key means it is either a weak entity or a modelling error.

Label the rectangle with the entity-set name, never an individual. Draw STUDENT, not "Aman" — individuals belong in the data, not the diagram.

Decide cardinality and participation separately. First ask "how many?" (1, N, M), then ask "is it mandatory?" (single vs double line). They never depend on each other.

A weak entity always needs three things together: a double rectangle, a double diamond identifying relationship, and a dashed partial key.

Descriptive data about a relationship goes on the diamond, not on either entity. A grade belongs to ENROLS, not to STUDENT or COURSE alone.

Every M:N relationship becomes its own table when implemented. If you cannot find a home for a relationship's attribute, you have found a missing junction table.