The Entity-Relationship (ER) Model
Press Next → or use ← → arrow keys
The Story — The Architect's Blueprint
The ER model is a high-level, conceptual data model. It's the blueprint of a database: cheap to draw, cheap to change, and impossible to skip if you want a clean design.
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).
The Three Building Blocks
The ER Notation Legend (Chen)
| Element | Symbol | Meaning |
|---|---|---|
| Entity | ▭ Rectangle | A "thing" we store data about |
| Weak Entity | ▭▭ Double rectangle | Cannot exist alone |
| Attribute | ⬭ Ellipse | A property of an entity |
| Key Attribute | ⬭ Underlined ellipse | Uniquely identifies the entity |
| Multivalued | ◎ Double ellipse | Holds multiple values |
| Derived | ⬭ Dashed ellipse | Computed from other attributes |
| Relationship | ◇ Diamond | Association between entities |
| Identifying Rel. | ◈ Double diamond | Identifies a weak entity |
| Partial Participation | — Single line | Optional participation |
| Total Participation | ═ Double line | Mandatory participation |
Entity vs Entity Set
| Concept | Definition | Corresponds to |
|---|---|---|
| Entity | A single specific instance — student Aman, Roll 101 | One row |
| Entity Set | The collection of all entities of the same type — STUDENT | The whole table |
In ER diagrams, rectangles are labelled with entity-set names (STUDENT), never individual names. Individuals live in the data, not the diagram.
Entity Set = Table • Entity = Row • Attribute = Column.
The Six Attribute Types
Composite vs Multivalued (and NULL)
A NULL is missing data — one value that isn't there yet. A multivalued attribute holds several values at once. They are not the same thing.
Keys — Guaranteeing Uniqueness
When two students are both "Rahul Sharma", queries return the wrong row. Keys guarantee every query lands on exactly one row. They nest:
Every primary key ⊆ candidate key ⊆ super key. The designer picks the best candidate as primary; the leftover candidates become alternate keys.
Every Key Type at a Glance
| Key | Definition | Example (STUDENT) |
|---|---|---|
| Super Key | Any attribute set that uniquely identifies a row (extras allowed) | {Roll_No}, {Roll_No, Name} |
| Candidate Key | A minimal super key — remove any part and uniqueness is lost | {Roll_No}, {Email}, {Aadhaar_No} |
| Primary Key | The one candidate key chosen 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} |
| Foreign Key | Attribute referencing another table's primary key | Dept_ID → DEPARTMENT |
A composite key needs all its attributes together to be unique — like (Roll_No, Course_ID) in an enrolment table.
Relationships & Their Degree
Unary links a set to itself (Employee manages Employee). Binary connects two — the workhorse of design. Ternary ties three at once (Doctor prescribes Drug to Patient). Relationships can also carry their own attributes, like grade or enrolment date.
Cardinality — How Many?
Cardinality (1, N, M) answers "how many?" Participation (single vs double line) answers "is it mandatory?" — they're independent. A relationship can be 1:N and total at once.
Participation — Optional or Mandatory?
Total participation encodes a business fact: a loan with no customer is meaningless, so the connection is mandatory. The diagram simply records the rule.
Strong & Weak Entities
"Room 101" is ambiguous — every floor and branch has one. Only "Room 101 of the Mumbai Taj" is unique. Room is weak (depends on Hotel); Hotel is strong.
1) Drawn as a double rectangle · 2) Connected to its owner by a double-diamond identifying relationship with total participation · 3) Its dashed partial key becomes unique only when combined with the owner's primary key. Full identifier: Loan_No + Payment_No.
Putting It Together — A Mini University ER
Many STUDENTs ENROL in many COURSEs, and each enrolment records a Grade. A textbook M:N relationship with a descriptive attribute — note the grade sits on the diamond, not on either entity.
From ER Diagram to Tables — 7 Rules
| # | Rule | Application |
|---|---|---|
| 1 | Strong entity → its own table; key becomes primary key | STUDENT(Roll_No PK, …) |
| 2 | Composite attribute → store only the leaf parts as columns | Name → First_Name, Last_Name |
| 3 | Multivalued attribute → separate table linked by the entity's key | Phone_No gets its own table |
| 4 | Derived attribute → usually not stored; computed on demand | Age from DOB |
| 5 | 1:N → put the "one" side's PK as a foreign key on the "many" side | No new table needed |
| 6 | M:N → new junction table holding both keys + descriptive attributes | ENROLS(Roll_No, Course_ID, Grade) |
| 7 | Weak entity → table whose PK = owner's key + partial key | Loan_No + Payment_No |
The University Diagram, as Tables
-- Rule 1: strong entities → their own tables
CREATE TABLE STUDENT (
Roll_No INTEGER PRIMARY KEY,
Name TEXT,
Gender TEXT
);
CREATE TABLE COURSE (
Course_ID TEXT PRIMARY KEY,
Title TEXT
);
-- Rule 6: the M:N relationship becomes a junction table
CREATE TABLE ENROLS (
Roll_No INTEGER REFERENCES STUDENT(Roll_No),
Course_ID TEXT REFERENCES COURSE(Course_ID),
Grade TEXT, -- relationship attribute
PRIMARY KEY (Roll_No, Course_ID) -- composite key
);An M:N relationship has nowhere to store Grade in either entity's table. The junction table gives it a home — one row per (student, course) pair. If you can't find a home for a relationship's attribute, you've found a missing junction table.
Three Common Mistakes to Avoid
If a candidate "thing" only exists because two other entities came together, it's a relationship, not an entity.
Golden Rules of ER Modelling — 1 to 4
Golden Rules of ER Modelling — 5 to 7
ER modelling is the cheap, erasable blueprint stage. Get the nouns, verbs and adjectives right on paper, and the tables almost write themselves.
The Blueprint, Complete
You can now read and draw an ER diagram, tell strong from weak entities, choose the right key, distinguish cardinality from participation, and convert any diagram into clean tables. Next comes turning these tables into well-formed schemas — keys in depth, functional dependencies and normalization.
Nouns become rectangles, verbs become diamonds, adjectives become ellipses — underline the one that's unique, double the one that's weak, and every M:N gets its own table.
📐 End of tutorial · Press ← to review, or click Restart