DBMS & SQL · Lesson 2 of 13 · Updated ·
ER Model — Entities, Attributes, Relationships and Cardinality
Before creating tables, designers draw an Entity–Relationship (ER) diagram: a picture of what things the system stores and how they connect.
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:
agefromdate_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.