# ER Model — Entities, Attributes, Relationships and Cardinality

Source: https://skills101.in/learn/dbms-sql/er-model — Skills101 free tutorial (DBMS & SQL). Updated 2026-11-09.

Before creating tables, designers draw an **Entity–Relationship (ER) diagram**: a picture of *what things* the system stores and *how they connect*.

![ER diagram: entity Student (attributes roll_no as key, name, email) connected through the relationship "enrolls" to entity Course (attributes code as key, title), many-to-many](https://skills101.in/learn-img/dbms-sql/er-diagram.svg)

## 1. The building blocks

| Symbol | Name | Example |
|---|---|---|
| Rectangle | **Entity** — a thing we store data about | Student, Course |
| Ellipse | **Attribute** — a property | name, email |
| Underlined ellipse | **Key attribute** — identifies the entity | roll_no |
| Diamond | **Relationship** — how entities connect | enrolls, teaches |

## 2. Types of attributes

- **Simple** — can't be divided: `age`.
- **Composite** — made of parts: `name` → first, last; `address` → street, city, PIN.
- **Multi-valued** — many values: `phone_numbers` (double ellipse).
- **Derived** — calculated from others: `age` from `date_of_birth` (dashed ellipse).
- **Key** — unique identifier: `roll_no`.

## 3. Cardinality: how many?

| Type | Meaning | Example |
|---|---|---|
| **1 : 1** | one A with one B | Person — Aadhaar card |
| **1 : N** | one A with many B | Department — Employees |
| **M : N** | many A with many B | Students — Courses |

**Participation:** if every employee **must** belong to a department, Employee has **total** participation (double line); if not every course must have students, Course has **partial** participation.

## 4. Weak entities

A **weak entity** can't be identified on its own — it depends on a **strong** entity. Example: `Dependent` (an employee's child) is identified by the employee's id + the dependent's name. Drawn with a double rectangle.

## 5. From ER diagram to tables

| ER part | Becomes |
|---|---|
| Strong entity | a table; key attribute → primary key |
| 1 : N relationship | put the "one" side's key as a **foreign key** in the "many" side table |
| M : N relationship | a **new table** with both keys (a junction table) |
| Multi-valued attribute | a separate table |

For the picture above:

- `students(roll_no PK, name, email)`
- `courses(code PK, title)`
- `enrollments(roll_no FK, code FK, PRIMARY KEY(roll_no, code))` ← the M : N relationship

## 6. Extended ER: generalisation and specialisation

- **Specialisation** (top-down): Employee → Developer, Manager.
- **Generalisation** (bottom-up): Car + Bike → Vehicle.
- **Aggregation**: treat a relationship as an entity (a "project assignment" that itself is monitored by a manager).

## Common mistakes

- Putting a foreign key on the wrong side of a 1 : N relationship (it goes on the **many** side).
- Trying to store an M : N relationship with a foreign key in one table — you need a junction table.
- Storing derived attributes (age) instead of the base value (date of birth).

## Quick recap

- Entity (rectangle), attribute (ellipse), key (underlined), relationship (diamond).
- Attribute types: simple, composite, multi-valued, derived, key.
- Cardinality 1:1, 1:N, M:N; total vs partial participation.
- M:N → junction table; 1:N → foreign key on the many side.


Practice quiz and free certificate: https://skills101.in/learn/dbms-sql/er-model